Software

Run a parameter query within an Access form

When your users need to run a parameter query while they're working in a form, this custom button will save them some time.

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:

  1. Open the form in Design View.
  2. Click on the Text Box tool and then click and drag to locate the control in your form.
  3. Right-click the text box and select Properties.
  4. Click in the Name property box and enter txtEnterState.
  5. Click in the Caption property box and type Enter MO or IL.
  6. Click on the Command Button tool and click and drag to locate the button in your form (Figure A).

Figure A

  1. Close and save your form. (In this example, we saved the form as qryFormQueryState.)
  2. 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.)
  3. Add all the fields you want to your query.
  4. Right-click the Criteria cell under the State field and select Build.
  5. Enter the following code at the prompt, as shown in Figure B: [Forms]![frmQueryState].[txtEnterState]

Figure B

  1. Click OK.
  2. Close and save the query. (In this example, we saved the query as Customer Query by State.)
  3. Open the form in Design view.
  4. Click the Command Button control and then click and drag to locate the control in your form.
  5. In the Command Button Wizard, click Miscellaneous in the Categories box, and then click Run Query (Figure C).

Figure C

  1. Click Next. Select Customer Query by State, and then click Next again.
  2. Click in the text box and type Run Customer Query by State, as shown in Figure D.

Figure D

  1. 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).

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.

8 comments
Noor110
Noor110

I have designed a query which I want to attach with a form, from where it should obtain data for criteria field. I have applied [Forms]![frmname].[textboxname] exprension in my query for obtaining text from a text box placed on form, but it is showing blank result. what is the solution?

smkymtnguy
smkymtnguy

I can successfully run a query from my form on fields that are text entry. However, it is not working (neither is the query) on a lookup field. What is the difference and how do I do a criteria search on a lookup field in a table?

gbstarkey
gbstarkey

I built this twice however still unable to get it to work. I like the concept, su much more eloquent than a parameter query box. What i really need is for this to work, then expand on it so I can typea LIke "*" parameter lookup query in the text object. Can somenon offer me advise Thanks

paulbuko
paulbuko

I'm trying to build a form that allows the user to select from multiple combo boxes and pass that to a query. Works fine if every combo box has an entry. But if any of the combo boxes are left blank (user didn't need to search that field) then no records are pulled. I can understand that this may look like that field is to be blank as the criteria so I was experimenting with sending something like ' Like "*" '. But even though this is passing to the query properly, it doesn't work. I need some way to allow one or more of the combo boxes to remain blank and the query to see it like there is no criteria entered for that field. Any ideas?

Gren65
Gren65

Maybe you had a typo? It was AWESOME for me because I used the idea of putting the [Forms]![TransmitSurveys].[svid] into the criteria row on my query design, and the svid is a text box name on my parent form, where a subform runs the query depending upon the row selected in another row of a different subform on the parent form! Yeah!

bpsharkey
bpsharkey

Is anyone else having trouble with this? Instead of using the text field to search, my command button is inexplicably prompting users with an input box. But the input box does not provide any helpful directions (it reads out the text entered in the Criteria field from the query), and I can't figure out how to change it while still running the query. Help?

smkymtnguy
smkymtnguy

Seems that lookups are quirky. I constructed it using a combobox for the lookup field and it worked fine.

MSGRule
MSGRule

When you doi the cirteria build. Don't past the names form above, rather go through the wizard select the forms and field then it gets added correctly then works fine

Editor's Picks