Use selection from drop down list as parameter for another query.

[ardagh]

I want to be able to choose an item from a drop down list (based on a query i.e. distinct products) and have the item chosen to be automatically used as a parameter in another query.

 

[M8KWR]

to use the drop down list as a parameter in another query, in the second query you would need to put this into the criteria of the field you are filtering on: -


[forms]![FORM_NAME]![FIELD_NAME]

hope this helps. Any issues let me know.

 

[ardagh]

Hi

Thanks for your input but I can’t get it to work. Do I need to put an on_click or an After_update event in the first box to run the second query (qrysum) and populate the second list box.

Here is my setup below.


I have a form called frmsearchNew.

I have a combo box called pickpart to give a list of distinct part numbers.
(This list box is based on a simple select distinct query).

User should be able to select a part no in the first combo box and the chosen part will be used in a second query whose results will be displayed in a list box in the same form.

The second query is called qrySum
I have put in the code you suggested [forms]![frmsearchNew]![pickpart] in the field required – see SQL below - but I get nothing returned from the query no matter what part I choose. When I try the query with out this line
HAVING (((tblstock.[MaterialPart#])=[forms]![frmsearchNew]![pickpart])) I get all the data.

Example with line
SELECT tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location, Sum(tblstock.Qty) AS SumOfQty
FROM tblstock
GROUP BY tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location
HAVING (((tblstock.[MaterialPart#])=[forms]![frmsearchNew]![pickpart]))
ORDER BY tblstock.[MaterialPart#] DESC;

Query minus line which gives all data
SELECT tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location, Sum(tblstock.Qty) AS SumOfQty
FROM tblstock
GROUP BY tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location
ORDER BY tblstock.[MaterialPart#] DESC;


Any ideas on what I'm doing wrong

Thanks

 

[M8KWR]

i have tested it with the same form and combo box name, and its work fine for me.

Are you moving of the combo box field before you run the second query "qrySum".

Until you move of the combo box, the value woould not be stored.

Double check your spelling etc, but it you have copied and pasted it straight from the sql, then it should be fine.

Let me know how you get on..

 

[ardagh]

Still no luck. Is there anyway I can attach a small example database to show exactly what I'm trying to do.

 

[PHV]

You may try this in the AfterUpdate event of the pickpart control:
[name of 2nd list control].RowSource = "SELECT [MaterialPart#],[Lot#],Location" _
& ",Sum(Qty) AS SumOfQty FROM tblstock" _
& " WHERE [MaterialPart#]='" & [pickpart] & "'" _
& " GROUP BY [MaterialPart#],[Lot#],Location"

If MaterialPart# is defined as numeric in tblstock then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244