Users can enter criteria directly into a parameter query’s dialog box, but there may be times when they’ll need to run a parameter query while working in a form. You can add a button to the form that will run a query using criteria entered in a form field. For example, say your company services customers in two states, Missouri and Illinois. You would like to create a query that will allow service personnel working in either state to quickly get a list of their customer data. Follow these steps:
- Open the form in Design View.
- Click on the Text Box tool and then click and drag to locate the control in your form.
- Right-click the text box and select Properties.
- Click in the Name property box and enter txtEnterState.
- Click in the Caption property box and type Enter MO or IL.
- Click on the Command Button tool and click and drag to locate the button in your form (Figure A).
- Close and save your form. (In this example, we saved the form as qryFormQueryState.)
- Click on the Query object in the Database window and click New. (In Access 2007, click the Create tab and then click Query Design in the Other group.)
- Add all the fields you want to your query.
- Right-click the Criteria cell under the State field and select Build.
- Enter the following code at the prompt, as shown in Figure B:
- Click OK.
- Close and save the query. (In this example, we saved the query as Customer Query by State.)
- Open the form in Design view.
- Click the Command Button control and then click and drag to locate the control in your form.
- In the Command Button Wizard, click Miscellaneous in the Categories box, and then click Run Query (Figure C).
- Click Next. Select Customer Query by State, and then click Next again.
- Click in the text box and type Run Customer Query by State, as shown in Figure D.
- Click Next and then click Finish.
Now when service representatives want a customer list, they simply enter the appropriate state and click the Run Customer Query by State button (Figure E).
Miss an Access tip?
Check out the Microsoft Access archive and catch up on other Access tips.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.