I am attempting to use the After Update Event to lookup in the underlying table to see if this product is already listed in the table. Can anyone tell me what I'm doing wrong here.

I get a Run-time error '3464':
Data type mismatch in criteria expression.



Private Sub ProductBarCode_AfterUpdate()
If Not IsNull(DCount("ProductID", "Products", "ProductBarCode = " & Me.ProductBarCode)) Then
MsgBox "That product does not exist in the db"
Else
MsgBox "that product already exists in the db"
End If


Thanks!!!

Hi,
try the following:

If DCount("ProductID", "Products", "ProductBarCode = " & Me.ProductBarCode) = 0 Then
MsgBox "That product does not exist in the db"
Else
MsgBox "that product already exists in the db"
End If

HTH
Good luck

HTH,

Thanks, I tried that and still get the same error.

Any ideas?

Thanks

Is ProductBarCode a text data yype? If so then use:
If DCount("ProductID", "Products", "ProductBarCode = '" & Me.ProductBarCode & "'") = 0 Then

Hi,
What datatype is ProductBarCode?

BTW (by the way) my name it not HTH, it means Hope This/That Helps .

HTH
Good luck

Thanks Matt. That was the problem.

And sorry freakazeud, didn't know about HTH...

btw, am I correct in using a text field for a UPC barcode field?

Thanks again

you're welcome.
No problemo.
Glad WE could assist.
Yes you should use a Text field.
Good luck on future projects!

One other issue please,

The code works perfectly:

If DCount("ProductID", "Products", "ProductBarCode = '" & Me.ProductBarCode & "'") = 0 Then
'do nothing
Else
'cancel the entry, notify the user, and stay on the blank productbarcode field
MsgBox "A Product with that UPC is already in the database", , "Duplicate Record"
Me.Form.Undo
Me.ProductBarCode.SetFocus
End if

However, the last line doesn't seem to function (Setfocus) after the Message Box, the cursor is moved to the next field on the form. Why does it not go back to the productBarCode field?

Thanks!

Try:
docmd.GoToControl "ProductBarCode"

Thanks Matt,

Tried that and it too does not do it. Focus still goes to the next field.

Ah right, because the code is on the After Update Event. You will need to move the code to the Before Update Event and use Cancel = True.

Thanks Matt,

The After Update was the problem.

The Cancel = True statement caused a problem too...

I changed that line to

me.form.undo

and it's working as intended.

MANY thanks...

You are welcome.