check if record exists

[ianking]

Can anyone tell me the SQL code to check to see if a record exists or not?

eg if a record exists where a particular field matches a textfield on a form, then update database

else

msgbox ("error")

 

[pbaldy]

Several ways; the easiest might be:

If DCount(...) > 0 Then
  update...
Else
  Msgbox...
End If

More info here:

http://www.mvps.org/access/general/gen0018.htm

 

[ianking]

Sorry - I'm not sure of the syntax for dcount. I need to check whether there is a record in the books table with a match for the barcodetext text box on the form

My code looks like this
Private Sub Commandborrow_Click()
DoCmd.SetWarnings False
On Error GoTo Err_Commandborrow_Click

DoCmd.RunSQL "UPDATE [books] SET [books]![Date_borrowed]= now where [barcodetext] = [books]![Barcode]"
DoCmd.RunSQL "UPDATE [books] SET [books]![BorrowerID] = [borrowerIDtext] where [barcodetext] = [books]![Barcode]"
MsgBox ("This item now borrowed")
barcodetext = ""
Exit_Commandborrow_Click:
Exit Sub

Err_Commandborrow_Click:
MsgBox Err.Description
Resume Exit_Commandborrow_Click

End Sub

 

[CraigDolphin]

http://www.techonthenet.com/access/functions/domain/dcount.php

This should help you with the syntax for the dcount function.

 

[ianking]

I'm using this code:

If DCount("Barcode", "books", "Barcode" = [barcodetext]) > 0 Then

but it never seems to return a value > 0 even if i enter a barcode I know is in the books table

 

[CraigDolphin]

Try...

If DCount("Barcode", "books", "Barcode =" & Me.[barcodetext]) > 0 Then

The equals should be inside the quotes, and you may need the Me. to indicates that you're referring to the control on the form, not the field, if they share the same name

Note: this assumes that the value is a number. If it were a text string you'd need:
If DCount("Barcode", "books", "Barcode ='" & Me.[barcodetext] & "'") > 0 Then

 

[ianking]

Yes it was a text filed - many thanks for the help - working now.