Do not overwrite a field

[Darrell Childress]

I have a form where users click a button and it inputs the current time
in a field called TimeStart to indicate that they have started working
on a job. I have found where users inadvertently (I assume) go back and
hit that button again and it overwrites the field with the then current
time. How can I prevent that from happening? In other words, when the
user clicks the button, if the field already has a value in it, I do not
want it to overwrite what's already in there. Also, I would like to give
a message to the user stating that "You have already started working on
this job" if that happens (obviously, I don't want that message to
appear under normal circumstances, i.e., if the field is blank when they
click the button).
Thanks for any help,
Darrell

 

[Stefan Hoffmann]

hi Darrell,

I have a form where users click a button and it inputs the current time
in a field called TimeStart to indicate that they have started working
on a job. I have found where users inadvertently (I assume) go back and
hit that button again and it overwrites the field with the then current
time. How can I prevent that from happening?

The field should be a Date/Time field with required set to no and no
default value. Use this condition for testing:

If IsNull(Me![TimeStart]) Then
Me![TimeStart] = Now()
Else
MsgBox "Already started."
End If


mfG
--> stefan <--

 

[RonaldoOneNil]

In the click event of your button put something like this

If Nz([TimeStart],"") = "" then
[TimeStart] = Format(Now(),"Short Time")
Else
Msgbox "You have already started working on this job"
End If

 

[Darrell Childress]

That works great. One more question. Previously, I had all the steps
being done in a macro, so when the button was clicked, it actually
called up a macro, with the first step in the macro being to enter the
current time. I removed that step from the macro and used the code
below. I would also like it to NOT even call up the macro if there is
already a value in the TimeStart field. In other words, if the TimeStart
field is blank, then run all the code. If the TimeStart already contains
a value, then only display the "Already started" message and quit.
Here's my current code:

Private Sub StartJob_Click()
If IsNull(Me![TimeStart]) Then
Me![TimeStart] = Now()
Else
MsgBox "This job has already been started"
End If

Dim stDocName As String
stDocName = "mcrStartJob_Laser_SOD"
DoCmd.RunMacro stDocName
End Sub


If necessary, I think I could completely remove the macro commands and
just write the necessary VBA code to do what the macro is doing. Would
that be the better way to go anyway?

hi Darrell,

I have a form where users click a button and it inputs the current time
in a field called TimeStart to indicate that they have started working
on a job. I have found where users inadvertently (I assume) go back and
hit that button again and it overwrites the field with the then current
time. How can I prevent that from happening?

The field should be a Date/Time field with required set to no and no
default value. Use this condition for testing:

If IsNull(Me![TimeStart]) Then
Me![TimeStart] = Now()
Else
MsgBox "Already started."
End If


mfG
--> stefan <--

 

[Jeff Boyce]

As an alternate approach, what about making it impossible?

If you disable that button when there's already a date/time value in the
field, no one has to get the message "Dummy! You already have a date/time
started!"

Combine this with the other suggestion you got to pre-load the date/time
using DefaultValue, and your user wouldn't even have to click a button!

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Darrell Childress]

Excellent idea Jeff, and I like your reasoning. Thanks,
Darrell

 

[Stefan Hoffmann]

hi Darell,

I removed that step from the macro and used the code
below. I would also like it to NOT even call up the macro if there is
already a value in the TimeStart field. In other words, if the TimeStart
field is blank, then run all the code. If the TimeStart already contains
a value, then only display the "Already started" message and quit.

Just place the call to the macro into the correct If branch:

Private Sub StartJob_Click()

On Local Error GoTo LocalError

If IsNull(Me![TimeStart]) Then
Me![TimeStart] = Now()
Me.Dirty = False ' Save the record.
DoCmd.RunMacro "mcrStartJob_Laser_SOD"
Else
MsgBox "This job has already been started"
End If

Exit Sub

LocalError:
MsgBox "Error while running job." & vbCrLf & _
"Description: " & Err.Description, vbCritical

End Sub

If necessary, I think I could completely remove the macro commands and
just write the necessary VBA code to do what the macro is doing. Would
that be the better way to go anyway?

I would say yes, cause you have more control, more possibilities and if
you're used to it the ability to faster read and write VBA macros.

But it's not necessary, nor mandatory.

mfG
--> stefan <--

 

[Darrell Childress]

Thanks for the help on this, it's working great, plus I learned something.

hi Darell,

I removed that step from the macro and used the code
below. I would also like it to NOT even call up the macro if there is
already a value in the TimeStart field. In other words, if the TimeStart
field is blank, then run all the code. If the TimeStart already contains
a value, then only display the "Already started" message and quit.

Just place the call to the macro into the correct If branch:

Private Sub StartJob_Click()

On Local Error GoTo LocalError

If IsNull(Me![TimeStart]) Then
Me![TimeStart] = Now()
Me.Dirty = False ' Save the record.
DoCmd.RunMacro "mcrStartJob_Laser_SOD"
Else
MsgBox "This job has already been started"
End If

Exit Sub

LocalError:
MsgBox "Error while running job." & vbCrLf & _
"Description: " & Err.Description, vbCritical

End Sub

If necessary, I think I could completely remove the macro commands and
just write the necessary VBA code to do what the macro is doing. Would
that be the better way to go anyway?

I would say yes, cause you have more control, more possibilities and if
you're used to it the ability to faster read and write VBA macros.

But it's not necessary, nor mandatory.

mfG
--> stefan <--