DAO Recordset

[Aoife]

Hi

Been struggling with this for several hours - anything obviously wrong with the code below. I've used a DLookup to find valueSignInFastTrackID5, it returns 4851. However the code appears to find a match but the MsgBox "true" line doesn't kick in and the record isn't altered. I can't see where I'm going wrong:banghead:

 Dim dbFast2 As DAO.Database
Dim rstFast2 As DAO.Recordset
  
 Dim valueSignInFastTrackID5 As Integer
  
  
 MsgBox valueSignInFastTrackID5 'returns 4851
     If IsNumeric(valueSignInFastTrackID5) Then
    MsgBox "numeric" ' it says it's numeric
    End If
     
     Set dbFast2 = CurrentDb
    Set rstFast2 = CurrentDb.OpenRecordset("visitorSignOUTDetailsQuery", , dbOpenDynaset)
    
   rstFast2.FindFirst [VisitorSignInID] = valueSignInFastTrackID5
   If rstFast2.NoMatch Then
   MsgBox "no match"
   Else
   MsgBox "match" ' says it's found a match
   End If
  
  If rstFast2![VisitorSignInID] = valueSignInFastTrackID5 Then 
    MsgBox "true" ' this msg box doesn't appear
     rstFast2.Edit
    rstFast2![SigOut] = "FastTrack"
    rstFast2![DateOut] = Now
    rstFast2![TimeOut] = Now
    rstFast2.Update
        
      rstFast2.Close
   
     Set rstFast2 = Nothing
 Set dbFast2 = Nothing
    End If

 

[Cronk]

If you look up the syntax for FindFirst, you'd see it is
rst.FindFirst "Field = Condition"


In your case, you need
rstFast2.FindFirst "[VisitorSignInID] =" & valueSignInFastTrackID5

 

[Aoife]

Many thanks Cronk, don't think I would ever have got there

 

[pbaldy]

Typically more efficient than opening a recordset on an entire table/query and then trying to find the desired record is opening the recordset on that record, like:

Set rstFast2 = CurrentDb.OpenRecordset("SELECT * FROM visitorSignOUTDetailsQuery WHERE [VisitorSignInID] =" & valueSignInFastTrackID5 , , dbOpenDynaset)

and then test for EOF to see if it found the record.

 

[Aoife]

Thanks for the tip Paul, will give it a go

 

[pbaldy]

No problem. You probably won't notice a difference with smaller tables, but in my view it's a good habit to have, and it minimizes network traffic.