Multiple selection

[kft10 via AccessMonster.com]

Hi,

I created a list box with 'Simple' multi select. I'd like to capture those
selections and put it on the criteria on my query. Is anyone in the forum can
tell me how to do that?

Thank you in advance.

KF

 

[Jeanette Cunningham]

Hi KF,
here is some code you can use to do that.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the query
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
End Sub

Private Sub MyQuery()
Dim strSQL as String

strSQL = "SELECT yadda, yadda, yadda " _
& "FROM yadda " & BuildWhereCondition(strControl As String)

End Sub

Notes:
replace
BuildWhereCondition(strControl As String)
with
BuildWhereCondition("NameOfTheListBox")

use strSQL as the query.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[kft10 via AccessMonster.com]

Hi Jeanette,

Thank you for the answer. I really appreciate it. I have 1 more question:
Where I have to put this code (on combo box or somewhere)?
Private Function BuildWhereCondition(strControl As String) As String

'Set up the WhereCondition Argument for the query
Dim varItem As Variant

etc....

Hi KF,
here is some code you can use to do that.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the query
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
End Sub

Private Sub MyQuery()
Dim strSQL as String

strSQL = "SELECT yadda, yadda, yadda " _
& "FROM yadda " & BuildWhereCondition(strControl As String)

End Sub

Notes:
replace
BuildWhereCondition(strControl As String)
with
BuildWhereCondition("NameOfTheListBox")

use strSQL as the query.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 6 lines]

KF

 

[Jeanette Cunningham]

The code for the function
Private Function BuildWhereCondition(strControl As String) As String
goes on the form's code module.

The code:
needs to be changed to suit your query.
Do you have a query where the first part is the select clause and
the next part is the from clause?
The next part of the query is usually the where clause.

Use BuildWhereCondition(strControl As String)
in the where clause part of the query.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

Thank you for the answer. I really appreciate it. I have 1 more question:
Where I have to put this code (on combo box or somewhere)?
Private Function BuildWhereCondition(strControl As String) As String

'Set up the WhereCondition Argument for the query
Dim varItem As Variant

etc....

Hi KF,
here is some code you can use to do that.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the query
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select
End Sub

Private Sub MyQuery()
Dim strSQL as String

strSQL = "SELECT yadda, yadda, yadda " _
& "FROM yadda " & BuildWhereCondition(strControl As String)

End Sub

Notes:
replace
BuildWhereCondition(strControl As String)
with
BuildWhereCondition("NameOfTheListBox")

use strSQL as the query.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 6 lines]

KF