• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Validate data enterred into a field on a form against a value in another table

I have a form that contains a field titled BarCode. This field is bound to a table call it Table-AAA. When the user enters a number into the barcode field I want the application to validate that the number the user entered into the Barcode Field  is a number listed in TABLE-BBB. If it is not a valid value I will pop up an error message. The name of the field in Table-BBB to use in the validation process is "AssetNbr".

I know that this VBA code will be placed on the After-Update event of the BarCode field but I do not know how to create this code.

Any Code suggestions on how to do this?

Thanks in advance for your suggestions.

Lou Dufresne
Lou Dufresne
2 Solutions
You could do this:

If IsNull(DLookup("[AssetNbr]","Table-BBB","[AssetNbr]='" & Me!BarCode & "'")) Then
   'Record doesn't exist in bbb
   'It does
End If

However, you can achieve the same thing by enforcing referential integrity between tables AAA and BBB. This would force the AAA record to have a related record in BBB, if that is what you want to achieve. The above code btw assumes that the field is text - if it is a numeric field, remove the apostrophe ' characters.

if dcount("[IDFIELD]", "TABLE-BBB", "[AssetNbr] = '" & me!BarCode & "'") = 0 then
     msgbox "Enter your error code here"
     'it is in the list

End sub
Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thank you both for your expert help.

Lou Dufresne

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now