MUltiple parameter options (2003)

Topic

New Reply

A lottery DB has (currently) 6 allowable methods of payment (for example,quarterly cheque, quarterly cash, annual cheque, annual cash)
The users want a labels report which can be parameterised – so they want to be able to print lables for all people who pay by quarterly cheque, for example, but they also want to be able to print labels for any combination of payment types (for example, print labels for everyone who pays by quarterly cheque or annual cheque or quarterly cash or annual cash, print labels for everyone who pays quarterly etc.)
What’s the best way to cope with all these combinations, please? (I can’t see how to write the criteria for all the possible combinations in the query which underlies the report)
Thanks
Silverback

Reply | Quote

Replies

I would use a multi-select list box on a form to let the user select one or more payment methods. See for example post 402,894.

Reply | Quote

Thanks, Hans
Working perfectly, now
Silverback

Reply | Quote

The way I handle reporting with multiple parameters is to create an unbound form with the parameters I want users to select. In your case a multi-select list would work or a set of check box controls could also be used. Check box controls would be harder to administer if the list changed often but might be easier for user’s to understand.

In the Open event in the report, open the parameter form as a dialog with the DoCmd.OpenForm statement. Create OK and Cancel buttons in the parameter form. I also create a check control to hold the Cancelled status and make it invisible. You could also use a custom property for this. When the user selects OK, set the Cancelled status to False and make the form invisible. When the user selects Cancel, set the Cancelled status to True and make the form invisible. This way the report can be cancelled if the user didn’t really want to run it.

When the dialog form is hidden the Open Event in the report will start up again. Create an object variable that references the paramter form. Copy the user’s selections into variables and then close the parameter form. Make sure to set the object variable for the form to Nothing at the end of your code. Once you have the parameters stored in variables you can use them to construct a WHERE clause for the report’s record soruce. Wtih that constructed you can set the recordsource with the complete query, including your WHERE clause or set the Filter and Filter On properties.

This approach is very flexible and once you get it set up the first time changes usually don’t take that long. I try to use the same parameter form in as many reports as possible. This gives the user a common interface to work with. I also pass the name of the report into the parameter form using the OpenArgs property. This allows me to use the Open Event to turn on/off certain controls and set default values and to also use a validation procedure when the user clicks the OK button to make sure required values have been selected.

HTH

Reply | Quote

Hello Patrick
Thanks for your reply. I have got the thing working using Hans’ code, but I am intrigued by your suggestion which I would like to keep for future use. However, my skill level means I don’t quite understand your reply in its entirety.
1. What is the check control to hold Cancelled status. Is this something to be used elsewhere to test whether the user wants to continue the action or cancel it?
2. What is ‘an object variable that references the parameter form?’
3. Finally, having got the multiple parameters working, there’s a practical problem with labels. When the report is run, the output starts at Row 1, Column 1. This means that a sheet of labels which has been used before doesn’t have sticky labels left at this position. Is there a way to a) specify where the first label should go and/or move the report’s output so the labels start at the position where the first label is on the sheet, please?
Thanks
Silverback

Reply | Quote

About #3: see HOW TO: Skip Used Mailing Labels and Print Duplicates in Access 2000 (applies to later versions too). There is a link near the end to download a sample database demonstrating the technique described in the article.

Reply | Quote

Hans
Thank you again. Did not need the sample database; the instructions are very precise and it all worked first time out.
Problem solved.
Silverback

Reply | Quote

Hi Solverback,

Here are the answers to your questions.

1) The Cancelled check box control is for use by the report to determine if the user cancelled out of the parameters form. In the open event for the report, the report should open the form in a modal state by using the acDialog parameter value in the DoCmd.OpenReport command. This stops the report code until the user is done with the parameter form. When the user click on either the OK or Cancel button the code behind those buttons make the form invisible but desn’t close the form. Closing the form is up to the report’s Open event, after the user’s selections have been extracted from the form. So, if the user clicks OK, set the value of the Cancelled check control to False so the report can read that value from the form and know that the user still wants to run the report. If the user clicks Cancel, set the value of the check box control to True so the Open event in the report can set the report’s Cancel property to True and close the report before it’s displayed on the screen.

2) Once the parameter form is open you can reference it’s controls by typing Forms(“name_of_the_parameter_form”).name_of_the_control.value but this would make for a lot of typing. Instead, dimension a variable as Access.Form; Dim frmParameters AS Access.Form. Then, in the code Open event, after the parameter form is hidden and the Open event procedure starts up again, set a reference to the parameter form with ‘Set frmParameters = Forms(“name_of_the_parameter_form”). Once you do that you can access the form’s properties and controls through frmParameters. When you are done with the form you can make this call to close it; DoCmd.Close acform, frmParameters.Name, acSaveNo. After you close the form make sure to destroy the object reference; Set frmParameters = Nothing.

3) Printing labels will always present problems. The first thing is that no vendor of labels is going to recommend you run the same sheet of labels through a printer more than once. But, if you do, one suggestion would be to create a work table that you can use as surrogate, empty labels. Create as many records as there are used up labels on the page. Then, in the query that returns the records for the labels, use a JOIN and an ORDER BY to include the empty labels and make them come out first. This issue is actually outside of your original post. I would suggest posting again on just the label issue. You may get an answer that’s better than mine.

Good luck.

Reply | Quote

Patrick
Thank you for your detailed explanation of this technique.
Hans’ reply about the label issue solved the problem of partially used label sheets completely. Microsoft obviously recognised a common problem and provided the necessary code and instructions which worked perfectly.
Thanks again
Silverback

Reply | Quote