FindFirst with AND statement

[robjones01]

I'm having a real issue with the below code

strDate = Format(Now, "Short Date")

stLinkCriteria = "[CardID]='" & Me![tCard_ID] & "'"

stSearchDupeCheckIn = "(([DateVisited]=#" & strDate & "#) And ([CardID]='" & Me.tCard_ID.Value & "'))"

Set rsMembers = CurrentDb.OpenRecordset("tblMembers")

Set rsAudit_Log = CurrentDb.OpenRecordset("tblAudit_Log")

strMemberNumber = Null

strFullMember_Number = Null

strTitle = Null

strFirstName = Null

strSurname = Null

strCardID = 0

rsAudit_Log.FindFirst stSearchDupeCheckIn

If rsAudit_Log.NoMatch Then

GoTo ContinueSearch

Else

GoTo EndScript

End If

It errors at the FindFirst with the following message:

"Operation is not supported for this type of object"

I've tried everything to no avail...any help is much appreciated

[Gicu]

how do you declare the rsAudit_log variable? Have you tried Dim rsAudit_log as Dao.recordset?

[davegri]

Try this:

stSearchDupeCheckIn = "[DateVisited]=#" & strDate & "# And [CardID]=" & tCard_ID

[robjones01]

Unfortunately it's still not working, here's the full code;

Dim stDocName, stLinkCriteria, stSearchDupeCheckIn, strMemberNumber, strFullMember_Number, strTitle, strFirstName, strSurname, strCardID As String
Dim rsMembers, rsAudit_Log, rsVisitors As Recordset
Dim strAuditNum As Long
Dim strDate As Date

strDate = Format(Now, "Short Date")
stLinkCriteria = "[CardID]='" & Me![tCard_ID] & "'"
stSearchDupeCheckIn = "[DateVisited]=#" & strDate & "# And [CardID]=" & tCard_ID

Set rsMembers = CurrentDb.OpenRecordset("tblMembers")
Set rsAudit_Log = CurrentDb.OpenRecordset("tblAudit_Log")

strMemberNumber = Null
strFullMember_Number = Null
strTitle = Null
strFirstName = Null
strSurname = Null
strCardID = 0


rsAudit_Log.FindFirst stSearchDupeCheckIn
If rsAudit_Log.NoMatch Then
    GoTo ContinueSearch
Else
    GoTo EndScript
End If

ContinueSearch:

I'm at a loss as I've done this many times and its not working suddenly.

It's errors at this code

rsAudit_Log.FindFirst stSearchDupeCheckIn

I've tried moving to the last and first record prior to running the search but it still doesn't work.

The DateVisited field is set as a date, and the CardID is a Short Text field.

[aytee111]

CardID requires quotes around it if it is a text field.

stSearchDupeCheckIn = "[DateVisited]=#" & strDate & "# And [CardID]='" & tCard_ID & "'"

[orange]

This may not be a direct cause, but with Access, if you do not explicitly DIM your variables/objects they will default to Variant.
So you would use this general approach:

Dim strDocName as String
Dim strTitle as String

or

Dim strDocName as String, strTitle as String, rsAudit_Log as DAO.Recordset

everything is explicitly Dimmed.

??What exactly is the purpose of the Audit log in your set up??

[robjones01]

CardID requires quotes around it if it is a text field.

stSearchDupeCheckIn = "[DateVisited]=#" & strDate & "# And [CardID]='" & tCard_ID & "'"

I’ve tried this and it still produces the same error.

[aytee111]

I'm not sure what is in strDate. "Format" produces a text field altho you have defined it as a date, so I don't know what will be produced. A far safer way would be
"DateVisited=Date()"

[Gicu]

As Orange pointed out I believe it is the way you declare your variables. The way you have it now rsAudit_Log is a variant, not a DAO.recordset.

Cheers,
Vlad

[orange]

What is the error number? Here is a link to possible cause(s) of the error.
Here is a link to DAO.RECORDSET.Findfirst with an example

[robjones01]

Guys, I solved this by adding 'dbopendynaset' to the open recordset statement...working fine now.

Thanks for your help