Message Box not working correctly

[KCK]

I have a query based continuous form that looks up a serial number entered by the user and allows them to view the matching data.
When a user enters a serial number that is not in the database I want a message box to pop up to warn them there is no match.

The code I am using makes the msgbox pop up on every entry. I have tried to put this code in a variety of form and control events with no luck. Can someone tell me what I am doing wrong?

Private Sub Form_AfterUpdate()
Dim intRespone As Integer

If DCount("[packing station scan].[serial_number]", "match with last test results", _
"[drive test results].[serial_number] = ' & Forms![match with last test results].[Serial_Number] & '") < 1 _
Then
intresponse = MsgBox("Stop! Serial Number has no test data!", vbYesNo, "No data found")
Select Case intresponse

Case vbYes
DoCmd.Requery

Case vbNo
DoCmd.Requery
End Select
End If

End Sub


Any help is greatly appreciated!

Regards,
Kerry

 

[llkhoutx]

Try

If vbyes = MsgBox("Stop! Serial Number has no test data!", vbYesNo, "No data found") then

else
'it's no

end if

 

[CEH]

Kerry, take a look at this code.... It's used to check for duplicate social security numbers input into a field. (From the table "tblApplicant") But it will show you one that works.

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.SocialSecurity.Undo


Else:
End If
End Sub

In your case you might just be able to change the "If Not IsNull(answer) then" to "If IsNull(answer) Then"

Hope that helps
Curtis

 

[KCK]

Hello llkhoutx and CEH,

Thanks to both of you for your feedback. I have it working now!

Thanks again,
Kerry

 

[tblake]

Hi There,

I have used the below coding below and modified to align with the table and form names I am using, but every time that I go to enter in a company name that I know does not exist in the table it is giving me the error message "Company Name Already Exists. Please Try Again".

Can someone advise me on what I am doing wrong, here is mine after the revisions:

Private Sub Company_Name_as_in_Vistamed_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[Company_Name_as_in_Vistamed]", "AMS Renewals and Termination Table", "[Company_Name_as_in_Vistamed] = '" & Me.Company_Name_as_in_Vistamed & "'")
If Not IsNull(Answer) Then
MsgBox "Company Name Already Exists." & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.Company_Name_as_in_Vistamed.Undo

Else:
End If
End Sub
__________________________________________________ ___________
Your assistance is appreciated.

tblake
__________________________________________________ _________
Quote:
Originally Posted by CEH View Post
Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"
Code:
Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.SocialSecurity.Undo


Else:
End If
End Sub

 

[pbaldy]

Looks okay offhand. Have you set a breakpoint and seen what the textbox is returning? Can you post the db here?