Requerying forms record source

[davesmith202]

When I click a button on my switchboard, it opens a Contacts form with a list of contacts. The contacts form source is controlled with this code:

Private Sub Form_Load()

Select Case intWhichForm
   Case 1
      Me.RecordSource = "qry1"
   Case 2
      Me.RecordSource = "qry2"
   Case 3
      Me.RecordSource = "qry3"
      
End Select

Me.Requery

End Sub

The switchboard code for each button is similar to this:

intWhichForm = 1
DoCmd.OpenForm "Contact List", acNormal

When I click off that tab (using Access 2007!) and go back to the switchboard, I can click another button for a different set of data. However, the contents remain the same.

How can I get it to refresh?

Thanks,

Dave

 

[pbaldy]

It sounds like you're leaving that form open? If so, the form load code isn't running when you switch back to it. I'd probably close it. If you want to leave it open, you could test for that in the button code (IsLoaded) and if the form is already open, change its source in the button code.

I'd wonder what the different queries returned, as there may be a better way to accomplish the goal.

 

[davesmith202]

Yes, the form is still open. Client wants it that way. But I've also got the same code in the GotFocus event. I thought if I switched to it, then because it has GotFocus it would then run the code changing the record source. Or is it too late then?

 

[pbaldy]

I don't like thus haven't used the tabbed look in 2007. You might try the activate event.

 

[davesmith202]

So could I do this instead?

DoCmd.OpenForm "Contact List", acNormal,"qry1"

And then just have a different query for each button?

 

[pbaldy]

I tested the wherecondition argument of OpenForm, and it did work on an already open form. I have not tested the filter argument. You never said what the different queries did. There may be better ways to accomplish the overall goal.

 

[davesmith202]

Each query is very different. They are very complex queries. Some of those queries are about 3 queries deep!

 

[pbaldy]

I guess I lean towards setting the recordsource from the calling form instead of the called form, like this behind the first button:

DoCmd.OpenForm "Contact List", acNormal
Forms![Contact List].RecordSource = "qry1"

 

[davesmith202]

Did you mean this? i.e. is there supposed to be a comma after acNormal?

DoCmd.OpenForm "Contact List", acNormal, Forms![Contact List].RecordSource = "qry1"

 

[davesmith202]

Got it now. Stupid me. Two lines of code! lol

Thanks for your help.

 

[gemma-the-husky]

could you not use the form gotfocus event

just do me.requery

-----------
one thing is that requerying the form, resets the cursor to the first record

so in the switchboard, you could set a flag to force the requery only in certain circumstances

then in the popup's gotfocus event you can say

if NeedToRefresh then me.requery

 

[pbaldy]

No problemo.

Dave, I'm not sure how a requery helps when the recordsource needs to be changed.