multiple selection in a listbox

[Guest]

I have a listbox and would like to select multiple items to preview, I have
set the multiple select to simple but when I do preview, it brings all the
records including those I did not select,,,,, What do I need to add/do so
that am able to preview only what I selected

 

[Allen Browne]

See:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

 

[Guest]

I have read some of your responses in regards to multi-selct list boxes and
have tried your multi-select code for a form I have. It only works for one
field on my form. I have several "AutoID" fields...

I have been trying to get each filter to work independantly with the intent
of making them work together later.

This works:
'Model Filter
With Me.Combo_Mod
For Each varMod In .ItemsSelected
'Build filter using AutoID
strMod = strMod & .ItemData(varMod) & ","
Next
End With

'Remove trailing comma, add field name, IN operator, and brackets
l_Mod = Len(strMod) - 1
If l_Mod > 0 Then
strMod = "[AutoID] IN (" & Left$(strMod, l_Mod) & ")"
End If

This doesn't:
'Vendor Filter
With Me.Combo_vend
For Each varVend In .ItemsSelected
strVend = strVend & .ItemData(varVend) & ","
Next
End With

l_Vend = Len(strVend) - 1
If l_Vend > 0 Then
strVend = "[AutoID] IN (" & Left$(strVend, l_Vend) & ")"
End If

What syntax do I use when I have multiple fields with the same name that
come from two different tables?

 

[Douglas J. Steele]

The list of fields in a recordset cannot have the same field name twice: one
of the occurrences is going to be renamed, usually to something like Expr1.
That means that you should not be running into problems with multiple fields
with the same name coming from two different tables.

Are you sure that both list boxes are set up the same way: that the first
column of the list box contains the numeric Id field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have read some of your responses in regards to multi-selct list boxes and
have tried your multi-select code for a form I have. It only works for
one
field on my form. I have several "AutoID" fields...

I have been trying to get each filter to work independantly with the
intent
of making them work together later.

This works:
'Model Filter
With Me.Combo_Mod
For Each varMod In .ItemsSelected
'Build filter using AutoID
strMod = strMod & .ItemData(varMod) & ","
Next
End With

'Remove trailing comma, add field name, IN operator, and brackets
l_Mod = Len(strMod) - 1
If l_Mod > 0 Then
strMod = "[AutoID] IN (" & Left$(strMod, l_Mod) & ")"
End If

This doesn't:
'Vendor Filter
With Me.Combo_vend
For Each varVend In .ItemsSelected
strVend = strVend & .ItemData(varVend) & ","
Next
End With

l_Vend = Len(strVend) - 1
If l_Vend > 0 Then
strVend = "[AutoID] IN (" & Left$(strVend, l_Vend) & ")"
End If

What syntax do I use when I have multiple fields with the same name that
come from two different tables?

--
-CLSWL

See:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

 

[Guest]

I solved that problem by doing this:
strMod = "[Model].[AutoID] IN (" & Left$(strMod, l_Mod) & ")"

Now I have a new challenge. I have been able to link criteria fields
together but I get no results if on of the feild is empty. What value should
I default the string to when there is no limiting criteria.

DoCmd.OpenReport "repAssyNo", acViewPreview, , strMod & "AND" & strDrawNo,
acWindowNormal


-CLSWL

 

[Douglas J. Steele]

You can use something like:


Dim strCondition As String

If Len(strMod) > 0 Then
strCondition = strCondition & strMod & " And "
End If

If Len(strDrawNo) > 0 Then
strCondition = strCondition & strDrawNo & " And "
End If

If Len(strCondition) > 0 Then
' Remove the extra " And " from the end...
strCondition = Left$(strCondition, Len(strCondition) - 5)
End If

DoCmd.OpenReport "repAssyNo", acViewPreview, , strCondition,
acWindowNormal