Wilma
asked on
Creating forms & subforms from queries
I have created a time recording database, with various queries and forms. The problem I have is when I create a form from a query and want to insert a sub-form (using the same query), when the form is opened it asks the parameter questions twice, obviously once for the sub-form and once for the main form. This is ok if you enter the details exactly the same both times, but if a typo is made you can end up with the wrong information altogether! I need to know how to tell it that the answers to the first set of parameters are the same as the second. Hope that makes sense!
ASKER
Thanks, this may work, but how do I do it??
1. Create your form. Name the text boxes something intelligible (on mine, for instance, I'm frequently asking for a date range, so my text boxes will be txtStartDate and txtEndDate).
2. In your query (and I'll use the above as an example), click in the criteria line for the appropriate field(s), then hit the Build button (the magic wand at the top of the screen). Once the form pops up, double-click the forms folder on the left, then All Forms, then the name of your form. From the middle list, you should be able to scroll down to see your text box/combo box/whatever. Double-click that. Repeat this procedure for each piece of criteria you want in your popup form.
NOTE: If you want to let the users choose from multiple criteria but not have to enter *every* piece of criteria, you can put
Like "*" & forms!YourForm.YourTextBox
for each criteria. That way, if they leave the box blank, Access will still pull records.
3. Behind the OK button on your popup form, put this code (you can right-click on the button, choose Build Event, then Code Builder):
DoCmd.OpenReport "NameOfYourReport", acviewpreview
DoCmd.close acform, "NameOfYourPopupForm"
Hope that gives you a good start.
2. In your query (and I'll use the above as an example), click in the criteria line for the appropriate field(s), then hit the Build button (the magic wand at the top of the screen). Once the form pops up, double-click the forms folder on the left, then All Forms, then the name of your form. From the middle list, you should be able to scroll down to see your text box/combo box/whatever. Double-click that. Repeat this procedure for each piece of criteria you want in your popup form.
NOTE: If you want to let the users choose from multiple criteria but not have to enter *every* piece of criteria, you can put
Like "*" & forms!YourForm.YourTextBox
for each criteria. That way, if they leave the box blank, Access will still pull records.
3. Behind the OK button on your popup form, put this code (you can right-click on the button, choose Build Event, then Code Builder):
DoCmd.OpenReport "NameOfYourReport", acviewpreview
DoCmd.close acform, "NameOfYourPopupForm"
Hope that gives you a good start.
ASKER
Thanks very much - excellent comment! Please post an answer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent answer! Thanks very much.
Instead of using the "regular" parameters, create a small pop-up form to get the parameters you need. Then, in your query, point to the text boxes on this form. When a user tries to open your main form/subform combo, open the popup form first, and when they click the OK button, first open your main/sub combo and then close the popup form. Would that work?