Updating a listbox that is using a saved query

[cmenkedi]

Hello
I have a form that I use to generate different reports with 3 list boxes and a combo box on it. The first 2 list boxes are used to determine what if any is in the last list box. It all works the way I want it except that I would like to use the combo box to further filter the 3rd list box down.
My problem is that my list box uses saved queries. I did this because I wanted to stay away from coding all the different reports and wanted to make it easier for me to add new reports to the list in the future. I have all the information needed to open the report and even filter the report in a table.

If I had a way to pull the sql that my list box was using out, I could then add my new parameter to it.

Any help on this problem would be greatly appreciated.
Thanks
Chris
Access 2007

 

[jzwp22]

You can get the row source of the list box and assign it to a variable, something like this

Dim holdSQL As String
holdSQL = Me.lstBoxName.RowSource

 

[boblarson]

And to go with what jzwp22 has said, you can add criteria to your saved query by using:

Me.lstBoxName.RowSource = "Select * From YourSavedQueryNameHere WHERE FieldName = " & Me.ComboBoxNameHere

or if the field is text:

Me.lstBoxName.RowSource = "Select * From YourSavedQueryNameHere WHERE FieldName = " & Chr(34) & Me.ComboBoxNameHere & Chr(34)

or if it is a date:

Me.lstBoxName.RowSource = "Select * From YourSavedQueryNameHere WHERE FieldName = #" & Me.ComboBoxNameHere & "#"

 

[cmenkedi]

Thanks
I tried and it didn't work the way I wanted.
Is there a way I can pull the sql out of the query, add my new where clause to the existing where clause, and then set the rowsource to the new sql?

Again I am trying to keep from coding in for ease of update.

 

[jzwp22]

Yes, you can take the query text and assign it to a variable as I showed, add your additional criteria to the query and then assign it to the row source of the list box as Bob showed.

What didn't work as you intended?

Again I am trying to keep from coding in for ease of update

What do you mean by this statement?

 

[boblarson]

Thanks
I tried and it didn't work the way I wanted.

You know that statement is about as useful as going to the doctor and telling him, "Doc, I have a pain - can you fix it?"

Is there a way I can pull the sql out of the query, add my new where clause to the existing where clause, and then set the rowsource to the new sql?

There is a way but it is not simple and does require more coding.

Again I am trying to keep from coding in for ease of update.

I assume that you mean you want it to be more flexible than it currently is, correct?