Is it possible to open a report using a listbox?

[HTFC]

Hi all,



Judging from other questions and answers on this forum, the answer is yes, however I can't quite work out how.

I have a report called Player Apps that, at the moment, is opened in the right season by the user typing in the season that they want into the Message box (I think it is called that anyway!) but as the season's grow I would like it to use a list box to display the available seasons (these are on a table) so that the user just needs to point and click and hey presto that report opens!

I guess that the list box needs to be made as a form but then how that form is called up (from a command button I would guess) and then what code is needed to choose the right season and the right report to open!

I have a sneaking suspicion that it is something to do on the click function but not 100% sure.

Thanks for all or any help offered.

[jzwp11]

Typically you would use an unbound form (not tied to a table). On that form you would place a combo box based on your season table. You will need a command button also that opens a report. You would filter the report by the selection made in the combo box. I just created the attached database for another thread that illustrates the technique (I used a table of people rather than seasons, but the principle is the same). See the form frmSelectOneOrAll

[HTFC]

That is some really clever stuff, however, try as I might to alter your code I am unable to do so.

I have only one field which is called Season, how do I get it to work. I know it should be easy and I can follow your logic but mine falls apart at the critical moment!

Any help again will be greatly appreciated.

[jzwp11]

Without more details on your table structure and how the process falls apart, it is difficult to diagnose. Could you zip and post a copy of the database with any sensitive data removed/altered so that we can see what is going on?

[HTFC]

Posting it will be difficult but I will try to explain a little more.

The table that I want the list box to read from is called tbl_season and the field is called Season.

I tried to alter your code so it looked like this: SELECT 0, "ALL" FROM tbl_season UNION SELECT tbl_season.pkseason & ", " FROM tbl_Season

But it didn't work (which if I have done - it usually doesn't!)

[jzwp11]

You will have to construct the query in the SQL view window & you are missing the season field and you do not need the ","

SELECT 0, "ALL" FROM tbl_season UNION SELECT tbl_season.pkseason, tblseason.season FROM tbl_Season

[cactuspete13]

jzwp11 - This was awesome, I was having the same problem that HTFC was having and this solved it instantly. I am now using it on several projects I am working on. However I am running into a roadblock applying it to one. I did the same thing I have done with another, copy combobox, button, and vba code, change the vba code (replacing with new names), changing the query. But when I hit the search button, instead of pulling up the reports using the name I am trying to pull up, I get message box asking for parameter value with the name I am trying to pull up above. If I type that name in again it works but if I leave it blank it pulls up a blank report. . I am trying to open the report named Informal Report based on which Case Owner is selected. The combobox is a list of list of Case Owner's in the system (the query the combobox is using is counting how many times an owner appears so no duplicates). Below is the VBA Code and I've attached a few screenshots.

Private Sub cmdReport_Click()
'define some variables
Dim lngloop As Long
Dim strIDs As String

If Me.lstPeople.ItemsSelected.Count <> 0 Then
If Me.lstPeople.ItemsSelected.Count > 0 Then
For lngloop = 0 To Me.lstPeople.ItemsSelected.Count - 1
'if the first selected item is found lngloop=0 then assign it to strIDs; for the second and subsequent selected items add a comma then the ID
If lngloop = 0 Then
strIDs = strIDs & Me.lstPeople.ItemData(Me.lstPeople.ItemsSelected(l ngloop))
Else
strIDs = strIDs + "," & Me.lstPeople.ItemData(Me.lstPeople.ItemsSelected(l ngloop))
End If
Next lngloop
End If
'add opening and closing parenthesis
strIDs = "(" & strIDs & ")"
End If
'now open the report filtered with the above collected IDs
DoCmd.OpenReport "Informal Report", acViewPreview, , "CaseOwner in " & strIDs
End Sub

Thanks for the help

[jzwp11]

What is the bound field of the listbox? Is it a number or the actual name of the person as shown in your attachment? The code I provided assumes that the bound field is numeric, so that strIDs will look like this (1,3,2,6,7). If the bound field is text, then you must enclose each selected item in single quotes like this ('Smith', 'Jones', 'Miller')

Modifying the code for text values would look like this:

For lngloop = 0 To Me.lstPeople.ItemsSelected.Count - 1
'if the first selected item is found lngloop=0 then assign it to strIDs; for the second and subsequent selected items add a comma then the ID
If lngloop = 0 Then
strIDs = "'" & strIDs & Me.lstPeople.ItemData(Me.lstPeople.ItemsSelected(l ngloop)) & "'"
Else
strIDs = "'" & strIDs + "'," & Me.lstPeople.ItemData(Me.lstPeople.ItemsSelected(l ngloop))
End If
Next lngloop

[cactuspete13]

Awesome! But when I try to select multiple I get a missing operator syntax error. When I open up the VBA to debug, the entire OpenReport line is highlighted. What am I missing to set it up to recall multiple text searches. Works fine with numerical values.

[jzwp11]

I'm not sure what is going on. I would recommend putting in a debug.print strIDs statement just prior to the OpenReport command. This will push the value in strIDs to the VBA immediate window. That will allow you to check the syntax of what is in strIDs. Are there any single quotes or other special characters within the text values being selected?

[cactuspete13]

No special characters. The error message says: Run-time error '3075': Syntax error (missing operator) in query expression 'CaseOwner in ("Jones',Smith')'.
The Open Report line reads: DoCmd.OpenReport "Informal Report", acViewPreview, , "CaseOwner in" & strIDs, acDialog

[pbaldy]

I think jz goofed the quoting (it's adding too many to the beginning and missing the last ending). I do it this way:

http://www.baldyweb.com/multiselect.htm

but to fix jz's I think the Else line becomes:

strIDs = strIDs & ",'" & Me.lstPeople.ItemData(Me.lstPeople.ItemsSelected(l ngloop)) & "'"

[cactuspete13]

pbaldy - That got it. Thanks for the help.

[pbaldy]

Happy to help!

[cactuspete13]

One more question, and this one might be a little more interesting. I am trying to use this feature to apply a filter to a query that feeds a report. A query pulls three columns of info from a table, including the field Company which lists their company and department. The report has a series of text blocks with the source code counting how many employees are with each company. I am trying to set it up so that a user selects a department from the list, and the query applies that filter to show only that department from all companies. Below is the code, when I try to run it I get a debug error highlighting the apply filter line and giving me an error 2465.

DoCmd.OpenQuery "PersonnelInfoQ"
DoCmd.ApplyFilter , "Company in " & strIDs, [Company]
DoCmd.OpenReport "CountReport", acViewPreview, , , acDialog