I have a form/main form setup.
I have a button for adding a record via vba (not using the built-in record add feature of the UI).
After adding the record to the underlying recordset, I need to requery the form.
Then, I want to select (show) the newly added record in the subform.
Sometimes the following code works. Other times it APPEARS that the code that finds the record and shows it runs BEFORE the subform's REQUERY, so it shows the first record, not the one I just added (which could be anywhere in a long list of records).
Here's my code (that works in many occasions).
Set frm = Me.Subfrm.Form
.OrderBy = "Order"
.OrderByOn = True
' SHOW THE RECORD
ctr = 0
ctr = ctr + 1
Set rs = frm.RecordsetClone
.FindFirst "ProjectIEID=" & lngID
If Not .NoMatch Then
frm.Bookmark = .Bookmark
Debug.Print ctr, frm!ProjectIEID, lngID
Loop While ctr <= 10 And frm!ProjectIEID <> lngID
I've tried doing a Wait/DoEvents loop and that seems to work, but it forces an arbitrary delay which isn't always necessary and could end up not being a long enough delay and thus not fully solving the problem.
Is there any way to make the SHOW THE RECORD code only run after the requery has fully completed?