When I have the Me.Refresh in my code it crashes my access application.
I changed it to the DoCmd.Requery - however when this runs it takes the user
to the first record in the database.
What can I use to refresh my query and still stay on the current record?
Matt
I know this post is quite old, but I am posting my solution for anyone else searching for a fix for this issue. I scoured the Internet and did not find a reliable solution and it seems that a number of folks have had this problem but did not really find a viable fix that should work in all cases. I hope that my solution is just that. I certainly took my share of Advil while finding what worked for me and hopefully this will spare others the some heartburn.
OK, so here is my scenario before creating the fix. I have a form with 2 subforms. The parent form has 2 combo boxes with their datasources set to fields in the tables that underly the queries which are the datasource for the 2 corresponding subforms. I hope that is clear. When an item is selected or deselected from the combo boxes the controls' Change event would run Me.Refresh / MeRequery, both of which would randomly crash Access. This is to refresh the records shown in the subforms. At times it worked fine, other times not so much = spontaneous Access crash with no errors.
I also have code running in the Parent form's Current, Load , BeforeUpdate and AfterUpdate events. So this likely causes a delay in the Parent and subforms' record being commited to the underlying tables. Thus, at times, the subforms are requering while the commit transaction is still open on their underlying tables. At least that is my theory.
The issue seems to be that the parent forms' record is not completely saved when the subform is called to requery....sometimes....exactly whenever.
The fix:
In the combo boxes on the parent form Change event:
Private Sub MyComboBox1_Change
'Save the current record of the parent form first
DoCmd.RunCommand acCmdSaveRecord
'Give Access time to finish saving the current record
DoEvents
'Requery the corresponding subform ONLY (not the parent form as that will trigger the Load and Current events again, and not Refresh as 'that will not update the subform's displayed records.)
Me!SubFrom1.Form.Requery
End Sub
For good measure I also set the parent form and subforms' "Wait for Post Processing" property to Yes, but not sure if that has any effect.
Peace, love and happy coding!
