Multi-Select Listbox as filter for Query field (1 Viewer)

[eadavis87]

Alright, lets see if I can explain this. I've searched various forums as it seems others have had similar issues.

{Photos Attached}

On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.

What is the best way to code this? I'm a beginner when it comes to VBA and have tried modifying suggestions from other threads with 0 luck.

 

[pbaldy]

If it's for a report I'd do this:

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

 

[eadavis87]

I input my names

Private Sub CMD_Select_Forecast_Report_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made

If Me.WBS_Select.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one WBS# to Continue."
Exit Sub
End If

'add selected values to string

Set ctl = Me.WBS_Select
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

'trim trailing comma

strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items

DoCmd.OpenReport "Select Forecast Report", acViewPreview, , "WBS_Display(" & strWhere & ")"
End Sub

When I run it, I get syntax Error and the debugger highlights the last line.

 

[pbaldy]

For starters, you want one of these, not both:

strWhere = strWhere & ctl.ItemData(varItem) & ","
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

Looks like you value is text, in which case you'd use the second. You also missed the bit in red:

"EmpID IN(" & strWhere & ")"

 

[eadavis87]

For starters, you want one of these, not both:

strWhere = strWhere & ctl.ItemData(varItem) & ","
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

Looks like you value is text, in which case you'd use the second. You also missed the bit in red:

"EmpID IN(" & strWhere & ")"

Caught the "In" as you posted. Have line

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "'," because it is text.

When I run, I get a msgbox asking for parameters for [WBS_Display]

 

[pbaldy]

That would imply that field is not in the report's record source. Is it perhaps a space rather than an underscore in the field name?

 

[eadavis87]

Something like that...

IT WORKS!!!

Thank you!

 

[pbaldy]

Happy to help!

 

[papic1972]

Hi Pbaldy,

I was having some syntax error issues (crossing over from Access 2003 to Access 2010) with my multi select listbox. I tried using your code from your Baldyweb site, it helped to eliminate the syntax error, however I cannot get your code to filter my report, instead it shows all records. Could you please have a look at my code & see where the error could be:

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click


Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.List58.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one contract to build report.", vbCritical, "Message from ZP"
Exit Sub
End If

'add selected values to string
Set ctl = Me.List58
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptMarginReport", acPreview, , "ContractID IN(" & strWhere & ")"


Exit_cmdPreview_Click:
Exit Sub
Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click



End Sub


The query SQL is:

SELECT tblContract.ContractID, tblLocal.LoadLineID, tblLocal.Driver, tblLocal.DeliveryDate, tblLocal.Qty, tblLocal.SubRate, tblLocal.SubPayment, tblLocal.OrderID, tblLocal.Suburb, tblContract.Contract, tblLocal.Yard, tblLocal.Plant, tblLocal.MinQty, tblDrivers.DriverID, tblLocal.AreaKlm, tblLocal.BrickType, tblLocal.WorkSortOrder, tblLocal.DocketNo, tblLocal.TimberSisFlexi, tblLocal.JDRate, tblLocal.SubRate, tblLocal.JDPayment, tblLocal.SubPayment, tblLocal.Balance, tblDrivers.SubContractor, tblDrivers.GroupNo, tblDrivers.GroupName, tblLocal.BrickType, IIf([GroupNo]=1,"JD Drivers",IIf([GroupNo]>1,"All Other Subys","All Other Subys")) AS GroupNameforReport, tblLocal.AmountPaid1, tblLocal.AmountPaid2, tblLocal.OutstandingBalance, tblContract.ReportableName
FROM (tblLocal LEFT JOIN tblContract ON tblLocal.ContractID = tblContract.ContractID) LEFT JOIN tblDrivers ON tblLocal.Driver = tblDrivers.DriverName
WHERE (((tblLocal.Driver) Is Not Null) AND ((tblLocal.BrickType) Not Like "PP" And (tblLocal.BrickType) Not Like "C/OUT" And (tblLocal.BrickType) Not Like "M*M" And (tblLocal.BrickType) Not Like "PPTF")) OR (((tblLocal.Driver) Is Not Null) AND ((tblLocal.BrickType) Is Null))
ORDER BY tblLocal.WorkSortOrder;

is this not working correctly due to the joins in the query? ContractID is numerical.

Many thanks!

 

[pbaldy]

Looks okay offhand. Can you post the db here?

 

[papic1972]

I can't post it, it's pretty large & it's connected to SQL Server backend. It's driving me banana's trying to get this to work!!

 

[papic1972]

Hey pbaldy, I've solved it! I decided to recreate my report. My original report had a macro on the OnOpen event which triggered a Start/End Date filter. I removed the macro & coded the filter into the query instead, that fixed it. It all works!! Thank you for the brilliant code from your site!!

 

[pbaldy]

Happy to help!

 

[irsmalik]

Dear Friends............. All senior programmers are requested to plz help.

I am new in Access programming. I was stuck but your page for Multi Select Listbox gave me idea, I am unable to complete my work. I am wanting your help. If you help me I will be able to solve my problem of List box.

I am selecting Company Name from Combo Box OK
Then Selecting Region Name from Combo Box OK
Then selecting Territory Name from Lixt Box (One or many)
but Report not working


Form5 with Single selection of Territory is working good
Form006 with Multi Select Territory is not working ..

An other idea is to put "*" in Territory List Box to print ALL Territories.

A humble request to please solve my problem. My DB is attached. Hoping for an early response.

Thanks in advance
Regards
irsmalik

 

[pbaldy]

Your listbox is returning the text value, not the ID expected by the button code. That's because of this:

Me.txtTerritory.RowSource = "SELECT DISTINCT TerritoryName FROM

You either need to switch to using the name, or change this to return the ID and name, with the ID hidden.

 

[irsmalik]

Sir
As a new programmer I do not know how to do it.
That's why I tried to copy your Code.
Kindly write me the Code line that I will put in my Territory List Box so it may work fine.

Thanks

 

[pbaldy]

I did, in the code you got from my website (it's still commented out in your code):

'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

 

[irsmalik]

Dear Sir Pbaldy

I follow your code and Multi Select is selecting but Report Preview is giving error. Sir Please help me and Correct this code as I am using txt of my Territory field.

Private Sub Preview_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.txtTerritory.ItemsSelected.Count = 0 Then
MsgBox "Kindly Select at least 1 Territory"
Exit Sub
End If

'add selected values to string
Set ctl = Me.txtTerritory
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "Demonst Report1New", acPreview, , "TerritoryName IN("" & strWhere & "")"

End Sub

Last line of DoCmd is giving error. Sir you are requested to please correct it.
Thanks in advance
irsmalik

 

[pbaldy]

You added extra quotes for some reason. Try

DoCmd.OpenReport "Demonst Report1New", acPreview, , "TerritoryName IN(" & strWhere & ")"

 

[irsmalik]

Sir....... I changed the code as you corrected
DoCmd.OpenReport "Demonst Report1New", acPreview, , "TerritoryName IN(" & strWhere & ")"

but still No Report Preview. It goes to Debug Mode and this line is Yellow Highlighted. All three fields are Text.

All three fields are Text
Company Combo Greenlet/Suncrop
Region Combo Hyderabad
Territory Combo Matli, Hala

sir I upload it please have a look where I am making mistake.
Data

Thanks.
irsmalik