Parameterized query in a subform

[grgimpy]

I created a parameterized query which gets the Criteria from a Form like so:

[Forms]![SEB-OverhangCharts]![Criteria1]

When I put the form in my main form as a subform, the query cannot find the parameters correctly and prompts me to enter them in. I assume the path name needs to be changed slightly, but I can't figure out what to change it to. The main form's name is [SEB-MainPage], the subform is [SEB-OverhangCharts], and the text box it looks up is [Criteria1]. I tried:

[SEB-MainPage]![SEB-OverhangCharts]![Criteria1]

and similar things, but got nowhere.

Any help would be greatly appreciated, thanks!

 

[belovedcej]

I have had this problem before. The issue is actually that the Forms are both opening at the same time, so your subform is looking for the control on your main form before it has loaded.


There is an "easy" way out of this - if your database will not be really large. Simply remove the criteria from the query. Then, open your main form in design view. Right click on the subform and open the properties dialog. (NOT in the little black box in the left corner of the subform - be sure all that is highlighted is the box around your subform, not the subform itself.) Go to the data tab and where it says "link child field" and "link master field" enter the field name which ties the two together.

This will be slower when dealing with more data or lots of subforms, because it loads everything and then filters it.

Another option would be to leave the criteria in the query. Then do not set the source object of the subform at all, so that when your form loads, the subform would be empty. (to fix this, just delete the sourceobject from the subform properties) Have some other event (a button, a mouseover, or something like that) set the source. To do this, on the chosen event put: Me.mySubform.SourceObject = "mySubformName"

Hope that helps.

 

[grgimpy]

I tried the second option, but I'm still having a problem with the query recognizing the criteria. I would rather not use the first option. The query is only used by the one subform. I have the form set up so that if a user checks a checkbox, it will fill in the criteria in a text box automatically. There are 4 criteria, and thus 4 text boxes all in this one subform that the query reads from.

When I set the source object via coding, the main form will open up with the subform w/o any prompts. But when the user tries to check the checkbox and select criteria, I get a prompt for each criteria for each pivotchart. The query defines what is plotted in each pivotchart based on the criteria. Any more ideas would be greatly appreciated. Thanks.

 

[dhookom]

Try this which is what you might get if you drilled down through open forms in the builder.

Forms![SEB-MainPage]![SEB-OverhangCharts].FORM![Criteria1]

This assumes the name of the subform control is the same as the name of the subform. This ain't always the case.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[belovedcej]

Try this - do not have checking the boxes trigger the criteria. Instead, allow the user to check all the appropriate boxes, and then have a command button which they click to then open the subform based on the criteria they just entered.

 

[grgimpy]

Try this which is what you might get if you drilled down through open forms in the builder.

Forms![SEB-MainPage]![SEB-OverhangCharts].FORM![Criteria1]

This worked perfectly. Thanks dhookom. Thanks for the input also belovedcej. The info was good to know anyways.

 

[belovedcej]

Cool! The simple answer is [almost] always the better one - and Duane has most of the answers anyway.

 

[dhookom]

Duane has had most of the problems and made most of the mistakes in the past ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

I find that hard to believe Duane....I thought you were made with all this info hard coded!



les

 

[dhookom]

Not much hard-coded here. Mostly data-driven. In fact if driving through Wisconsin, if you see license plate [blue][DTA DRVN][/blue] that would be me.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]