We help IT Professionals succeed at work.
Get Started

In MS Access, wait for a requery before continuing code in vba

Kaprice
Kaprice asked
on
4,109 Views
Last Modified: 2012-05-12
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
  With frm
    .OrderBy = "Order"
    .OrderByOn = True
    .Requery
  End With
  
' SHOW THE RECORD
  ctr = 0
  Do
  
    ctr = ctr + 1
    DoEvents
    Set rs = frm.RecordsetClone
    With rs
      .FindFirst "ProjectIEID=" & lngID
      If Not .NoMatch Then
        frm.Bookmark = .Bookmark
      End If
    End With
    Debug.Print ctr, frm!ProjectIEID, lngID
  Loop While ctr <= 10 And frm!ProjectIEID <> lngID

Open in new window


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?
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 16 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE