We help IT Professionals succeed at work.

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

Kaprice
Kaprice asked
on
Medium Priority
3,969 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

Travis HydzikVarious
CERTIFIED EXPERT

Commented:
try the attached

 
Sub TestCode()
    Dim rFind As Range
    Dim irow As Long
    Dim oldVal As String, newVal As String
    With Sheet1
        Set rFind = .Cells.Find(what:="Table2", lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            irow = rFind.Row + 1
            Do While .Cells(irow, "A").Value <> vbNullString
                newVal = .Cells(irow, "A").Value
                If oldVal = newVal Then
                    Application.DisplayAlerts = False
                    .Range(.Cells(irow - 1, "A").Address, .Cells(irow, "A").Address).Merge
                    Application.DisplayAlerts = True
                End If
                oldVal = newVal
                irow = irow + 1
            Loop
        End If
    End With
End Sub

Open in new window


 Merge-Example-thydzik.xlsm

Author

Commented:
I'm confused. This is an MS Access problem. Not sure how your Excel example helps me.
Travis HydzikVarious
CERTIFIED EXPERT

Commented:
yeh, I posted in the wrong question, apologies for that. have flagged your question to the moderators my answer deleted.

didn't post to let you know as I didn't want to add more irrelevant comments.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Is that the entire code?

I can't see where you are declaring or setting lngID...?

<I have a button for adding a record via vba (not using the built-in record add feature of the UI).>
Just for clarity, can you post this code?

<After adding the record to the underlying recordset>
The recordset orf the main form or the subform?

<I need to requery the form.>
The main form?, the subform? ...or both?

You have other code in there as well, if you comment out this code, does it work.?

Finally, why is the requery needed?
When a value changes in the subform, after that record is added, any calculations involving this vale should update.

JeffCoachman

Author

Commented:
boag2000, here are my answers...

This is a snippet of the relavent part of the code. No need to post the rest. The code works and the declarations are fine.

The RS is for the subform.

I'm requerying the subform.

Finally, why is the requery needed?
When a value changes in the subform, after that record is added, any calculations involving this vale should update.

I'm not using the UI for the record insert. Via code, I'm adding the record. I need to requery the subform to show the added record.

Adding the record works fine and the record shows in the subform.

But, I also want to select the newly added record (which could be anywhere in the list of records).

That code works, too, sometimes. But in others, I've found the record gets selected and THEN the subform is requerying or refreshing or something that causes it to select record 1.

I SUSPECT but haven't proven that the sort or the requery lines are running and the record find code is running before the requery or sort completes.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
For me at least, this would be much easier to investigate if you posted a sample DB,...
...There are just too many things here that I am not certain about...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman

Author

Commented:
I'm reluctant to modify my database so I can make it public.

It seems like a pretty straightforward question. I just want to know if Access has a way to delay running code until a forms requery/refresh/filter/sort function has completed.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<'m reluctant to modify my database so I can make it public.>
I don't need you exact database, just make up a junky sample db that illustrates this issue...


Again, you have a lot going on there that I don't know why it is there (the For Loop for example)

But try it like this in any event:


 Set frm = Me.Subfrm.Form
  With frm
    .OrderBy = "Order"
    .OrderByOn = True
    .Requery
  End With
 
    DoEvents

' SHOW THE RECORD
  ctr = 0
  Do
 
    ctr = ctr + 1

    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



...In other words just use basic troubleshooting and delete (comment out) everything not needed to requery or return to the record.
Then see if it works, ...If so, then add back each deleted element until the issue re-appears...

I have no issue doing what you are requesting with a simple main/sub form.

JeffCoachman

Author

Commented:
Oh, you're right. I should have taken that DO loop out. That was a clumsy attempt to see if repeating the find up to 10 times would work. But, it always finds it on the first loop, so it doesn't belong there.

I'll check out your suggestion and let you know.

Author

Commented:
I don't see any differences in your code from mine. What did you change?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
I moved doevents out of the loop and put it before the loop

Author

Commented:
That didn't resolve it.

Author

Commented:
I'll be digging into this, again, next week. I'll consider posting a sample db with the problem.

Author

Commented:
I've not forgotten this. Just delayed.
Commented:
OK, I solved the problem.

The trick is to take the code that finds and displays the newly inserted or added record in the Form_Timer event.

So, you

add the record
requery the form
set me.timerinterval to some number 500 works for me, but might need to be larger for larger recordsets

In Form_Timer,
you first set me.timerinterval to 0
Then the code that finds and displays the new record.

Author

Commented:
No one was able to give me a working answer. Through experimentation, I came up with it on my own.

It's a good solution, so I thought the community, here, would benefit from me posting it and accepting it as the correct answer.