Report for multiple listbox selections

[waymond]

I have a report that is grouped by objects, so it prints all the objects. The client wants a way to select certain objects like arnight, cmxlau45 etc and only print those reports. How do I do this? Do I have to use a query and listbox if so how?

thank You

 

[dhookom]

There is at least one faq in this forum that describe how to use a multi-select list box to select records to print in a report. Check this one 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]

 

[FancyPrairie]

Check out this FAQ. It contains a function that handles single and multi-select list boxes, combo boxes, ranges, text boxes, option groups, and check boxes. It returns the Where clause without the word Where. It's all based on the Tag property. You only have to do 3 things to make it work.

1. Open a new module and copy the code from the FAQ and paste it into your new module.
2. Define the tag properites of the controls as specified in the FAQ.
3. Open your report like this: DoCmd.OpenReport "ReportName",,,BuildWhere(Me)

 

[waymond]

What FAQ are you referring to please
thank you

 

[FancyPrairie]

Sorry 'bout that. Forgot to include it. Here it is FAQ181-5497

 

[waymond]

I have the following code for the listbox but when I click the command button it brings up the reports with no data?

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & " [ObjectID] = " & ListFilter.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function


Private Sub Command4_Click()
DoCmd.OpenReport "UpdatedRptonQuery", acViewPreview, , GetCriteria()
Please help the report is based on a query.
thank you

 

[dhookom]

You need to learn how to debug your code. Every veteran programmer does this a lot to find errors. You can add debug.print to your code to display the contents of a memory variable in the debug window.

You can then review the value and possibly paste it in a query window or whatever to trouble-shoot.

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & " [ObjectID] = " & ListFilter.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
Debug.Print stDocCriteria
End Function


Private Sub Command4_Click()
DoCmd.OpenReport "UpdatedRptonQuery", acViewPreview, , GetCriteria()


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]

 

[waymond]

n the debug window it says my varItem is empty. What does this mean? How can I fix this or what should I be looking at to fix this situation.
Please help
thank you

 

[dhookom]

Is varItem empty while the code is running or after. Try to get the value after is too late.

I thought you would see the value of stDocCriteria as my code suggests.

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]