Data Validation Question 1

[prenfrow]

I have a form with a part number text box that stores data in a table (tblInventory). I want to take the input from the part number text box, lookup in another table (tblXref) that has three columns, NewPartNo, OldPartNo and UnitofMeasure. The user can enter either the NewPartNo or OldPartNo, but I want to always want to return the NewPartNo to the form. If possible, I would like to populate another input box on the form with the UnitofMeasure associated with the part number. Also, I would like to give an error message if the part number doesn't exist. Can someone help me with the code to do this? Thanks, Paul!!

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya prenfrow . . .

Have a look at [blue]DLookUp![/blue]

Questions are:
[ol][li]How are you determining entry of new or old part number (you mention only one input box)?[/li]
[li]I can see entering an old part number and returning the new. A new partnumber thats found to exist should simply need a msgbox saying so. Yes?[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ck1999]

You could look up new number and if not found lookup old number

    vv = DLookup("UnitofMeasure", "tblInventory", "NewPartNo= '" & Me.Text0 & "'")
    If IsNull(vv) Then
        vv = DLookup("UnitofMeasure", "tblInventory", "OldPartNo= '" & Me.Text0 & "'")
        Me.Text0 = DLookup("NewPartNo", "tblInventory", "OldPartNo= '" & Val(Me.Text0) & "'")
    End If
    txtmeasure = vv

this assumes part numbers are text

text0 = textbox of partnumber entered into
txtmeasure = txt box for UnitofMeasure

ck1999