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

Posted on 2004-11-30
Last Modified: 2012-08-13
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.

Question by:Lou Dufresne
    LVL 41

    Assisted Solution

    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.
    LVL 5

    Accepted Solution


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

    End sub
    LVL 1

    Author Comment

    by:Lou Dufresne
    Thank you both for your expert help.

    Lou Dufresne

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now