Validate Acct Num Exists

B

Bill

What I would like to do is have a user enter an account number then run a
query against a table to validate that account number exists in that table
and if not, present the user with a message box advising them of an invalid
account number.

Thank you in advance for any help you can provide.
 
O

Ofer

On the after update event of the field, run the code, using dlookup to check
if the record exist

' If the account number field type is number
If isnull(dlookup("[account number in the table]","[Table Name], "[account
number in the table] = " & Me.[account number in the form])) then
msgbox "invalid account number"
End if

' If the account number field type is string
If isnull(dlookup("[account number in the table]","[Table Name], "[account
number in the table] = '" & Me.[account number in the form] & "'")) then
msgbox "invalid account number"
End if
 
M

Maha Arupputhan Pappan

Bill,

Just add on the Ofer. I suggest you to use the Error Trap trick in your
command button (if you have one). When you create a command button, Access
will auto generate the Private Sub event procedure and which will look as
below:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

Include "Err.Number &" (without quotations) before the MsgBox
Err.Description. Thus, it will prompt you a Number. Then you can use the "IF"
statement to trap this issue.

Hope this is helpful.
 
Top