FindFirst and Select Problem and Fix

[GregDillon]

Hi. I just wanted to post this tip.

Problem: Sometimes a findfirst doesn't work when the selection criteria on the recordset has been changed just prior to the findfirst statement. I think this is because Access doesn't always properly wait for the SELECT to finish before it start the FindFirst. This causes Access to not find the record because it hasn't selected it yet!

Fix: Add a MoveLast and MoveFirst (in that order) in from of the FindFirst. The MoveLast force Access to wait for the select to end. The MoveFirst just move the record to the beginning of the recordset so the the findfirst will work (I not sure the findfirst is needed - but lets be safe).


Code sample:
...
Dim vProj As Long
Dim rst As DAO.Recordset

DoCmd.OpenForm "frmInputCallDetail"
Forms![frmInputCallDetail].RecordSource = "Select * from tasklist where compldate is not null"
'The next two line are necessary to force Access to finish selecting records
'prior to doing a findfirst. I do not no why Access does not wait properly
'anyway without these statements.
Forms![frmInputCallDetail].Recordset.MoveLast
Forms![frmInputCallDetail].Recordset.MoveFirst
'Find the record that matches the Project number.
Set rst = Forms![frmInputCallDetail].RecordsetClone
rst.FindFirst "[ProjNumb] =" & vProj
Forms![frmInputCallDetail].Bookmark = rst.Bookmark
Forms![frmInputCallDetail]!frmInputDetailSubForm.Visible = False
Forms![frmInputCallDetail].SetFocus