Filtering an Access subform with a combo box and an extended list box for selecting multiple items

[mrbps137]

Hello,

I am new to the forum and pretty new to using VBA in Access. I have a database that I have been using to store data, connect to some sources, query it, run updates. I am looking to make the Db more user friendly in case members of my team want to utilize it to extract data they need. I have more projects I would like to do, but for starters i was trying to do a form for my table of YTD actuals (this probably 70 columns of data that is detailed lines of financial actuals coming from the general ledger). I created the form and added in a subform of the YTD actuals table. I saw on some sites people doing a combo box that added a <select multiple> option that then made a extended list box visible in case you wanted to select multiple items. My thoughts currently are that i would want users to be able to select their Product (short text field in the Tbl_YTDActuals)... i would eventually like to duplicate this so they can also filter down the month(s) they want.

Any help is greatly appreciated. I have been struggling for a couple weeks now working on this in between other things.



The combo box works great and as i select one or another the subform filters, but when i choose select multiple.. the subform does not filter and i get a syntax error message. The strange part i feel is that when i hover over the hightlighed line in the debugger (in red below).. the recordsource shows the item i selected in the combo box prior to doing select multiple.

Here is the code currently in the Db:

Private Sub cboProduct_AfterUpdate()
Me.Refresh


If Me.cboProduct = "<Select Multiple>" Then


Me.FormHeader.Height = 3000
Call Clearlist(ListProduct)
Me.ListProduct.Visible = True
Me.Searchbtn.Visible = True
Me.ListProduct.Height = 3000

Else
Me.ListProduct.Visible = False
Me.Searchbtn.Visible = False
Me.ListProduct.Height = 0
Me.FormHeader.Height = 200

Call Search
End If
End Sub
______________________________________________
Sub Search()
Dim myProduct, strSQL, strCriteria As String


If IsNull(Me.cboProduct) Then
'do nothing
ElseIf Me.cboProduct = "<Select Multiple>" Then
strCriteria = "[Product] in (" & TempVars!TempMultiProduct & ")"
Else
myProduct = "[Product] = '" & Me.cboProduct & "'"
strCriteria = myProduct
End If
strSQL = "select * from Tbl_YTDActuals where (" & strCriteria & ")"
Me.SubformYTDActuals.Form.RecordSource = strSQL
Me.SubformYTDActuals.Form.Requery
End Sub
_______________________________________________
Private Sub Form_Load()
Me.ListProduct.Visible = False
Me.Searchbtn.Visible = False
Me.FormHeader.Height = 700
Call EmptySubform
End Sub
_______________________________________________
Sub EmptySubform()
Dim mySQL As String


mySQL = "select * from Tbl_YTDActuals where [Product] = 'XXXXXX'"
Me.SubformYTDActuals.Form.RecordSource = mySQL
Me.SubformYTDActuals.Form.Requery
End Sub
_______________________________________________
Private Sub ListProduct_AfterUpdate()
Dim varItem As Variant
Dim strCriteria As String
Dim TempMultiProduct As TempVar


For Each varItem In Me!ListProduct.ItemsSelected
strCriteria = strCriteria & "," & Me!ListProduct.ItemData(varItem) & ""
Next varItem
If Len(strCriteria) = 0 Then
Exit Sub
Else
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
TempVars!TempMultiProduct = strCriteria
MsgBox (TempVars!TempMultiProduct)
End If
End Sub
______________________________________________
Private Sub Searchbtn_Click()
Call Search
End Sub

[pbaldy]

You can use this method to see/test the contents of the variable. If you don't spot the error, post the SQL here.

https://www.baldyweb.com/ImmediateWindow.htm

[Welshgasman]

Also please use code tags when posting more than a couple of lines of code.
The # button.