Which "event" to use?

[Big Pat]

• I have a query which joins two tables and includes various calculated values.
• Form1a shows the results of this query in continuous view.
• Form1b shows records from Table1 in single-view with a sub-form 2, based on Table2 in continuous view.
• Each detail line on form 1a has a button which opens form 1b filtered to the appropriate
  record (button created via the command button wizard).

So far so good, everything works OK.

If I select a record and and then make some changes to sub-records, that should change some of the results back on form 1a, but when I close form 1b, no changes have been made.

I assume I need to requery the controls on the form 1a e.g. Me.ControlName.Requery and I have tried this in both On Activate() and On GotFocus() but neither of these worked.

If I manually close form 1a and re-open it then it DOES show the correct changes. So basically, I think I want to simulate that behaviour without actually closing the form.

I suppose the close button on form 1b could *also* close form 1a and then re-open it, but that seems inefficent.

Do I need to requery on a different event or do I need to do something else?

Thanks,

Pat.

 

[vbaInet]

You need to requery the form itself:

Forms![COLOR=Red]FormName[/COLOR].Requery

 

[Pat Hartman]

Keep in mind that requerying a form will reposition the record pointer back to the first record in the recordset. If the form shows multiple records and you want it to stay at the current record, you will need to save the current ID, do the requery and then use code to reposition the recordpointer to the ID you saved.

 

[Big Pat]

Where do I put that code? In the On GotFocus event of the form I'm coming back to (form 1a)? Or in the After Update event of the contol I update on form 1b? Or somewhere else?

Pat, thanks for the tip about saving the record position. The particular "view" shown is unlikely to show more than 10 records at any time and will be visible without scrolling down, so I don't see this as an issue in this case. However I'll certainly keep it in mind.

 

[vbaInet]

In the After Update event of the form where you are amending records.

 

[Big Pat]

Hi - Just wanted to come back and say thanks. That worked. I really appreciate it!

 

[vbaInet]

No problemo Pat.