refreshing or requerying a continuous form when returning to it.

[slharman1]

I have a continuous form that I can DBL click on a record and it opens a form to edit the data in that record. How do I refresh the recordset in the continuous form whenever I come back to it (it is still open in the background while working on the main form.
Thanks

 

[theDBguy]

To simply refresh the form, you can use the Requery method. However, that would reset the record pointer to the first record. If you don't want that, we'll have to add more code.

 

[Pat Hartman]

If you are only editing existing records and not adding/deleting, then on the line AFTER the line that opens the edit form, Refresh the current form.

Me.Refresh

However, if your popup form can add or delete records, then Refresh won't work. You need to use Requery and as theDBguy pointed out, that will reposition the form to the first record. Repositioning can be done but you need to keep the ID of the record you want to position to. Bookmarks won't work because they are tied to the in memory copy of the recordset and Requery will destroy them.

 

[slharman1]

Thanks.
repositioning is not a problem.
But actually I will be adding records to the recordset of the continuous form while the popup form is open. So where should I put the requery method?
Thank you

 

[Gasman]

I believe if you requery the recordset of the form and not the form itself, then the pointer to the current record is retained.?

I found this out from isladogs's post here https://www.accessforums.net/showthread.php?t=83334&highlight=recordset.requery#4

 

[theDBguy]

I believe if you requery the recordset of the form and not the form itself, then the pointer to the current record is retained.?

I found this out from isladogs's post here https://www.accessforums.net/showthread.php?t=83334&highlight=recordset.requery#4

Oh yeah, I forgot about that. Thanks!

 

[slharman1]

I believe if you requery the recordset of the form and not the form itself, then the pointer to the current record is retained.?

I found this out from isladogs's post here https://www.accessforums.net/showthread.php?t=83334&highlight=recordset.requery#4

Would put that in the got focus event code of the form?

 

[Gasman]

I would probably put it after the line that opens that form from the double-click event?

 

[Pat Hartman]

But actually I will be adding records to the recordset of the continuous form while the popup form is open.

I would NOT recommend having two forms bound to the same recordSource doing updates at the same time.

When you open formB from formA to show a specific record, if formA allows updates, you need to save the current record if it is dirty BEFORE opening formB. And, I always open formB as model and popup so that the user has to close the form before doing anything on any other form.

 

[slharman1]

I would probably put it after the line that opens that form from the double-click event?

I would like it to requery after I have added more records vie the pop up. It is not really a pop up or modal form. Just another form. Will the code continue when I go back to the continuous form?
Thanks

 

[theDBguy]

I would like it to requery after I have added more records vie the pop up. It is not really a pop up or modal form. Just another form. Will the code continue when I go back to the continuous form?
Thanks

I think you have two options:

1. If you don't want the user to go back and forth between the two forms, then I would recommend using the approach both @Gasman and @Pat Hartman said. In your code to open the other form, I would use something like this:

DoCmd.OpenForm "NameOfFormB", , , , , acDialog
Me.Requery

2. If you need the user to go back and fort between the two forms, but keeping in mind the warning already mentioned about updating the same data source from multiple locations at the same time, then I would probably use something like the following in the Close event of the second form.

Forms!NameOfFormA.Requery

Hope that helps...

 

[Pat Hartman]

If formB is not opened as modal, the code in formA continues running after formB is opened. If formB is opened as modal, the code in formA STOPS and waits for formB to close before continuing.

You can Requery formA from formB in formB's AfterUpdate event.

However, I tell you again, it is poor practice to have two forms bound to the same table open at the same time unless one of them is modal which prevents the original form from being accessed until the modal form closes.

 

[slharman1]

If formB is not opened as modal, the code in formA continues running after formB is opened. If formB is opened as modal, the code in formA STOPS and waits for formB to close before continuing.

You can Requery formA from formB in formB's AfterUpdate event.

However, I tell you again, it is poor practice to have two forms bound to the same table open at the same time unless one of them is modal which prevents the original form from being accessed until the modal form closes.

Can I make the second form a modal form and still update it AND the sub forms on it?
If so that might work. I am not updating the first (continuous form) it is only providing the list to open the second form.

 

[Pat Hartman]

Being modal does not impact whether or not a form and its subforms are updateable. All it does is prevent you from setting focus to any other object in the application without closing the modal form first.

 

[slharman1]

Being modal does not impact whether or not a form and its subforms are updateable. All it does is prevent you from setting focus to any other object in the application without closing the modal form first.

Cool. Thanks Pat. Thank you all. You’ve taught me a lot today.