multiple select list boxes on form - VBA code ot working

[Guest]

I'm reposting this because I have been working for over three weeks trying to
develop the VBA code and was hoping to stay in that vicinity.

My form has multiple-select list boxes (both text and numbers) that contain
customer contact info, company revenue, and sales relationships.

How do I loop the code so that the form displays (which will eventually be
exported to Excel) only the records based on the multiple criteria
selection within each box?

Any help you can offer is appreciated. I can get the code to work for one
multiple-select dropdown filter, and I can get the code to work for many text
box filters. But not for what I need.

Help!

 

[ruralguy via AccessMonster.com]

Maybe this link will help:
http://www.fontstuff.com/access/acctut11.htm

 

[Guest]

That link doesn't help because it's just for one list box. I have an array of
list boxes, all with multiple select conditions. I need the on_click( )
command to cycle through all of the list boxes, but don't know/can't find the
syntax.

Is there an example of this code anywhere?

 

[ruralguy via AccessMonster.com]

Are you looking for something like:
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acListBox Then
'-- What do you want to do with this ListBox???
End If
Next ctl

That link doesn't help because it's just for one list box. I have an array of
list boxes, all with multiple select conditions. I need the on_click( )
command to cycle through all of the list boxes, but don't know/can't find the
syntax.

Is there an example of this code anywhere?

Maybe this link will help:
http://www.fontstuff.com/access/acctut11.htm

[quoted text clipped - 14 lines]

 

[Guest]

Maybe. I will try and report back.

Thank you so much for your help. If it works, I will happily send you a cake.

Rebecca

Are you looking for something like:
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acListBox Then
'-- What do you want to do with this ListBox???
End If
Next ctl

That link doesn't help because it's just for one list box. I have an array of
list boxes, all with multiple select conditions. I need the on_click( )
command to cycle through all of the list boxes, but don't know/can't find the
syntax.

Is there an example of this code anywhere?

Maybe this link will help:
http://www.fontstuff.com/access/acctut11.htm

[quoted text clipped - 14 lines]

Help!

 

[Guest]

I can't get the code to work. I don't know how to define it, and the more I
try, the dumber I seem to become.

Is there an example somewhere that I can just download or model?

Are you looking for something like:
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acListBox Then
'-- What do you want to do with this ListBox???
End If
Next ctl

That link doesn't help because it's just for one list box. I have an array of
list boxes, all with multiple select conditions. I need the on_click( )
command to cycle through all of the list boxes, but don't know/can't find the
syntax.

Is there an example of this code anywhere?

Maybe this link will help:
http://www.fontstuff.com/access/acctut11.htm

[quoted text clipped - 14 lines]

Help!

 

[ruralguy via AccessMonster.com]

How about posting the code you use to get one multiple-select dropdown filter
to work and maybe we can figure out what code you need to do several.

I can't get the code to work. I don't know how to define it, and the more I
try, the dumber I seem to become.

Is there an example somewhere that I can just download or model?

Are you looking for something like:
Dim ctl As Control

[quoted text clipped - 16 lines]

 

[Guest]

I have been tackling the exact problem myself and finally got it to work - a
form with 4 list boxes that filters the data based on my selections. I
created a Function that builds a Where String and then passed that to the
query of the form with a Command Button that filters the records when pressed.

Private Function WhereString() As String
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 As String
Dim varItem As Variant

On Error Resume Next

' ... build "Make" criterion expression
If Me.lstMake.ItemsSelected.Count > 0 Then
strWhere = strWhere & "Make IN ("
For Each varItem In Me.lstMake.ItemsSelected
strWhere = strWhere & "'" & _
Me.lstMake.ItemData(varItem) & "', "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND "
End If

If Me.lstModel.ItemsSelected.Count > 0 Then
strWhere1 = strWhere1 & "Model IN ("
For Each varItem In Me.lstModel.ItemsSelected
strWhere1 = strWhere1 & "'" & _
Me.lstModel.ItemData(varItem) & "', "
Next varItem
strWhere1 = Left(strWhere1, Len(strWhere1) - 2) & ") AND "
End If

If Me.lstGroup.ItemsSelected.Count > 0 Then
strWhere2 = strWhere2 & "SDescription IN ("
For Each varItem In Me.lstGroup.ItemsSelected
strWhere2 = strWhere2 & "'" & _
Me.lstGroup.ItemData(varItem) & "', "
Next varItem
strWhere2 = Left(strWhere2, Len(strWhere2) - 2) & ") AND "
End If

If Me.lstBrand.ItemsSelected.Count > 0 Then
strWhere3 = strWhere3 & "Brand IN ("
For Each varItem In Me.lstBrand.ItemsSelected
strWhere3 = strWhere3 & "'" & _
Me.lstBrand.ItemData(varItem) & "', "
Next varItem
strWhere3 = Left(strWhere3, Len(strWhere3) - 2) & ") AND "
End If

WhereString = strWhere & strWhere1 & strWhere2 & strWhere3
If Len(WhereString) > 0 Then
WhereString = " WHERE " & Left(WhereString, Len(WhereString) - 5)

End If

End Function

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strRecordSource As String
On Error Resume Next

strRecordSource = "qryModelSearch"

' move focus to clear button
Me.cmdClear.SetFocus

' build sql string for form's RecordSource
strSQL = "SELECT * FROM " & strRecordSource & _
WhereString()

Me.RecordSource = strSQL

End Sub

I can't get the code to work. I don't know how to define it, and the more I
try, the dumber I seem to become.

Is there an example somewhere that I can just download or model?

Are you looking for something like:
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acListBox Then
'-- What do you want to do with this ListBox???
End If
Next ctl

That link doesn't help because it's just for one list box. I have an array of
list boxes, all with multiple select conditions. I need the on_click( )
command to cycle through all of the list boxes, but don't know/can't find the
syntax.

Is there an example of this code anywhere?

Maybe this link will help:
http://www.fontstuff.com/access/acctut11.htm
[quoted text clipped - 14 lines]

Help!

 

[Guest]

Here's the code that works for one multiple select. I mimicked the code from
a sample database. It doesn't filter the form directly (which would be ideal
if I could figure out how to do it) but rather creates a temporary query that
filters the records based on that criteria (and takes forever but is good
enough for me).

Private Sub cmdOpenQuery_Click()


On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM CompanyRegion"

'Build the IN string by looping through the listbox
For i = 0 To LSTRegAv.ListCount - 1
If LSTRegAv.Selected(i) Then
If LSTRegAv.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LSTRegAv.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Region] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCompanyRegion"
Set qdef = MyDB.CreateQueryDef("qryCompanyRegion", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyRegion", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.LSTRegAv.ItemsSelected
Me.LSTRegAv.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "Please select from the list" _
, , "Selection Required."
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

 

[ruralguy via AccessMonster.com]

Thanks for posting the code Rebecca. That helps quite a bit. Are all of
your ListBoxes regions? If not then what is contained in the other ListBoxes?
Does Stu's post help?

Here's the code that works for one multiple select. I mimicked the code from
a sample database. It doesn't filter the form directly (which would be ideal
if I could figure out how to do it) but rather creates a temporary query that
filters the records based on that criteria (and takes forever but is good
enough for me).

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM CompanyRegion"

'Build the IN string by looping through the listbox
For i = 0 To LSTRegAv.ListCount - 1
If LSTRegAv.Selected(i) Then
If LSTRegAv.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LSTRegAv.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Region] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCompanyRegion"
Set qdef = MyDB.CreateQueryDef("qryCompanyRegion", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyRegion", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.LSTRegAv.ItemsSelected
Me.LSTRegAv.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "Please select from the list" _
, , "Selection Required."
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

 

[Guest]

Stu's post didn't help me.

Not all of the list boxes will be regions. There are a lot of list boxes I'd
like to add (about 5), which include CompanyNumber (number field),
CompanyName (text field) AccountExecutive (text field) CompanyRole (text
field) and Revenue (number field).

If I can just figure out how to do it for two (or three), I feel like I can
make the whole thing work.

Or give up and become a hobo.

 

[ruralguy via AccessMonster.com]

Hobo is nice. 8^)
If I suggested that instead of eliminating the WHERE clause when the user
select ALL that you include *all* of the entries in the list box in your IN()
clause, does that help? It sounds like each ListBox might be unique and
therefore require a unique Where clause preceeded with AND if the previous
ListBox already has started the WHERE clause. Will all of the WHERE clauses
have the IN() clause?

 

[Guest]

I got it to work. I just inserted a bunch of Else If conditions and it seems
to work fine.

Now I need to figure out how to add an Export Selected to Excel option
button. Advice?

 

[ruralguy via AccessMonster.com]

That's great! Glad you got over that stumbling block. As for your next task,
beyond my direct experience. You may wish to start another thread for this
to get some fresh eyes looking at the Export problem.