dlookup value in a table

[geno]

I have a form where the user will enter in a new product, these products have a unique upc code. After the user enters in the new products upc I need to look in the table to see if the upc exists already. If it does I will need a message saying the upc already exists, clear the upc field and have this field retain it's focus. If the upc is not already in the table the user can continue entering data.
This forum is great!
Thanks

 

[dynamictiger]

I think the simplest approach to this could be to create a query that returns a count of upC numbers using the form as criteria. If the count is >0 there must be a match.

So you could load some could behind the form like

if Dlookup("Count","MyTable")>0 then

Me.Undo

Me.txtUpc.setFocus

MsgBox"INvalid number"

End if

 

[Pat Hartman]

Use something like the following in the UPC field's AfterUpdate event.

If Not IsNull(Dlookup("UPC","YourTable", "UPC = " & Me.YourControlName)) then
Me.Undo
MsgBox "Duplicate UPC",vbOKOnly
End if

 

[geno]

Thanks Pat your suggestion worked. What I'm trying to do now is to setfocus back onto this field if there is a duplicate. I've tried me.catupc.setfocus and I've put it in different places in your code but the focus always goes to the next field. Any ideas?
Thanks