.findfirst strCriteria returns wrong recordcount 1

[Apollo13a]

Hello, I'm using this code to parse a query and return the record count using strCriteria. TN is a string and SignInDate is a date. RecordCount should be 3 but it is returning the total number of records in the query. It appears findfirst is not using the strCriteria.

Public Sub DupSignIn(TN As String)
Debug.Print

 Dim dbs As Database
 Dim rstSignIn As Recordset
 Dim strCriteria As String
 
    Set dbs = CurrentDb
    Set rstSignIn = dbs.OpenRecordset("qryDupSignIn", dbOpenDynaset)
    
 Dim SignInDateHolder As Date
 Dim rcdSignIn As Integer

 SignInDateHolder = Nz(Forms!frmMain!frmSignIn_subform.Form!SignInDate, 0)
 strCriteria = "[SignInDate]= # " & SignInDateHolder & "# and [TicketNum]= '" & TN & "'"
 
 rstSignIn.MoveLast
 rstSignIn.MoveFirst
 rstSignIn.FindFirst strCriteria
              
                If Not (rstSignIn.NoMatch) Then
                    MsgBox "Match Found"
                    rcdSignIn = rstSignIn.RecordCount
                    
                Else
                
                End If
 rstSignIn.Close
 Set rstSignIn = Nothing
 
End Sub

I'm receiving any errors i just do not get the right recordcount. Is my code accurate? should I look into using a query def?
Jim

 

[Zion7]

No, because find first does not filter the recordset. It simply moves the cursor.

You should use strCriteria, in the opening of your recordset

Dim SignInDateHolder As Date
Dim rcdSignIn As Integer

SignInDateHolder = Nz(Forms!frmMain!frmSignIn_subform.Form!SignInDate, 0)
strCriteria = "[SignInDate]= # " & SignInDateHolder & "# and [TicketNum]= '" & TN & "'"



Set rstSignIn = dbs.OpenRecordset("SELECT * FROM qryDupSignIn WHERE " & strCriteria, dbOpenDynaset)


Or You could use, rstSignIn.Filter = strCriteria



BUT PERSONALLY, why not use DCount???

Dim SignInDateHolder As Date
Dim rcdSignIn As Integer

SignInDateHolder = Nz(Forms!frmMain!frmSignIn_subform.Form!SignInDate, 0)
strCriteria = "[SignInDate]= # " & SignInDateHolder & "# and [TicketNum]= '" & TN & "'"

rcdSignIn = DCount("pkID","qryDupSignIn",strCriteria)

 

[Apollo13a]

Thanks Zion7
I didn't think of Dcount to see if the record existed, but now that you mention it..... seems it would be the way to go. Although I did learn something about FindFirst.
Thanks, Jim