Checking for a Number

[Guest]

I have a "Find" form where a user can type a Badge Number in a text box press
the Find button and another form will open with a filter showing all the
records related to that Badge Number. When the user clicks the Find button i
first want to make sure that Badge Number has records in that form and if it
doesn't I want to display a message box. Here is the code I have...

Private Sub cmdFind_Click()

Me.txtEnterBadge.SetFocus
If Me.txtEnterBadge.Text = "" Then
MsgBox "You must enter a Badge Number"
ElseIf Me.txtEnterBadge <> DLookup("BadgeNum", "tbl_Form111", _
"BadgeNum =" &
Me.txtEnterBadge & "") Then
MsgBox "There is no history for the Badge Number you entered"
Me.txtEnterBadge.SetFocus
Else
DoCmd.RunMacro "mcro_Find.FindEmpDocManager"
End If

End Sub

Even if the Badge Number is not in the Form/Table it still opens instead of
showing the Message Box. Can anyone see anything I am doing wrong with this
code, or is there a better way to check this?

Thanks in Advance!

 

[Gijs Beukenoot]

Playa drukte met precisie uit :

I have a "Find" form where a user can type a Badge Number in a text box press
the Find button and another form will open with a filter showing all the
records related to that Badge Number. When the user clicks the Find button i
first want to make sure that Badge Number has records in that form and if it
doesn't I want to display a message box. Here is the code I have...

Private Sub cmdFind_Click()

Me.txtEnterBadge.SetFocus
If Me.txtEnterBadge.Text = "" Then
MsgBox "You must enter a Badge Number"
ElseIf Me.txtEnterBadge <> DLookup("BadgeNum", "tbl_Form111", _
"BadgeNum =" &
Me.txtEnterBadge & "") Then
MsgBox "There is no history for the Badge Number you entered"
Me.txtEnterBadge.SetFocus
Else
DoCmd.RunMacro "mcro_Find.FindEmpDocManager"
End If

End Sub

Even if the Badge Number is not in the Form/Table it still opens instead of
showing the Message Box. Can anyone see anything I am doing wrong with this
code, or is there a better way to check this?

Thanks in Advance!

The thing is that you're formfield has a text format and your dlookup
returns a numeric value. I think that's why it fails.

Try (but I haven't tested this syntax) :

ElseIf Me.TxtEnterBadge <> Trim(Cstr(NZ(DLookup("BadgeNum",
"tbl_Form111", "BadgeNum =" & Me.txtEnterBadge & ""),""))) Then

Oh, and you should also test for Null in the first if...

 

[Marshall Barton]

I have a "Find" form where a user can type a Badge Number in a text box press
the Find button and another form will open with a filter showing all the
records related to that Badge Number. When the user clicks the Find button i
first want to make sure that Badge Number has records in that form and if it
doesn't I want to display a message box. Here is the code I have...

Private Sub cmdFind_Click()

Me.txtEnterBadge.SetFocus
If Me.txtEnterBadge.Text = "" Then
MsgBox "You must enter a Badge Number"
ElseIf Me.txtEnterBadge <> DLookup("BadgeNum", "tbl_Form111", _
"BadgeNum =" &
Me.txtEnterBadge & "") Then
MsgBox "There is no history for the Badge Number you entered"
Me.txtEnterBadge.SetFocus
Else
DoCmd.RunMacro "mcro_Find.FindEmpDocManager"
End If

End Sub

Even if the Badge Number is not in the Form/Table it still opens instead of
showing the Message Box. Can anyone see anything I am doing wrong with this
code, or is there a better way to check this?

The issue here is that DLookup returns a Null when it
doesn't find a record and anything compared to Null in any
way always fails. I.e. If x = Null fails and If x <> Null
also fails.

Another point is that a text box with nothing in it will be
Null, so your code can be simplified a little:

If IsNull(Me.txtEnterBadge.Text) Then
MsgBox "You must enter a Badge Number"
ElseIf IsNull(DLookup("BadgeNum", "tbl_Form111", _
"BadgeNum =" & Me.txtEnterBadge)) Then
MsgBox "There is no history for the Badge" &
Me.txtEnterBadge
Else
DoCmd.RunMacro "mcro_Find.FindEmpDocManager"
End If