2 Parameters Query from a form and subform

[pippo]

Hello,
I have been struggling with this for a few weeks, maybe just not possible!
I have a form, frm_clients. The record source is a query, qry_clients. The selection criteria for qry_clients comes from another form frm_SearchClients.

frm_clients has a subform which displays a list of subsidiaries. I would like to be able to select any subsidiary and refresh frm_clients to display the data associated with the subsidiary.

I have inserted a second criteria in qry_clients but it prompts me for the first criteria which would have come from frm_SearchClients. If I click OK the query returns the value I want.

Is there a way to indicate that if frm_SearchClients is not opened and the first criteria is blank the query should bypass the prompt and use the second criteria?

I have tried with [criteria1] or [criteria2], I have tried with IIf is null, no luck.
Thank you

 

[pbaldy]

In a word, no. If there's a form reference in the query the form needs to be open so Access can find it. Wouldn't that form have to be open for frm_clients to be opened?

 

[pippo]

That is what I thought. I have vb code that closes the search form once the selection is made.
Thank you for confirming. I was going crazy looking for a solution.

 

[pbaldy]

No problem. You can hide the form if you want; it doesn't have to be visible.

 

[pippo]

Back to reality, and to NY. Missing Pizza!!

OK this is undoubtedly too complicated for my skill-set.
Yes, I can leave the form open but I would have to pass the parameters along anyway. Here is the sequence of events as of now:
1. Switchboard: A form with a text field where you can enter parts of a name. on enter:
2. Opens a form with a list box populated by names that fit the search criteria. The form source is a query where the parameter is populated by the switchboard.
The switchboard is closed
3. Upon selection of one record the frm_clients is opened with all the information associated with the specific client. Among the information is a subform with a list of other clients that are associated to the company

What I would like to do is: if I select a company in this subform list, refresh the information of the main form to that of the company selected.

on click, in the background I could reopen the switchboard and pass the parameter to it, reopen the second form and pass the parameter and then refresh the existing form. But:
A. seems insane
B. Not sure it makes sense
C. Not sure it is doable

Before I drive myself crazy I would much appreciate your input.

 

[pbaldy]

Sorry, lost track of this thread. Did you get it sorted? This:

if I select a company in this subform list, refresh the information of the main form to that of the company selected.

should be relatively easy. Depending on your setup, you can either go to that record, change the filter on the main form or reset it's record source. Typically I'd do the latter, along the lines of:

Forms!MainFormName.RecordSource = "SELECT Blah FROM TableName WHERE KeyField = " & Me.FieldName