Locking a unique form

[Jevon]

I have employees entering data into a form ie each day
after they enter data I want them to lock the data to protect it
can I place a "button" or control to lock their data in the form by the date
after they entered it?

 

[Tom van Stiphout]

On Tue, 20 Jan 2009 05:27:18 -0800, Jevon

I understand you have a form with one or more records for each day.
The records should be editable only during the day they were entered.
If that is correct, write this one-liner in the Form_Current event:
Me.AllowEdits = (myDateField = Date)
(of course you change myObjectNames to yours)

What this does is compare the date field to the current date. That
results in either True or False. Then we set AllowEdits to that value
so it will be True and allow editing only on today's date.

-Tom.
Microsoft Access MVP

 

[Jevon]

Tom
Thanks for the direction but I don't get it
First the name of the form is "z-tape daily sales"
I place the one-liner on the z-tape form in the current event, from the
properties (for the z-tape form)
Me.AllowEdits = (myDateField = Date)
and
AllowEdits = (myDateField = Date)
and
AllowEdits = Date
and
AllowEdits = "Date"
I get a message
"microsoft access can't find macro 'allowedits = (myDateField = Date)'

Please help with more direction

 

[John Spencer (MVP)]

Pardon me for jumping in.

When looking at the form properties, change the
text in the On Current to read [Event Procedure]
Then click the button with three periods at the end.

That will open the VBA editor and should display these two lines
Private Sub Form_Current

End Sub

Between those two lines enter the suggested code so you end up with

Private Sub Form_Current
Me.AllowEdits = (myDateField = Date)
End Sub

Hope that is sufficient to get you started.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jevon]

John
Thank You so much
that worked AND I COULD UNDERSTAND!
Thanks
Jevon

 

[Jevon]

Sorry, not done yet or atleast not so fast:
I now get this error:

Invalid use of Null (Error 94)


Null is a Variant subtype used to indicate that a data item contains no
valid data. This error has the following cause and solution:

You are trying to obtain the value of a Variant variable or an expression
that is Null. For example:
MyVar = Null
For Count = 1 To MyVar
.. . .
Next Count
?is this from adding the code suggested in the forms-properties-event-on
current [event procedures] ...

OR
from a calculation im doing in a different box as in
=IIf(IsNull([No Sale %]),[Customer Count]/[No Sale Count],0)
Thanks in advance
Jevon

 

[John Spencer]

It is possible that it is the code. Since MyDateField could be null,
you would need to check for that or use the NZ function to force a date
if it were null. I think that the following expression will take care
of this.

Me.AllowEdits = (Nz(MydateField,Date())=Date())

Alternative code, that you could use in place of the above
IF IsNull(MyDateField) Then
Me.AllowEdits = True
ELSE
Me.AllowEdits = (myDateField = Date)
END IF

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry, not done yet or atleast not so fast:
I now get this error:

Invalid use of Null (Error 94)


Null is a Variant subtype used to indicate that a data item contains no
valid data. This error has the following cause and solution:

You are trying to obtain the value of a Variant variable or an expression
that is Null. For example:
MyVar = Null
For Count = 1 To MyVar
. . .
Next Count
?is this from adding the code suggested in the forms-properties-event-on
current [event procedures] ...

OR
from a calculation im doing in a different box as in
=IIf(IsNull([No Sale %]),[Customer Count]/[No Sale Count],0)
Thanks in advance
Jevon

John
Thank You so much
that worked AND I COULD UNDERSTAND!
Thanks
Jevon

 

[james]

get 1234567890

 

[Jevon]

Johm
Thanks for the help thus far.

I now get ...

Conpile error:
Block If without End If

I added the line End If prior to the end sub but got a differnt error

Please help

 

[Jevon]

John
In addition to the other error I can't put the date value (as in todays
date) in the form
because of the "auto locking" any suggestions?
--
Jevon

It is possible that it is the code. Since MyDateField could be null,
you would need to check for that or use the NZ function to force a date
if it were null. I think that the following expression will take care
of this.

Me.AllowEdits = (Nz(MydateField,Date())=Date())

Alternative code, that you could use in place of the above
IF IsNull(MyDateField) Then
Me.AllowEdits = True
ELSE
Me.AllowEdits = (myDateField = Date)
END IF

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry, not done yet or atleast not so fast:
I now get this error:

Invalid use of Null (Error 94)


Null is a Variant subtype used to indicate that a data item contains no
valid data. This error has the following cause and solution:

You are trying to obtain the value of a Variant variable or an expression
that is Null. For example:
MyVar = Null
For Count = 1 To MyVar
. . .
Next Count
?is this from adding the code suggested in the forms-properties-event-on
current [event procedures] ...

OR
from a calculation im doing in a different box as in
=IIf(IsNull([No Sale %]),[Customer Count]/[No Sale Count],0)
Thanks in advance
Jevon

John
Thank You so much
that worked AND I COULD UNDERSTAND!
Thanks
Jevon

 

[John Spencer (MVP)]

Jevon,

At this point, I have no idea what code you are using. Would you care to post
the entire sub - start with the line

Private Sub Form_Current()

and copy everything to the line that says
End Sub



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jevon]

John
it is:

Private Sub Command163_Click()

End Sub

Private Sub Form_Current()
If IsNull(myDateField) Then
Me.AllowEdits = True
Else
Me.AllowEdits = (myDateField = Date)
End Sub

 

[John Spencer]

So the name of your date field is myDateField? If not can you replace
myDateField with the actual name of the control.

Private Sub Form_Current()
On Error GoTo Proc_Error
'Add error handler so we can see where error happens.

If IsNull(Me.TheNameOfYourDateFieldControl) = True Then
Me.AllowEdits = True
Else
Me.AllowEdits = (Me.TheNameOfYourDateFieldControl = Date())
END IF 'Add missing end if

Exit Sub 'All worked so lets get out of the sub

Proc_Error: 'Come here and report if error happens in this sub
Msgbox Err.Number & ":" & err.Description,,Me.Name & " Current Error"
End Sub


If that still errors, then I am stuck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jevon]

John
no errors but now the form does not lock for any dates (todays or other dates)

thanks for all your past efforts and hopfully this one too.

 

[John Spencer]

So now my guess is that you are storing the Date and the time in your
field. If so, we need do add one more bit to this


Private Sub Form_Current()
On Error GoTo Proc_Error
'Add error handler so we can see where error happens.

If IsNull(Me.TheNameOfYourDateFieldControl) = True Then
Me.AllowEdits = True
Else
Me.AllowEdits=(DateValue(Me.TheNameOfYourDateFieldControl) = Date())
END IF 'Add missing end if

'Add the following for testing purposes
MsgBox Me.TheNameOfYourDateFieldControl & " // " & Date()),,"Values"
'This should tell you what you are comparing.

Exit Sub 'All worked so lets get out of the sub

Proc_Error: 'Come here and report if error happens in this sub
Msgbox Err.Number & ":" & err.Description,,Me.Name & " Current Error"
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jevon]

John
Highlights the msgbox me.Date

It has a:
Syntax error


Visual Basic can't determine what action to take. This error has the
following causes and solutions:

A keyword or argument is misspelled.
Keywords and the names of named arguments must exactly match those specified
in their syntax specifications. Check online Help, and then correct the
spelling.

Punctuation is incorrect.
For example, when you omit optional arguments positionally, you must
substitute a comma (,) as a placeholder for the omitted argument.

A procedure isn't defined.
Check the spelling of the procedure name.

You tried to specify both Optional and ParamArray in the same procedure
declaration.
A ParamArray argument can't be Optional. Choose one and delete the other.

You tried to define an event procedure with an Optional or ParamArray
parameter.
Remove the Optional or ParamArray keyword from the parameter specification.

You tried to use a named argument in a RaiseEvent statement.
Events do not support named arguments.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

 

[hor vannara]

Johm
Thanks for the help thus far.

I now get ...

Conpile error:
Block If without End If

I added the line End If prior to the end sub but got a differnt error

Please help

 

[hor vannara]

John
In addition to the other error I can't put the date value (as in todays
date) in the form
because of the "auto locking" any suggestions?
--
Jevon

It is possible that it is the code. Since MyDateField could be null,
you would need to check for that or use the NZ function to force a date
if it were null. I think that the following expression will take care
of this.

Me.AllowEdits = (Nz(MydateField,Date())=Date())

Alternative code, that you could use in place of the above
IF IsNull(MyDateField) Then
Me.AllowEdits = True
ELSE
Me.AllowEdits = (myDateField = Date)
END IF

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry, not done yet or atleast not so fast:
I now get this error:

Invalid use of Null (Error 94)


Null is a Variant subtype used to indicate that a data item contains no
valid data. This error has the following cause and solution:

You are trying to obtain the value of a Variant variable or an
expression
that is Null. For example:
MyVar = Null
For Count = 1 To MyVar
. . .
Next Count
?is this from adding the code suggested in the
forms-properties-event-on
current [event procedures] ...

OR
from a calculation im doing in a different box as in
=IIf(IsNull([No Sale %]),[Customer Count]/[No Sale Count],0)
Thanks in advance
Jevon



:

John
Thank You so much
that worked AND I COULD UNDERSTAND!
Thanks
Jevon