If record does/doesnt exist

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Use the DCount() function. If DCount() returns 0, the record doesn't exist.

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