Find out if a record exists

  • Thread starter Thread starter NH
  • Start date Start date
N

NH

What is the quickest and easiest way of testing whether a record already
exists in a table?

I.e. Is there a record with 'Joe' in the 'FirstName' field of the 'Contacts'
table?

My first thought is open a recordset, and loop though counting the records
with "Joe" in [Name].... But is seems quite a clunky way of doing such
simple task..

Thanks,

Nick
 
NH said:
What is the quickest and easiest way of testing whether a record already
exists in a table?

I.e. Is there a record with 'Joe' in the 'FirstName' field of the 'Contacts'
table?

My first thought is open a recordset, and loop though counting the records
with "Joe" in [Name].... But is seems quite a clunky way of doing such
simple task..
Click to expand...

Yeah, that would be clunky. It's usually better to open a
recordset that counts the number of records that meet the
criteria:

strSQL = "SELECT Count(*) FROM tbl WHERE FirstName = 'Joe'"
Set rs = db.OpenRecordset(strSQL)
If rs(0) = 0 Then
'not found
Else
'yes, it's already in the table
End If
rs.Close : Set rs = Nothing

You can probably do just as well using a DLookup

If IsNull(DLookup("field", "tbl", "FirstName = 'Joe'"))
'not found
Else
'yes, it's already in the table
End If
 
Hello Nick,

There are a few ways to do this. I use the DAO method. Depending on when you
want to check to see if data already exists would depend on where you need
to place the code below. My example is in the forms BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rsTemp As DAO.Recordset
' Clone the form
Set rsTemp = Me.RecordSetClone
' See if the name entered into the name field already exists
rsTemp.FindFirst "Name = '" & [Name] & "'"
' Check for a match
If rsTemp.NoMatch Then
' The name doesnt exist...
Else
' The name does exist...
End If

End Sub

Replace Name with the name of the field you are searching in the Table and
[Name] with the name of the field you are searching for on your form. The
above code would need a reference to the Microsoft DAO library. In VBA
(Alt+F11), goto Tools->References. Scroll down to Microsoft DAO 3.6 Object
Library (or the latest version of the DAO library depending on the version
of Access you are using) and tick the box. Click OK and all should work as
planned.

HTH,

Neil.
 
I'd use DLookup():

sName = "Joe"
if nz (dlookup (true, "Contacts", "FirstName=""" & sName & """"), false)
then
' it's there!
else
' it aint there!
endif

Not much reason to go to the trouble of opening a recordset, IMO, unless you
have this in a loop & the DLookup takes too long. In that case you might
open a global recordset on the table concerned, & use .Find or .Seek (within
the loop) to look for the record.

HTH,
TC
 
Back
Top