Two list boxes not working

[george 16-17]

Greetings,

Again, I am very new to Access and VBA. I posted a question yesterday
regarding one list box and received some great assistance
(http://www.microsoft.com/communitie...&p=1&tid=4fe552e0-d7c1-42fa-a629-8a80aebf2425).
This code is based on Allen Browne's work as referenced in the link above.

I want to add a second list box - one to filter location and one to filter
cost center. The cost center one works fine, but the location one is not
working. I keep receiving a "runtime error '2448' and the debugger points to
line Me.Filter = strWhere. Both field's (location and cost center) data type
are set to "text" in the table.

Here is my code:
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code Location****************************************
Dim varItem As Variant 'Selected items
Dim strDescrip As String 'Description of WhereCondition
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
strDelim = """" 'Delimiter appropriate to field type.

With Me.lstLocation
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 = "[Location] IN (" & Left$(strWhere, lngLen) & ") AND "
lngLen = Len(strDescrip) - 2
End If

'List box Cost Ctr******************************************
With Me.lstCostCtr
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.
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 = "[Cost Ctr] IN (" & Left$(strWhere, lngLen) & ") AND "
lngLen = Len(strDescrip) - 2
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Thanks in advance and any assistance would be appreciated,
george

 

[Daniel Pineault]

So it crashes at the very end, at

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Then perform a debug.print of the strWhere and post back the return output
from the immediate window so we can take a look at it.

Debug.Print strWhere 'Add this line
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

Greetings,

Again, I am very new to Access and VBA. I posted a question yesterday
regarding one list box and received some great assistance
(http://www.microsoft.com/communitie...&p=1&tid=4fe552e0-d7c1-42fa-a629-8a80aebf2425).
This code is based on Allen Browne's work as referenced in the link above.

I want to add a second list box - one to filter location and one to filter
cost center. The cost center one works fine, but the location one is not
working. I keep receiving a "runtime error '2448' and the debugger points to
line Me.Filter = strWhere. Both field's (location and cost center) data type
are set to "text" in the table.

Here is my code:
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code Location****************************************
Dim varItem As Variant 'Selected items
Dim strDescrip As String 'Description of WhereCondition
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
strDelim = """" 'Delimiter appropriate to field type.

With Me.lstLocation
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 = "[Location] IN (" & Left$(strWhere, lngLen) & ") AND "
lngLen = Len(strDescrip) - 2
End If

'List box Cost Ctr******************************************
With Me.lstCostCtr
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.
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 = "[Cost Ctr] IN (" & Left$(strWhere, lngLen) & ") AND "
lngLen = Len(strDescrip) - 2
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Thanks in advance and any assistance would be appreciated,
george

 

[george 16-17]

Hi Daniel,

Thanks for taking a look. Sorry I have not gotten back sooner.

I performed your suggestion recommended and the immediate window revealed:

[Cost Center] IN [Location] in ("FLPK") AND)
[Cost Center] IN [Location] in ("FLPK") AND)

It is the Location list box that is not working and "FLPK" is one of the
locations I tried to run.
I'm have no idea what this means being so new to VBA. Again, I appreciate
your help.

george

So it crashes at the very end, at

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Then perform a debug.print of the strWhere and post back the return output
from the immediate window so we can take a look at it.

Debug.Print strWhere 'Add this line
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.

Greetings,

Again, I am very new to Access and VBA. I posted a question yesterday
regarding one list box and received some great assistance
(http://www.microsoft.com/communitie...&p=1&tid=4fe552e0-d7c1-42fa-a629-8a80aebf2425).
This code is based on Allen Browne's work as referenced in the link above.

I want to add a second list box - one to filter location and one to filter
cost center. The cost center one works fine, but the location one is not
working. I keep receiving a "runtime error '2448' and the debugger points to
line Me.Filter = strWhere. Both field's (location and cost center) data type
are set to "text" in the table.

Here is my code:
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code Location****************************************
Dim varItem As Variant 'Selected items
Dim strDescrip As String 'Description of WhereCondition
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
strDelim = """" 'Delimiter appropriate to field type.

With Me.lstLocation
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 = "[Location] IN (" & Left$(strWhere, lngLen) & ") AND "
lngLen = Len(strDescrip) - 2
End If

'List box Cost Ctr******************************************
With Me.lstCostCtr
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.
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 = "[Cost Ctr] IN (" & Left$(strWhere, lngLen) & ") AND "
lngLen = Len(strDescrip) - 2
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Thanks in advance and any assistance would be appreciated,
george