TRYING Allen B'S multi-select list box

[Guest]

I have used Allen's list box example before and it worked perfectly... now

I have the following as the row source for my list box. Id is a number TID
is text

SELECT TerritoryNumbers.ID, TerritoryNumbers.TID, tblArea.AreaName,
TerritoryNumbers.DateCheckedIn
FROM TerritoryNumbers INNER JOIN tblArea ON TerritoryNumbers.Area =
tblArea.AreaID
ORDER BY TerritoryNumbers.DateCheckedIn;

I cannot get the following code to work on the preview button and I don't
know why
I get a an enter perameter box [TerritoryNumbers].[ID] everything else in
the code is original

'strDelim = """" 'If your field is a TEXT type, remove the single-quote
on this line
strDoc = "rptViewReports"

'Loop through the ItemsSelected in the list box.
With Me.lstTerritories
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 = "[TerritoryNumbers].[ID] IN (" & Left$(strWhere, lngLen)
& ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "TerritoryNumbers: " & Left$(strDescrip, lngLen)
End If
End If

Can someone tell me what is wrong?
Thanks!

 

[Allen Browne]

To help you debug it, add this line to the end of your procedure:
Debug.Print strWhere
When it fails, open the Immediate Window (Ctrl+G), and see what's printed
there. Does it look right?

Depending on where/how you are using this output, including the table name
may be an issue. Try omitting the:
[TerritoryNumbers]
from the statement. If there are multiple ID fields in the querie's tables,
you could alias it, e.g.:
SELECT TerritoryNumbers.ID AS TerritoryID, ...
so you can refer to it as TerritoryID.

 

[Guest]

Thanks... by what you said I was able to figure out the problem. It was 2
problems the ID thing was one (Changed ID to TerID) and the other was that
the TerID wasn't in the qry the report was based on. Working now!

Thanks again.
lmv

To help you debug it, add this line to the end of your procedure:
Debug.Print strWhere
When it fails, open the Immediate Window (Ctrl+G), and see what's printed
there. Does it look right?

Depending on where/how you are using this output, including the table name
may be an issue. Try omitting the:
[TerritoryNumbers]
from the statement. If there are multiple ID fields in the querie's tables,
you could alias it, e.g.:
SELECT TerritoryNumbers.ID AS TerritoryID, ...
so you can refer to it as TerritoryID.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used Allen's list box example before and it worked perfectly... now

I have the following as the row source for my list box. Id is a number TID
is text

SELECT TerritoryNumbers.ID, TerritoryNumbers.TID, tblArea.AreaName,
TerritoryNumbers.DateCheckedIn
FROM TerritoryNumbers INNER JOIN tblArea ON TerritoryNumbers.Area =
tblArea.AreaID
ORDER BY TerritoryNumbers.DateCheckedIn;

I cannot get the following code to work on the preview button and I don't
know why
I get a an enter perameter box [TerritoryNumbers].[ID] everything else in
the code is original

'strDelim = """" 'If your field is a TEXT type, remove the single-quote
on this line
strDoc = "rptViewReports"

'Loop through the ItemsSelected in the list box.
With Me.lstTerritories
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 = "[TerritoryNumbers].[ID] IN (" & Left$(strWhere, lngLen)
& ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "TerritoryNumbers: " & Left$(strDescrip, lngLen)
End If
End If

Can someone tell me what is wrong?
Thanks!