Link to home
Create AccountLog in
Avatar of Kaprice
KapriceFlag for United States of America

asked on

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

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?
Avatar of Travis Hydzik
Travis Hydzik
Flag of Australia image

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
Avatar of Kaprice

ASKER

I'm confused. This is an MS Access problem. Not sure how your Excel example helps me.
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.
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
Avatar of Kaprice

ASKER

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.
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
Avatar of Kaprice

ASKER

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.
<'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
Avatar of Kaprice

ASKER

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.
Avatar of Kaprice

ASKER

I don't see any differences in your code from mine. What did you change?
I moved doevents out of the loop and put it before the loop
Avatar of Kaprice

ASKER

That didn't resolve it.
Avatar of Kaprice

ASKER

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

ASKER

I've not forgotten this. Just delayed.
ASKER CERTIFIED SOLUTION
Avatar of Kaprice
Kaprice
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kaprice

ASKER

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.