Requery - how to use more sparingly

[logik]

Hello,

I use requery in the On Activate event of a form to ensure all the records are visible after a new record has been added. However, I note that this event is called whether a record has been added or not which seems a waste of time. The Dirty boolean value in the form does not seem to indicate anything useful
What would be a good test to see if I should requery or not?

I currently do the above in an embedded macro but happy to convert to VBA if necessary.

 

[pbaldy]

You would only need to requery if the record was added outside the form (and user). Is that the case?

 

[logik]

That is the case. I have a datasheet form and a pop up, single record form. When the single record form is closed, I call requery on On Activate in the datasheet form to see the complete, updated record list. However, the On Activate also gets called on Load and if the user cancels the record entry. I am trying to eliminate the latter two scenarios.

 

[pbaldy]

You can requery the first form from the single record form's "add record" button. If the second could be opened from other forms, you can use IsLoaded to see if the first is open before requerying.

 

[logik]

Yes, I can see the requery being called when I include it on the On Click of the "Add Button" but the first form is still not updated with the new record. A manual refresh shows it so I tried to add a refresh into the On Activate in the first form but to no avail.

 

[pbaldy]

You'd have to make sure the new record was saved first, then requery the first form. Show your code if that's not working.

 

[logik]

I had it after the save record but have now shifted the requery to after the Close Window command and that seems to work.
I have plenty more still to work on but this was bugging me.
Many thanks for the prompt and helpful replies

 

[pbaldy]

No problem, and welcome to the site by the way!

FYI, there are ways to force the save in code, like

If Me.Dirty Then Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord