Access 2000 Query

  ihbuk1 13:21 14 Aug 2003
Locked

I want to design a query that will give users a drop down list of items of which they can pick one of the items and it will then produce specified information for the chosen item.

Any ideas?

Thaanks

  The Transporter 13:43 14 Aug 2003

to create a drop down box and then click a button to run what happens to be selected from the drop down box.

You have to firtly create a table.

Create a new one in design view. and make sure that the first column is a number (type) and the second column is a text (type)

then :

Create an unbound form, by double clicking on 'Create form in design view'

Now add the combo box(es) you need to set the parameters of the query.
Just click on the combo box button on the toolbar, the position your cursor
on the form, where you wish the combo box to be situated, then click and drag
to the size you want.

Follow the wizard, selecting the following.

'I want the combo box to look up the values in a table or a query.' > Next

Select the table which has the field you wish the combo box to have. > Next

Select the field you want by double clicking it. > Next and Next again

Give the combo box a label, this will be the label which will appear on your form.
> Finish.

**************************
**************************

The next step is to add a command button to the form which will run the query.

To do this:

Just click on the command button on the toolbar, the position your cursor on the form, where you wish
the command button to be situated, then click and drag to the size you want.

Follow the wizard, selecting the following.

Categories - select 'miscellaneous' Actions -select 'Run Query' > Next

Highlight the query, you wish to run > Next

Give the command button either a picture or text. (You can change this later, if you change your mind.) > Next

Give the command button a name > Finish

Close, Name and save the form.

***********************************

Now set the following properties for the form.

To open the properties section of the form, in design view, right click on the upper left hand corner of the form,
where the two rulers meet a black square will appear along with a drop down list. Select properties.

Select the 'Other' tab. In the Popup property box, click Yes. In the Modal property box, click Yes.

Select the 'Format' tab.

In the BorderStyle property box, select Dialog. If you select the Dialog setting, the form has a thick border and
can include only a title bar and a Control menu. The form can't be maximized, minimized, or resized.

*************************************

Now add the parameter(s) to the query.

Open the query in design view.

In the parameter row under the field you wish to set the parameter for, right click in this section and select 'Build'


In the three columns (boxes) at the bottom of the Expression builder in the first column, select the unbound form
you have just made, by highlighting it. In the second column select the name of the combo box you made on the form,
which corresponds to this parameter. Double click this, this will place it in the top of the window area. Press OK.

The parameter should now appear in the area selected.

Close and save the query.

*************************************

Open the form, make a selection from the combo box and press the command button to see if everything is working.

**************************************

Now, Create a macro or event procedure that opens the form, when you wish the query to be run.

**************************************

  IanNiblock 16:02 14 Aug 2003

The Transporters solution will do exactly what you require, I though I would offer another option anyway. This idea is fine if you are only interested in displaying the data, however if you will need to produce a report based on your selection it may be better to use the idea presented by The Transporter.

I am assuming you already have a table(s) containing the information you require.

1. Create a query to select all the information that you want (including the information for the drop down box)

2. Create a form using the form wizard and select the query created above as the source for that form

3. Once the wizard has created the form for you open it in design view and add a combo box from the toolbox (making sure that the magic wand button is pressed in the toolbox)

4. Another wizard will appear - select the bottom of the 3 options (Find a record on my form based on the value I selected in my combo box) then follow the wizard through to it's conclusion.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?