How to use a column from a listbox as criteria for a query

[sk8er1]

I have a form with a listbox. The listbox contains 2 columns. I would like to run a report with the contents of the listbox. So, I have a query and I would like to filter the criteria with the column from the listbox ....

Is this possible in the QBE?


[forms]![frmSearch_Requestor]![lstSelected].Column(1)

 

[dhookom]

You can only use the bound column in this manner. You would remove the ".Column(x)" from the criteria.

If you have a need to use the unbound column, you can create an invisible text box on the form with a control source like:
=lstSelected.Column(x)
Use this hidden text box as the criteria in the query.

This all assumes the list box is not multi-select.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gol4]

Duane points out an excellent method for doing it via the QBE.

another method is to pass the criteria via the openreport method. This method will also work on a multiselect list box

Example

'build string from list box
Dim stcrit As String
Dim itm 
If Me!List1.ItemsSelected.Count > 0 Then 
For Each itm In Me.List1.ItemsSelected
stcrit = stcrit & "'" & List1.Column(1,itm) & "', "
Next itm
stcrit = Left(stcrit, Len(stcrit) - 2)
stcrit = "Facility in (" & facstr & ")"
DoCmd.OpenReport "myreport", acViewPreview, , stcrit
end if

 

[sk8er1]

Hi

Thanks for the response. So, on the form that has my listbox, I can create a button to invoke the report.
In the click event, I would build code based on your code snippet...

One question...what are you doing with this line...
stcrit = Left(stcrit, Len(stcrit) - 2)

Thanks ...I will try it out in the morning.

Ed

 

[dhookom]

The code proposed by gol4 continues to build a concatenate list of selected items with a single quote and comma between each. After looping through the selected items, there is a remaining "', " at the end like:
[tt][blue]'Red', 'White', 'Green', '[/blue][/tt]
Left(stcrit,Len(strcrit)-2) changes the expression to:
[tt][blue]'Red', 'White', 'Green'[/blue][/tt]

There is similar, generic code at faq703-3936.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sk8er1]

Ok thansk for the explanation, one more thing...
I ran thru the code in the debugger, and I'm not sure
I understand whats happening in the following line..

stcrit = "Facility in (" & facstr & ")"

In my case it would be
stcrit = "Question in ( )"
What value is held in facstr?

Thanks

 

[sk8er1]

One last thing...

If I open the report this way, do I leave the recordsource property (for the report) blank?

 

[dhookom]

Actually the facstr should be replaced by strCrit. Your code would be
stCrit= "[Question] In (" & strCrit & ") "
This assumes your [Question] field is text. If the field is numeric, you need to remove the single quotes from within the For Next loop:

'build string from list box
Dim stcrit As String
Dim itm 
If Me!List1.ItemsSelected.Count > 0 Then 
   For Each itm In Me.List1.ItemsSelected
       stcrit = stcrit &  List1.Column(1,itm) & ", "
   Next itm
   stcrit = Left(stcrit, Len(stcrit) - 2)
   stcrit = "[Question] in (" & strCrit & ")"
end if
DoCmd.OpenReport "myreport", acViewPreview, , stcrit

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gol4]

Sorry about that error. Pasted some of that in and missed that in the edit.

do I leave the recordsource property (for the report) blank?

no recordsource needs to be based on something either a table or a query. All we are passing is the where condition.
wherecondition A string expression that's a valid SQL WHERE clause without the word WHERE. look up openreport in access help and it should clear it up for you.

 

[sk8er1]

I had a feeling...but was not sure. Thanks for the help.