Query driven list box - Dynamic Query

[gibben]

Hi all,
I have a list box which is populated through a query, what I would like it to do is have the list only show values based upon a dropdown value within the form.

The theory works as far as if I add in a button to run the query it will take the value from the dropdown and produce the correct results, but if I then tie the listbox into the query it prompts for a value everytime the form opens.

The list box runs fine from the query if the query has a hardcoded value.

I am starting to think that the list box may have to be run from a query within the forms VBA coding rather than a standalone query ... would this be correct??

Thanks in advance .....

 

[fneily]

On the AfterUpdate event of the combobox type:

[ListBoxName].RowSource = ""
[ListBoxLabelName].Caption = "Annually "
[ListBoxName].ColumnCount = 4
[ListBoxName].ColumnWidths = "1 in; 1 in; 1 in; 1 in"
[ListBoxName].BoundColumn = 5
[ListBoxName].RowSource = "SELECT [Device_ID], [Eng_Units], [Date_Last_Cald], [Next_Cal_Date], [Cal_Frequency] FROM [calibration information] WHERE [Cal_Frequency] = '" & "Annually" & " ';"

Change ListBoxName and ListBoxLabelName to your listbox.
Change the caption to whatever
Change the columnCount, ColumnWidths, BoundColumn to what you want.

Ths SQL is an example. Change to your fields.
After the WHERE, set a field to Me![ComboBoxName]

Neil

 

[genaccess]

Set a new combobox with the values you want.In the query, set the criteria of the field you select in the combobox as =Forms!Combobox.
Design a macro which requery's your list box.
Put a command button that runs this macro.
That should do it.
Let me know if you need detial info. am in a hurry.

-CMT

 

[gibben]

Thanks a lot guys,

Got it working .... really appreciate the quick replies