Question on Allen Browne's multiple select

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

Guest

I found very useful information about multiple select in the following website
http://allenbrowne.com/ser-50.html

it works ok but my question is my listbox source comes a query that contains
union select query, as follows,
SELECT employee!EmplLastName & ", " & employee!EmplFirstName AS Name,
employee.EmplID
FROM Team INNER JOIN employee ON Team.TeamID = employee.TeamID
ORDER BY employee!EmplLastName & ", " & employee!EmplFirstName,
employee.EmplLastName
UNION select '[*** All Employees ***]', -1 from Team;

How do i open my report when there is a -1 as the employee ID
 
If the row with -1 as the employee ID has been selected, you don't really
want to have the Employee field show up in the Where clause (since you want
to select all of them), which implies that you shouldn't have to loop
through the ItemsSelected collection.

Unfortunately, there's no way to set up a list box so that if a particular
value has been selected, none of the other values may also be selected. That
means you still need to loop through the collection, but if you find out
that you've selected that particular row, stop processing.

Something like the following untested aircode:
 
Sorry about that: the untested air-code didn't make it in the previous post,
did it?

Change

With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

to

Dim booContinue As Boolean

booContinue = True
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
If .Column(1, varItem) = -1 Then
'Stop processing
booContinue = False
Exit For
Else
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
End If
Next
End With

If booContinue Then
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
End If
 
Thank you so much that worked perfectly well

Douglas J. Steele said:
Sorry about that: the untested air-code didn't make it in the previous post,
did it?

Change

With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

to

Dim booContinue As Boolean

booContinue = True
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
If .Column(1, varItem) = -1 Then
'Stop processing
booContinue = False
Exit For
Else
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
End If
Next
End With

If booContinue Then
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JOM said:
I found very useful information about multiple select in the following
website
http://allenbrowne.com/ser-50.html

it works ok but my question is my listbox source comes a query that
contains
union select query, as follows,
SELECT employee!EmplLastName & ", " & employee!EmplFirstName AS Name,
employee.EmplID
FROM Team INNER JOIN employee ON Team.TeamID = employee.TeamID
ORDER BY employee!EmplLastName & ", " & employee!EmplFirstName,
employee.EmplLastName
UNION select '[*** All Employees ***]', -1 from Team;

How do i open my report when there is a -1 as the employee ID
Click to expand...
Click to expand...
 
Back
Top