Validate field, reset value

[Mary]

I found the code below to validate a field which is working well. I have the
code in the On Click event of the "Emergency" checkbox. I want the checkbox
to be left unchecked if the Bridge_Num field is null. I do get the message
box, but the Emergency checkbox is checked. What code do I need to add to
set the value back to No for the checkbox? Thank you! Mary

Private Sub Emergency_Click()

If IsNull(Me.Bridge_Num) Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
MsgBox ("Please enter Bridge information!")
Cancel = True
Exit For
End If
End If
Next ctl
End If

End Sub

 

[Mary]

I added Cancel = True after the first line of code, and that does set the
value back to No for the checkbox, but the focus is still on the checkbox and
I can't click in the Bridge_Num field without hitting the escape key -- it
keeps giving me the message box message. I'd like it to cancel the checkbox
and move to the Bridge_Num field so the user can enter that data.

Thanks,
Mary

 

[Marshall Barton]

I found the code below to validate a field which is working well. I have the
code in the On Click event of the "Emergency" checkbox. I want the checkbox
to be left unchecked if the Bridge_Num field is null. I do get the message
box, but the Emergency checkbox is checked. What code do I need to add to
set the value back to No for the checkbox? Thank you! Mary

Private Sub Emergency_Click()

If IsNull(Me.Bridge_Num) Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
MsgBox ("Please enter Bridge information!")
Cancel = True
Exit For
End If
End If
Next ctl
End If

End Sub

Try putting the code in the check box's BeforeUpdate event
and add the line:
Me.Emergency.Undo
right after the Cancel line.

 

[BruceM]

Have you declared ctl anywhere? It would be something like this:

Dim ctl as Control

This could be in the form's Declarations section (at the top), or in this
procedure (probably better).

Another point:
At the top of the code window, add:
Option Explicit
directly below Option Compare Database (unless it is already there).

Click Debug >> Compile to make sure Access understands the code.

If Option Explicit is not there, in the VBA editor click Tools >> Options.
On the Editor tab, check "Require Variable Declaration". This will add
Option Explicit to future code modules, but you will still need to change
existing ones as described. If you do not require variable declaration
Access will pretty much assume anything it doesn't recognize is a variable
of variant type (I'm overstating this a bit, but don't want to get into an
extended discussion about this here). If you make a typo Access may go
ahead with the code, but it may not be in the way you expect. Similarly, if
"ctl" is not declared it could be that your code will work, but again the
results may be unpredictable in osme cases.

 

[Mary]

Thanks Marshall, it's working now. One more thing I'd like to do with code
is move the focus to the Bridge_num field so the user can enter that
information. I tried adding Me.Bridge_Num.SetFocus in a couple of places in
the code but I get an error message that I must first save the field before
moving the focus. Can you tell me where to put that code, or is that the
right way to go about it?

Thanks again, Mary

 

[Marshall Barton]

You can not move the focus until after Access has figured
out what to do with the control it's processing. This is an
intricate and tricky situation that I have not run into
before.

I suspect that the code you found was used in the form's
BeforeUpdate event where everything has settled down and the
record is about to be saved. However, you want to prevent
users from checking a check box in some situations AND move
the focus. The earliest you can do all that would be the
check box's AfterUpdate event, but, because of the Cancel
and Undo in the BeforeUupdate, you can not use Setfocus
there. I tried and even though Exit (and LostFocus) are far
from an ideal solution to your problem I could not get the
Exit event to work either.

For your specific scenario (not a general approach), I was
able to get what I think is your desired effect by letting
the BeforeUpdate event procede without any code and using
the AfterUpdate event to unravel things:

Private Sub Emergency_AfterUpdate()

If IsNull(Me.Bridge_Num) And Me.Emergency Then
. . .
MsgBox ("Please enter Bridge information!")
Me.Emergency = False
Me.Bridge_num.SetFocus
Exit For
. . .

 

[Mary]

Thank you so much - that did the trick!

You can not move the focus until after Access has figured
out what to do with the control it's processing. This is an
intricate and tricky situation that I have not run into
before.

I suspect that the code you found was used in the form's
BeforeUpdate event where everything has settled down and the
record is about to be saved. However, you want to prevent
users from checking a check box in some situations AND move
the focus. The earliest you can do all that would be the
check box's AfterUpdate event, but, because of the Cancel
and Undo in the BeforeUupdate, you can not use Setfocus
there. I tried and even though Exit (and LostFocus) are far
from an ideal solution to your problem I could not get the
Exit event to work either.

For your specific scenario (not a general approach), I was
able to get what I think is your desired effect by letting
the BeforeUpdate event procede without any code and using
the AfterUpdate event to unravel things:

Private Sub Emergency_AfterUpdate()

If IsNull(Me.Bridge_Num) And Me.Emergency Then
. . .
MsgBox ("Please enter Bridge information!")
Me.Emergency = False
Me.Bridge_num.SetFocus
Exit For
. . .
--
Marsh
MVP [MS Access]

Thanks Marshall, it's working now. One more thing I'd like to do with code
is move the focus to the Bridge_num field so the user can enter that
information. I tried adding Me.Bridge_Num.SetFocus in a couple of places in
the code but I get an error message that I must first save the field before
moving the focus. Can you tell me where to put that code, or is that the
right way to go about it?