Validate 3 Text Fields - Urgent Need to finish by Monday

[Jani]

I need to have three fields filled in prior to running a macro w/queries and
then opening a form. The code I have is below and it works if at least one of
the fields is filled but if all three fields are empty, it sometimes works
and the cursor ends up in the Date field or sometimes I get a runtime error
that the focus can't move to Combo100. I'm sure my code could be lot
cleaner. Any help would be much appreciated! Thanks!

Private Sub Box236_Click()
If IsNull(Me!Region) Then
MsgBox "You must select a Region"
Me!Region.SetFocus
Cancel = True
End If

If IsNull(Me!Combo100) Then
MsgBox "You must select a Location"
Me!Combo100.SetFocus
Cancel = True

End If

If IsNull(Me!cmbRepMonth) Then
MsgBox "You must select a Date"
Me.cmbRepMonth.SetFocus

Else
DoCmd.RunMacro "macMonthlyDataProcess"
DoCmd.Maximize
End If
End Sub

 

[Ken Snell]

Use ElseIf blocking. Also, you cannot cancel a Click event, so putting
Cancel = True in the code does absolutely nothing.

Private Sub Box236_Click()
If IsNull(Me!Region) Then
MsgBox "You must select a Region"
Me!Region.SetFocus

ElseIf IsNull(Me!Combo100) Then
MsgBox "You must select a Location"
Me!Combo100.SetFocus

ElseIf IsNull(Me!cmbRepMonth) Then
MsgBox "You must select a Date"
Me.cmbRepMonth.SetFocus

Else
DoCmd.RunMacro "macMonthlyDataProcess"
DoCmd.Maximize
End If
End Sub

 

[Tom van Stiphout]

On Sun, 3 Jan 2010 13:40:33 -0500, "Ken Snell"

Ah, but it is an important clue to something else that is wrong with
the OP's code: (s)he does not have Option Explicit at the top of each
code module. Please put that in right away, and then enable this
option by default in Code window > Tools > Options.

If Option Explicit was enabled, the code would not compile because
Cancel would be an undefined variable. That would be an important clue
something is very wrong with the code.

-Tom.
Microsoft Access MVP