Parameter query

[bgcogen]

Hi.
I have this query that is used to generate a report. But there are five different locations like say "London" and "Paris".

I want to create a parameter query that promps the user for the location, and only displays the records in that particular location.

I can do that part grand in the criteria part of the query. But my question is: I need for the user to be prompted for a combo box with the five different locations instead of typing it in. Does anyone know if this can be done????


Thanks a million for any help,
D

 

[Rich@ITTC]

Hi

Use a form for the parameter dialogue box.

Create a form, could be quite small, that has a combo box on it. This combo box draws on the list of the five locations (via a small lookup table usually - though you can 'hard code' in the values if you know they will never change).

In you paramater query you simply refer to the form and the combo box value e.g.

=Forms!frmSelectCity!cboCity

The query will take the selected value as the text value for your parameter query then.

HTH


Rich

 

[bgcogen]

Combo Box thing

Hi.
Yea, I tried that, but it doesn't work. When I typ ein the command
=Forms!frmSelectCity!cboCity

into the Query criteria section, it puts them into [ ].

What can I do????

 

[cogent1]

That's OK, it does put them into square brackets... The form has to be open as well , of course..

 

[bgcogen]

Still

When I put =Forms!frmSelectCity!cboCity into the field section of the query it turns to Expr1:Forms!frmSelectCity!cboCity, and still doesn't bring up the required combo box. When I put it into the criteria secton it still doesn't work!!

Is there any other way of bringing up a combo box for a parameter query???

Thanks,
D

 

[Rich]

Add your City field to the grid, add =Forms!frmSelectCity!cboCity to the Criteria for city

 

[cogent1]

Hold on!! What's going on here?


Are you sure you've got the expression in the criteria row? Access only changes the names of unidentified expressions in the field row...

The way this works is thus:
Your form is called (say)MyForm

The CRITERIA Row of your saved Query reads Forms!MYform!MyCombo. (in brackets)

You have a form open with your combo box showing. The combo is called (say) MYCombo

You select something from the Combo. Let's say BANGKOK.



Code on the AfterUpdate of the combo opens your query and shows all the records relating to Bang kok.


The combo is selected from your form NOT your query


Any luck now?

 

[bgcogen]

Problem

The problem is that when I put the line =Forms!frmSelectCity!cboCity into the creteria row it comes up as a text box with "Forms!frmSelectCity!cboCity ", in which I have enter some criteria, instead of opening up my combo box????

Whats happening??

Thanks,
D

 

[antomack]

Instead of just Forms!frmSelectCity!cboCity type [Forms]![frmSelectCity]![cboCity] and so long as you have the form with the combo open the query should run correctly.

 

[cogent1]

Does the "Text box" have "ENTER PARAMETER VALUE" as a title?

If so, your form isn't open when you run the query and the query's asking you to enter a value manually!!!

You won't see the combo when you run the query. You open the form first, make the selection from the combo and a macro or some code then runs the query and gives the results from the city you selected on the form.

To begin with, you don't even need the macro or code. Open the form and make your selection. Don't close the form!! Just open and run the query on top of the form and it'll work....


If this doeswn't work, send me the db and I'll fix it!!

 

[bgcogen]

Wahoo

Cool, it's working now, thats all for your help,

Much obliged

D

 

[bfdeal31]

Open Selection Form Automatically??

Hi,
I had a question similar to the one asked under this topic and successfully duplicated the suggested actions. However, I was wondering if it would be possible to have the form (used for selecting the city) open automatically when the query is run? That would be easier than making sure the user knows to open that form ahead of time. Thanks for your time.

Steve

 

[cogent1]

Not really applicable

In this case, it wouldn't really be logical. There's a combo on a form, and that combo provides the parameter for the query. When the combo is selected, code calls the query. The query can't call the form because there is no "code behind queries" in the same way as there's "code behind forms". You can hide the query in the database window or hide the database window itself to prevent users from running the query without opening the form first, though.

 

[bfdeal31]

that makes sense, thanks.

Thanks for replying.
What you said makes sense. I played around with what I was trying to do and now I have something that I'm happy with. Thanks.

 

[schumarg]

One more question...

Hi,

Been searching for this exact solution. Hope you don't mind one more question.

You mentioned to put code in the AfterUpdate event of the combobox on the form.

Can't seem to get this part.

Thanks,
Bob

 

[AncientOne]

can you explain what you don't understand? The code to use, or how to trigger it when the combo box is updated?

If it's the AfterUpdate that's puzzling you, go into design view and look at the properties for the combo box by right-clicking on it and selecting "properties" from the menu. Under the "Event" tab, you will see a list of events that can cause code to be launched. For example , an event can happen when the combo gets the focus, when it loses it, when the data changes, etc.

A very useful event happens when you make a new selection from the combo box to replace the existing one. The data is updated, so you may want to make something happen using the new data as a criterion. So, in the property sheet, you select "After Update" as the event and "Event procedure" as the property(there's a drop-down list.)

You are then magically transported to a blank procedure called something like Sub cboMyCombo_AfterUpdate. You type in the code you want to run:-it may open a form, or run a query to show people whose name begins with "S" or anything you need to do. Once the code is saved, every time you update the combo the code will be triggered.
(The code is saved with the form and is known as CBF or Code Behind Forms)

Hope this hasn't been too basic.

 

[schumarg]

Hi,

Thanks for your reply. After I posted the question last night, I kept working and got the code to run the query in the AfterUpdate, it will open and run the query. I did add code to not show the query data, but open, run and close. Then did a form.refresh. But the form does not show the new query data.

Still work to do here.

Not sure the form.refresh is in the right spot.

Thanks,
Bob

 

[AncientOne]

Try requerying the form if your query changes the current records.

Look up REQUERY in Help.

 

[schumarg]

Hi,

Finelly got it. Trying to get the Syntax right and in the right place kills me everytime.

It's hard when your learning this stuff by yourself. Need a mentor over my shoulder.

Thanks!!!
Bob