Dlookup to verify input

[tscotti]

Hi,

I'm using the code below to check new product# input from a form called frmProducts that is bound to a table called tblProducts.

Private Sub Prod__BeforeUpdate(Cancel As Integer)
Dim varX As Variant
varX = (DLookup([Prod#], "tblProducts", Forms!frmProducts.[Prod#]))
If Not IsNull(varX) Then
MsgBox [Prod#].Value & " already exists as a product #"
Me.Undo
Cancel = True
Else
'do nothing!
End If
End Sub


The problem is that any new product# that you input results in "product already exists".

Thank you very much, in advance, for any help.

Tony Scotti
Montreal

 

[MarkK]

The third item of the DLookup needs to be the lookup criteria, a string, like "<fieldname> <comparison operator> <value>"
varX = DLookup("[Prod#]", "tbLProducts", "[Prod#] = " & Forms!frmProducts.[Prod#])
Also, I never name anything that requires square brackets, so my syntax there might be faulty.

 

[tscotti]

Hi Lagbolt,

Thank you for your response.

Unfortunately, it still doesn't work.

Whether it matters or not, the Prod# field is a text field.

Thanks,
TS

 

[Pat Hartman]

Yes it matters. Strings need to be enclosed in quotes.:

varX = DLookup("[Prod#]", "tbLProducts", "[Prod#] = '" & Forms!frmProducts.[Prod#] & "'")

varX must be defined as a variant since the DLookup() will return null if the product isn't found.

BTW, using embedded spaces and special characters in your names is poor practice.

 

[tscotti]

Pat,

Thank you very much for your answer. It works.

Also, thanks for the poor practice comment. I will change it.

Regards,

Tony Scotti
Montreal