finding out if a table already has a record in it

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don Starnes - 10 Aug 2006 19:04 GMT

I am writing a function that tests if a record has been created in a table;
it is used before creating a record:

Dim cvdb As Database
Dim cvjoinrst As DAO.Recordset
Dim cvjoinspec As String
Dim mytest As Integer

' specify the database
Set cvdb = currentdb()

' open the join table
Set cvjoinrst = cvdb.OpenRecordset("joins_group_person")

cvjoinspec = "[index_groups]=2 AND [index_persons]=8"

With cvjoinrst
cvBookmark = .Bookmark ' Store current record location.
.FindFirst cvjoinspec ' look for the join

If .NoMatch Then ' If there is no such join,
mytest = 0 ' set the value to 0.
Else ' If there is a join,
mytest = 1 ' set the value to 1
End If
.Bookmark = cvBookmark ' go to the last current record.
End With

This code gives an error: Operation not supported for this type of object

What am I doing wrong?

Thanks in advance for your generous help,
Don

Reply to this Message

Douglas J. Steele - 11 Aug 2006 01:37 GMT

Why the bookmark? Are you using the recordset anywhere else?

To find out whether the record already exists, use DLookup or DCount, or
open a recordset specific to your search criteria:

Dim cvjoinspec As String
Dim mytest As Integer

cvjoinspec = "[index_groups]=2 AND [index_persons]=8"
If IsNull(DLookup("FieldName", "joins_group_person", cvjoinspec)) = True
Then
' record doesn't exist
mytest = 0
Else
' record exists
mytest = 1
End If

or

Dim cvjoinspec As String

cvjoinspec = "[index_groups]=2 AND [index_persons]=8"
If DCount("*", "joins_group_person", cvjoinspec) = 0 Then
' record doesn't exist
mytest = 0
Else
' record exists
mytest = 1
End If

or

Dim cvdb As Database
Dim cvjoinrst As DAO.Recordset
Dim mytest As Integer
Dim strSQL As String

strSQL = "SELECT * FROM joins_group_person " & _
"WHERE [index_groups]=2 AND [index_persons]=8"

' specify the database
Set cvdb = currentdb()

' open the join table
Set cvjoinrst = cvdb.OpenRecordset("strSQL")

If cvjoinrst.BOF And cvjoinrst.EOF Then
mytest = 0
Else
mytest = 1
End If

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I am writing a function that tests if a record has been created in a table;
> it is used before creating a record:
[quoted text clipped - 30 lines]
> Thanks in advance for your generous help,
> Don

Reply to this Message