Expressions in forms

[Guest]

I have a form where after someone enters the "Part Number", I want to have it
looked up in table and return the value to the NSN field, this is what I
wrote in the "After Update" event for the "Part Number".

Private Sub Part_Number_AfterUpdate()
Dim varNSN As Variant
varNSN = DLookup("[NSN]", "[tblNSN]", "[Part Number]=Forms![Part Number]")
Me.NSN = varNSN
End Sub

It does not work, any I ideas. Thanks.

 

[Jeff Boyce]

Another way to approach this would be to use a combobox. Your user would
select the PartNumber.

If you include the NSN as one of the fields in the query underlying the
combobox, you can use:
Me!cboComboBox.Column(n)
to get the value of the column holding NSN.

If you wish to stick with your use of DLookup(), check Access HELP for the
syntax. You may need to change the expression to something like:
...,"[Part Number] = " & Forms!YourFormName!YourPartNumberControlName &
")"
or
...,"[Part Number]= " & Me!YourPartNumberControlName & ")"

(your syntax may vary -- depends on what datatype your [Part Number] is)

Regards

Jeff Boyce
Microsoft Office/Access MVP