Hi All,
I have a public function called UPCE2UPCA that converts short UPC codes to full expanded ones. It works fine, because I use it elsewhere in this DB. I have a form where the user will enter a new product and I want to:
1) convert the UPC entered into a full, expanded UPCA
2) Compare the expanded version to my database to see if it's already in there
3) If it's not in there, save the expanded version to the table
The table name is ItemList and the field is call ItemUPCCode. The textbox name is ItemUPCCode, as is the control source. Here is my VBA so far, which is being called on the before update event of the text box:
Private Sub ItemUPCCode_BeforeUpdate(Cancel As Integer)
Me.ItemUPCCode = UPCE2UPCA(Me.ItemUPCCode)
If DCount("*", "ItemList", "[ItemUPCCode]='" & Me.ItemUPCCode & "'") <> 0 Then
rslt = MsgBox("This UPC is already on file", vbOKOnly)
Me.Undo
Else
Me.ItemUPCCode.Value = UPCE2UPCA(Me.ItemUPCCode)
End If
End Sub
This code returns a runtime error of '-2147352567 (80020009)'
If I make the textbox a calculated field that displays =UPCE2UPCA([ItemUPCCode]) then is displays the expanded UPC code fine, but doesn't save it to the table.