changing record source "on the fly"

[cyberbiker]

This has probably been answered before, but I cannot seem to find anything on it.

Please note, this is NOT connected to my other post (Firing blanks).

But how do I change a forms record source "on the fly".

I have a search form where one of 3 option buttons may be selected. The first 2 option buttons are to search a customer data base by either id number or name. Once selected the value of this control is used as the criteria in a query which is the record source of another form (which uses bound controls). Works fine

When the 3rd option button is selected, the search is by a return authorization number.

This is done by a combo box looking up values in a table.
The value of that combo box is the criteria in another query
which is identical to the first query except for the criteria.
Depending on which option is selected, I then open frmRmv1 or frmRmv2 with the record source set to qRmv1 or qRmv2 respectively. This works well, but somehow seems like the long way around the block.

The issue appears to my eyes that the query runs at the start of the load event and I cannot change the record source property until the form is loaded.

Is this a correct assumption and if so how do I get around it?

Terry (cyberbiker)

 

[tomschafer]

Add this to the OnClick event for the option buttons you want to change the record source.

let Me.RecordSource="[Name Of The Respective Query]"
Me.Refresh

 

[Aivars]

Hi Terry!

Use Filter for filtering data of your subform.

Private sub cboComboBox_AfterUpdate()
me.subFormName.form.filter="MyField=" & me.cboComboBox
me.subFormName.form.filteron=true
end sub

Aivars

 

[cyberbiker]

I have not yet tried either of these since I just walked into a "crisis" when I came in, but they sound good.
The only thing is that I want to change the record source for the next form I open not the current.
I think that I can take the ideas expressed here and run with them and make something work, but is there a way to have an event from form1 change thge record source for form2 which is not yet loaded? Terry (cyberbiker)

 

[UncleJack]

Hi Terry:

How about setting the record source for form 2 to a named string variable, like "MyString"? Have form 1 set the contents of the MyString so it's available when form 2 loads. That way the record source for form 2 always has the same name, but different values set by form 1.

Uncle Jack

 

[cyberbiker]

Thanks Uncle Jack. That is the answer.
Terry (cyberbiker)