If record does/doesnt exist

A

Ashg657

Hi,

I'm trying to construct a function on an after update event on a texbox in
my form.
The idea is the user enters a barcode number into the textbox and the
function searches the table "barcodes" to check if it does or doesn't exists.
Then, if it doesnt exist it will load up a seperate form to allow the user to
enter the new item into the table.

Now, i know the solution lies behind some SQL, recordset. I have looked
around the community and have tried several solutions all of which have
generated errors and now im confused. Anyone who could give me a straight
short function will be greatly appreciated.

Thanks in advance.
ash.
 
O

Ofer

You don't have to open a recordsaet to check if the record exist, instead use
the DLookUp

' If the BarCode field type is number use this
If IsNull(DLookUp("BarCodeField","BarCodeTable","BarCodeField = " &
Me.BarCodeFieldNameInTheForm) then
docmd.OpenForm "FormName
End If

' If the BarCode field type is string use this, adding a single quote before
and after
If IsNull(DLookUp("BarCodeField","BarCodeTable","BarCodeField = '" &
Me.BarCodeFieldNameInTheForm & "'") then
docmd.OpenForm "FormName
End If
 
D

David C. Holley

Use the DCount() function. If DCount() returns 0, the record doesn't exist.

If DCount([fieldName],
, [whereCondition]) = 0 then
 
Top