Linking List box selection to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with two multi select list boxes. I want to be able to use the selection made in the list boxes to act as criterion for the query behind it's sub-form.

I can get it to work for one list box with one selected item. However, I cannot get it to work when multiple items are selected. nor can I cancatenate the values of one list box with another.

Here is 1 of the coding that I was trying to use:
********
Private Sub List0_AfterUpdate()
'Returns a listing of the items selected from the list box (query criteria format)
Dim varItem As Variant
Dim strList As String

With Me!List0
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & " OR " & """[Lead Signature] Like '" & .Column(0, varItem) & "'"
Next varItem
End If
End With

strList = Right(strList, Len(strList) - 4) & """"
MsgBox strList

Me.Lead_tool_03_subform.Form.Filter = strlist
Forms![Lead Summary Drawing Information frm]![Lead tool 03 subform].Form.FilterOn = True
End Sub
********

can someone please spell it out for me. I've already spent 1.5 day on this to get nowhere. Is there a sample database somewhere that I could down load to greatly inspire myself?!

thanks

Daniel
PS: Don't like the new format of the forum! I find the postings messy ... what happened to the spacings and enters .... not much of an improvement in my opinion
 
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb" which might help. "Form 6" uses a multiselect listbox
to create the WHERE clause for the query. Also, the sample
"MultiSelect.mdb" gives some background information.

PS: I use Outlook Express as a newsgroup reader and I don't use the web
interface. I'm really happy with how they look.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Daniel said:
I have a form with two multi select list boxes. I want to be able to use
Click to expand...
the selection made in the list boxes to act as criterion for the query
behind it's sub-form.
I can get it to work for one list box with one selected item. However, I
Click to expand...
cannot get it to work when multiple items are selected. nor can I
cancatenate the values of one list box with another.
Here is 1 of the coding that I was trying to use:
********
Private Sub List0_AfterUpdate()
'Returns a listing of the items selected from the list box (query criteria format)
Dim varItem As Variant
Dim strList As String

With Me!List0
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & " OR " & """[Lead Signature] Like '" & ..Column(0, varItem) & "'"
Next varItem
End If
End With

strList = Right(strList, Len(strList) - 4) & """"
MsgBox strList

Me.Lead_tool_03_subform.Form.Filter = strlist
Forms![Lead Summary Drawing Information frm]![Lead tool 03 subform].Form.FilterOn = True
End Sub
********

can someone please spell it out for me. I've already spent 1.5 day on
Click to expand...
this to get nowhere. Is there a sample database somewhere that I could down
load to greatly inspire myself?!
thanks

Daniel
PS: Don't like the new format of the forum! I find the postings messy ...
Click to expand...
what happened to the spacings and enters .... not much of an improvement in
my opinion
 
Daniel said:
I have a form with two multi select list boxes. I want to be able to
use the selection made in the list boxes to act as criterion for the
query behind it's sub-form.

I can get it to work for one list box with one selected item.
However, I cannot get it to work when multiple items are selected.
nor can I cancatenate the values of one list box with another.

Here is 1 of the coding that I was trying to use:
********
Private Sub List0_AfterUpdate()
'Returns a listing of the items selected from the list box (query
criteria format) Dim varItem As Variant
Dim strList As String

With Me!List0
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & " OR " & """[Lead Signature] Like
'" & .Column(0, varItem) & "'" Next varItem
End If
End With

strList = Right(strList, Len(strList) - 4) & """"
MsgBox strList

Me.Lead_tool_03_subform.Form.Filter = strlist
Forms![Lead Summary Drawing Information frm]![Lead tool 03
subform].Form.FilterOn = True
End Sub
********

can someone please spell it out for me. I've already spent 1.5 day
on this to get nowhere. Is there a sample database somewhere that I
could down load to greatly inspire myself?!

thanks

Daniel
Click to expand...

It looks to me like you're putting quotes in the filter string that
shouldn't be there. It would be useful to see the actual value of
strList as it is displayed by your MsgBox, but it seems to me you're
going to get something like

"[Lead Signature] Like 'Fred' OR "[Lead Signature] Like 'Bill'"

when what you really want is

[Lead Signature] Like 'Fred' OR [Lead Signature] Like 'Bill'

Fixing that would mean changing the code to:

'---- start of revised version 1 -----
With Me!List0
If .MultiSelect = 0 Then
If Not IsNull(.Value) Then
strList = "[Lead Signature] Like '" & .Value & "'"
End If
Else
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & _
" OR [Lead Signature] Like '" & _
.Column(0, varItem) & "'"
Next varItem
strList = Mid(strList, 5)
End If
End If
End With

MsgBox strList

With Me![Lead tool 03 subform].Form
.Filter = strList
.FilterOn = True
End With
'---- end of revised version 1 -----

Note: the above code is going along with your use of the Like operator
in your comparisons, and that would be correct if your list box entries
contain wild-card characters or other patterns that you need the Like
operator to resolve. However, it may well be that you really want to
test for equality, in which case you can dispense with the Like
operator, and at the same time make your expression simpler and shorter
by using the "In" operator instead:

'---- start of revised version 2 -----
With Me!List0
If .MultiSelect = 0 Then
If Not IsNull(.Value) Then
strList = " = '" & .Value & "'"
End If
Else
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & ", '" & .Column(0, varItem) &
"'"
Next varItem
If .ItemsSelected.Count = 1 Then
strList = " = " & Mid(strList, 3)
Else
strList = " In (" & Mid(strList, 3) & ")"
End If
End If
End If
End With

MsgBox strList

With Me![Lead tool 03 subform].Form
If Len(strList) = 0 Then
.Filter = vbNullString
.FilterOn = False
Else
.Filter = "[Lead Signature]" & strList
.FilterOn = True
End If
End With
'---- end of revised version 2 -----
PS: Don't like the new format of the forum! I find the postings
messy ... what happened to the spacings and enters .... not much of
an improvement in my opinion
Click to expand...

I haven't looked to see if they've changed the format of the web
interface to the newsgroups, but are you aware that you can use Outlook
Express (or any other newsreader program) to read and post to the
newsgroups?
 
I will have to try using outlook instead of the web.

thanks for both tips!

Daniel

Roger Carlson said:
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb" which might help. "Form 6" uses a multiselect listbox
to create the WHERE clause for the query. Also, the sample
"MultiSelect.mdb" gives some background information.

PS: I use Outlook Express as a newsgroup reader and I don't use the web
interface. I'm really happy with how they look.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Daniel said:
I have a form with two multi select list boxes. I want to be able to use
Click to expand...
the selection made in the list boxes to act as criterion for the query
behind it's sub-form.
I can get it to work for one list box with one selected item. However, I
Click to expand...
cannot get it to work when multiple items are selected. nor can I
cancatenate the values of one list box with another.
Here is 1 of the coding that I was trying to use:
********
Private Sub List0_AfterUpdate()
'Returns a listing of the items selected from the list box (query criteria format)
Dim varItem As Variant
Dim strList As String

With Me!List0
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & " OR " & """[Lead Signature] Like '" & ..Column(0, varItem) & "'"
Next varItem
End If
End With

strList = Right(strList, Len(strList) - 4) & """"
MsgBox strList

Me.Lead_tool_03_subform.Form.Filter = strlist
Forms![Lead Summary Drawing Information frm]![Lead tool 03 subform].Form.FilterOn = True
End Sub
********

can someone please spell it out for me. I've already spent 1.5 day on
Click to expand...
this to get nowhere. Is there a sample database somewhere that I could down
load to greatly inspire myself?!
thanks

Daniel
PS: Don't like the new format of the forum! I find the postings messy ...
Click to expand...
what happened to the spacings and enters .... not much of an improvement in
my opinion
Click to expand...
 
a little off the subject... but where can I find an explanation on how to configure outlook to view posting from this user group?

Dan

Dirk Goldgar said:
Daniel said:
I have a form with two multi select list boxes. I want to be able to
use the selection made in the list boxes to act as criterion for the
query behind it's sub-form.

I can get it to work for one list box with one selected item.
However, I cannot get it to work when multiple items are selected.
nor can I cancatenate the values of one list box with another.

Here is 1 of the coding that I was trying to use:
********
Private Sub List0_AfterUpdate()
'Returns a listing of the items selected from the list box (query
criteria format) Dim varItem As Variant
Dim strList As String

With Me!List0
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & " OR " & """[Lead Signature] Like
'" & .Column(0, varItem) & "'" Next varItem
End If
End With

strList = Right(strList, Len(strList) - 4) & """"
MsgBox strList

Me.Lead_tool_03_subform.Form.Filter = strlist
Forms![Lead Summary Drawing Information frm]![Lead tool 03
subform].Form.FilterOn = True
End Sub
********

can someone please spell it out for me. I've already spent 1.5 day
on this to get nowhere. Is there a sample database somewhere that I
could down load to greatly inspire myself?!

thanks

Daniel
Click to expand...

It looks to me like you're putting quotes in the filter string that
shouldn't be there. It would be useful to see the actual value of
strList as it is displayed by your MsgBox, but it seems to me you're
going to get something like

"[Lead Signature] Like 'Fred' OR "[Lead Signature] Like 'Bill'"

when what you really want is

[Lead Signature] Like 'Fred' OR [Lead Signature] Like 'Bill'

Fixing that would mean changing the code to:

'---- start of revised version 1 -----
With Me!List0
If .MultiSelect = 0 Then
If Not IsNull(.Value) Then
strList = "[Lead Signature] Like '" & .Value & "'"
End If
Else
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & _
" OR [Lead Signature] Like '" & _
.Column(0, varItem) & "'"
Next varItem
strList = Mid(strList, 5)
End If
End If
End With

MsgBox strList

With Me![Lead tool 03 subform].Form
.Filter = strList
.FilterOn = True
End With
'---- end of revised version 1 -----

Note: the above code is going along with your use of the Like operator
in your comparisons, and that would be correct if your list box entries
contain wild-card characters or other patterns that you need the Like
operator to resolve. However, it may well be that you really want to
test for equality, in which case you can dispense with the Like
operator, and at the same time make your expression simpler and shorter
by using the "In" operator instead:

'---- start of revised version 2 -----
With Me!List0
If .MultiSelect = 0 Then
If Not IsNull(.Value) Then
strList = " = '" & .Value & "'"
End If
Else
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & ", '" & .Column(0, varItem) &
"'"
Next varItem
If .ItemsSelected.Count = 1 Then
strList = " = " & Mid(strList, 3)
Else
strList = " In (" & Mid(strList, 3) & ")"
End If
End If
End If
End With

MsgBox strList

With Me![Lead tool 03 subform].Form
If Len(strList) = 0 Then
.Filter = vbNullString
.FilterOn = False
Else
.Filter = "[Lead Signature]" & strList
.FilterOn = True
End If
End With
'---- end of revised version 2 -----
PS: Don't like the new format of the forum! I find the postings
messy ... what happened to the spacings and enters .... not much of
an improvement in my opinion
Click to expand...

I haven't looked to see if they've changed the format of the web
interface to the newsgroups, but are you aware that you can use Outlook
Express (or any other newsreader program) to read and post to the
newsgroups?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Dirk,

Your code to retrieve the selected items work great! But I have one more question, in my specific case I have four list box that I need to concatenate there selected items together to create a filter. How can I acheive this?

the end of your code was for 1 list box to act as a unique criteria:
******
With Me![Lead tool 03 subform].Form
If Len(strList) = 0 Then
.Filter = vbNullString
.FilterOn = False
Else
.Filter = "[Lead Signature]" & strList
.FilterOn = True
End If
End With
***********
but in my case I have 4 strlists (strlistLead,StrlistWP,strListAssy and strListID) at any time any combination of them may be selected or not. Thus my filter need to be adaptive. The filter itself should be additive "[Lead Signature]" & strList & " AND [WP]" & strlistWP ... but only if the str is not null. I could create a masive code to acheive this but I'm sure you no a much more efficient way.

thanks once again

Daniel


Dirk Goldgar said:
Daniel said:
I have a form with two multi select list boxes. I want to be able to
use the selection made in the list boxes to act as criterion for the
query behind it's sub-form.

I can get it to work for one list box with one selected item.
However, I cannot get it to work when multiple items are selected.
nor can I cancatenate the values of one list box with another.

Here is 1 of the coding that I was trying to use:
********
Private Sub List0_AfterUpdate()
'Returns a listing of the items selected from the list box (query
criteria format) Dim varItem As Variant
Dim strList As String

With Me!List0
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & " OR " & """[Lead Signature] Like
'" & .Column(0, varItem) & "'" Next varItem
End If
End With

strList = Right(strList, Len(strList) - 4) & """"
MsgBox strList

Me.Lead_tool_03_subform.Form.Filter = strlist
Forms![Lead Summary Drawing Information frm]![Lead tool 03
subform].Form.FilterOn = True
End Sub
********

can someone please spell it out for me. I've already spent 1.5 day
on this to get nowhere. Is there a sample database somewhere that I
could down load to greatly inspire myself?!

thanks

Daniel
Click to expand...

It looks to me like you're putting quotes in the filter string that
shouldn't be there. It would be useful to see the actual value of
strList as it is displayed by your MsgBox, but it seems to me you're
going to get something like

"[Lead Signature] Like 'Fred' OR "[Lead Signature] Like 'Bill'"

when what you really want is

[Lead Signature] Like 'Fred' OR [Lead Signature] Like 'Bill'

Fixing that would mean changing the code to:

'---- start of revised version 1 -----
With Me!List0
If .MultiSelect = 0 Then
If Not IsNull(.Value) Then
strList = "[Lead Signature] Like '" & .Value & "'"
End If
Else
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & _
" OR [Lead Signature] Like '" & _
.Column(0, varItem) & "'"
Next varItem
strList = Mid(strList, 5)
End If
End If
End With

MsgBox strList

With Me![Lead tool 03 subform].Form
.Filter = strList
.FilterOn = True
End With
'---- end of revised version 1 -----

Note: the above code is going along with your use of the Like operator
in your comparisons, and that would be correct if your list box entries
contain wild-card characters or other patterns that you need the Like
operator to resolve. However, it may well be that you really want to
test for equality, in which case you can dispense with the Like
operator, and at the same time make your expression simpler and shorter
by using the "In" operator instead:

'---- start of revised version 2 -----
With Me!List0
If .MultiSelect = 0 Then
If Not IsNull(.Value) Then
strList = " = '" & .Value & "'"
End If
Else
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & ", '" & .Column(0, varItem) &
"'"
Next varItem
If .ItemsSelected.Count = 1 Then
strList = " = " & Mid(strList, 3)
Else
strList = " In (" & Mid(strList, 3) & ")"
End If
End If
End If
End With

MsgBox strList

With Me![Lead tool 03 subform].Form
If Len(strList) = 0 Then
.Filter = vbNullString
.FilterOn = False
Else
.Filter = "[Lead Signature]" & strList
.FilterOn = True
End If
End With
'---- end of revised version 2 -----
PS: Don't like the new format of the forum! I find the postings
messy ... what happened to the spacings and enters .... not much of
an improvement in my opinion
Click to expand...

I haven't looked to see if they've changed the format of the web
interface to the newsgroups, but are you aware that you can use Outlook
Express (or any other newsreader program) to read and post to the
newsgroups?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Daniel said:
Dirk,

Your code to retrieve the selected items work great! But I have one
more question, in my specific case I have four list box that I need
to concatenate there selected items together to create a filter. How
can I acheive this?

the end of your code was for 1 list box to act as a unique criteria:
******
With Me![Lead tool 03 subform].Form
If Len(strList) = 0 Then
.Filter = vbNullString
.FilterOn = False
Else
.Filter = "[Lead Signature]" & strList
.FilterOn = True
End If
End With
***********
but in my case I have 4 strlists (strlistLead,StrlistWP,strListAssy
and strListID) at any time any combination of them may be selected or
not. Thus my filter need to be adaptive. The filter itself should
be additive "[Lead Signature]" & strList & " AND [WP]" & strlistWP
... but only if the str is not null. I could create a masive code to
acheive this but I'm sure you no a much more efficient way.

thanks once again

Daniel
Click to expand...

Here's a quickie function (not fully tested) that you can paste into a
standard module and call from your form's code:

'----- start of module code -----
Function fncListboxCriteriaString( _
lst As Access.ListBox, _
FieldName As String, _
Optional FieldType As Integer = 4) _
As String

' Returns a SQL criteria string filtering the
' field named <FieldName> by the values selected
' in list box <lst>. If the type of field to be
' filtered field is not numeric, specify the
' optional <FieldType> argument as, for example,
' dbText or dbDate.

' Notes:
' 1. This function uses the bound column of the list
' box as the source of the values to be filtered by.
' 2. If the field name requires brackets around it to
' be interpreted properly, the caller must supply them.


Dim strCriteria As String
Dim strValue As String
Dim varItem As Variant

If lst.ItemsSelected.Count > 0 Then

For Each varItem In lst.ItemsSelected

Select Case FieldType
Case dbText, dbMemo
strValue = _
Chr(34) & _
Replace(lst.ItemData(varItem), _
Chr(34), """""", _
, , vbBinaryCompare) & _
Chr(34)
Case dbDate
strValue = _
Format(lst.ItemData(varItem), _
"\#mm/dd/yyyy\#")
Case Else
strValue = CStr(lst.ItemData(varItem))
End Select

strCriteria = strCriteria & ", " & strValue

Next varItem

If lst.ItemsSelected.Count = 1 Then
strCriteria = _
FieldName & " = " & Mid(strCriteria, 3)
Else
strCriteria = _
FieldName & " In (" & Mid(strCriteria, 3) & ")"
End If

End If

fncListboxCriteriaString = strCriteria

End Function
'----- end of module code -----

Now your code can call this function for each list box on the form, like
this:

'----- start of code behind form (snippet) -----
Dim strlistLead As String
Dim strlistWP As String
Dim strlistAssy As String
Dim strlistID As String
Dim strFilterString As String

strListLead = _
fncListboxCriteriaString(Me!lstLead, "[Lead Signature]", dbText)

strlistWP = _
fncListboxCriteriaString(Me!lstWP, "WP", dbText)

strlistAssy = _
fncListboxCriteriaString(Me!strlistAssy, "Assy", dbText)

strlistID = _
fncListboxCriteriaString(Me!strlistID, "ID")

'*** Note: I've assumed that ID is a numeric field.

' Combine criteria into a single filter string.
If Len(strlistLead) > 0 Then
strFilterString = " AND " & strlistLead
End If
If Len(strlistWP) > 0 Then
strFilterString = " AND " & strlistWP
End If
If Len(strlistAssy) > 0 Then
strFilterString = " AND " & strlistAssy
End If
If Len(strlistID) > 0 Then
strFilterString = " AND " & strlistID
End If

' Drop leading " AND " from filter string, if present.
If Len(strFilterString) > 0 Then
strFilterString = Mid$(strFilterString, 6)
End If

' Apply or remove the filter from the subform,
' as needed.
With Me![Lead tool 03 subform].Form
.Filter = strFilterString
.FilterOn = (Len(strFilterString) > 0))
End With

'----- end of code behind form (snippet) -----
 
Back
Top