Listbox coding problem

  • Thread starter Thread starter george 16-17
  • Start date Start date
G

george 16-17

Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
 
You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
 
Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

Klatuu said:
You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
 
Your code is not putting an AND between the criteria for each field.
Also, you are putting quotes around all your items. If any of the fields
are numeric, you should not include the quotes.

To see what strWhere looks like before you try to make it the filter, but a
breakpoint on the line:
Me.Filter = strWhere

Then run the app. When it gets to that line it will stop and the VB Editor
will come up with that line highlighted. You can then type:

?strWhere

in the immediate window and you will see its content and verify it is correct.
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

Klatuu said:
You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dave,

I appreciate the prompt responses.

You are correct...I forgot the "AND" on the listbox code. It is working now.

Thanks so much!!!!

george

Klatuu said:
Your code is not putting an AND between the criteria for each field.
Also, you are putting quotes around all your items. If any of the fields
are numeric, you should not include the quotes.

To see what strWhere looks like before you try to make it the filter, but a
breakpoint on the line:
Me.Filter = strWhere

Then run the app. When it gets to that line it will stop and the VB Editor
will come up with that line highlighted. You can then type:

?strWhere

in the immediate window and you will see its content and verify it is correct.
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

Klatuu said:
You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


:

Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Good. FYI, here is a function I use in these situations. An example follows.
It is not using it in a list box, but it does work the same way

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:

On Error GoTo 0
Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

****************

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Dave,

I appreciate the prompt responses.

You are correct...I forgot the "AND" on the listbox code. It is working now.

Thanks so much!!!!

george

Klatuu said:
Your code is not putting an AND between the criteria for each field.
Also, you are putting quotes around all your items. If any of the fields
are numeric, you should not include the quotes.

To see what strWhere looks like before you try to make it the filter, but a
breakpoint on the line:
Me.Filter = strWhere

Then run the app. When it gets to that line it will stop and the VB Editor
will come up with that line highlighted. You can then type:

?strWhere

in the immediate window and you will see its content and verify it is correct.
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

:

You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


:

Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks again. That does look like a better method. I will add that in as my
search code expands.


Klatuu said:
Good. FYI, here is a function I use in these situations. An example follows.
It is not using it in a list box, but it does work the same way

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:

On Error GoTo 0
Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

****************

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Dave,

I appreciate the prompt responses.

You are correct...I forgot the "AND" on the listbox code. It is working now.

Thanks so much!!!!

george

Klatuu said:
Your code is not putting an AND between the criteria for each field.
Also, you are putting quotes around all your items. If any of the fields
are numeric, you should not include the quotes.

To see what strWhere looks like before you try to make it the filter, but a
breakpoint on the line:
Me.Filter = strWhere

Then run the app. When it gets to that line it will stop and the VB Editor
will come up with that line highlighted. You can then type:

?strWhere

in the immediate window and you will see its content and verify it is correct.
--
Dave Hargis, Microsoft Access MVP


:

Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

:

You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


:

Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help.
I do things like that because I am lazy. If I finding myself doing the same
thing a second time, it becomes a sub or function.

We lazy people are the most inventive because we are always looking for ways
to do less work <g>
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Thanks again. That does look like a better method. I will add that in as my
search code expands.


Klatuu said:
Good. FYI, here is a function I use in these situations. An example follows.
It is not using it in a list box, but it does work the same way

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:

On Error GoTo 0
Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

****************

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Dave,

I appreciate the prompt responses.

You are correct...I forgot the "AND" on the listbox code. It is working now.

Thanks so much!!!!

george

:

Your code is not putting an AND between the criteria for each field.
Also, you are putting quotes around all your items. If any of the fields
are numeric, you should not include the quotes.

To see what strWhere looks like before you try to make it the filter, but a
breakpoint on the line:
Me.Filter = strWhere

Then run the app. When it gets to that line it will stop and the VB Editor
will come up with that line highlighted. You can then type:

?strWhere

in the immediate window and you will see its content and verify it is correct.
--
Dave Hargis, Microsoft Access MVP


:

Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

:

You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


:

Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Agreed!

I switched from Excel to Access to manage the vast spreadsheets I deal with.
Even though I am struggling with the Access learning curve, I already realize
the benefit. I like working "smarter not harder" because I am lazy also!

Again, your assistance was deeply appreciated.

george

Klatuu said:
Glad I could help.
I do things like that because I am lazy. If I finding myself doing the same
thing a second time, it becomes a sub or function.

We lazy people are the most inventive because we are always looking for ways
to do less work <g>
--
Dave Hargis, Microsoft Access MVP


george 16-17 said:
Thanks again. That does look like a better method. I will add that in as my
search code expands.


Klatuu said:
Good. FYI, here is a function I use in these situations. An example follows.
It is not using it in a list box, but it does work the same way

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:

On Error GoTo 0
Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

****************

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

--
Dave Hargis, Microsoft Access MVP


:

Dave,

I appreciate the prompt responses.

You are correct...I forgot the "AND" on the listbox code. It is working now.

Thanks so much!!!!

george

:

Your code is not putting an AND between the criteria for each field.
Also, you are putting quotes around all your items. If any of the fields
are numeric, you should not include the quotes.

To see what strWhere looks like before you try to make it the filter, but a
breakpoint on the line:
Me.Filter = strWhere

Then run the app. When it gets to that line it will stop and the VB Editor
will come up with that line highlighted. You can then type:

?strWhere

in the immediate window and you will see its content and verify it is correct.
--
Dave Hargis, Microsoft Access MVP


:

Opps...sorry.

Me.Filter = strWhere.

Runtime error '2448'

Thanks,
george

:

You don't say on which line the error occurs or what error you are getting.
Without that, it is almost impossible to hazard a guess.
--
Dave Hargis, Microsoft Access MVP


:

Greetings,

I am very new to Access and VBA. I am trying to add a multiselect list box
(http://allenbrowne.com/ser-50.html) and code to a search form
(http://allenbrowne.com/ser-62.html) that I modified based on Allen Browne's
examples, but I can not seen to get the code right, as I keep on getting a
runtime error. The list box (lstLocation) filters the “Location†field in the
report section.
Any help would be appreciated.

Here is my code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'List box code**********************************************
Dim varItem As Variant
Dim strDescrip As String
Dim strDelim As String

strDelim = """"

'Loop through the ItemsSelected in the list box.
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) & ")"
lngLen = Len(strDescrip) - 2
End If

If Not IsNull(Me.txtCostCenter) Then
strWhere = strWhere & "([Cost Ctr] = """ & Me.txtCostCenter & """)
AND "
End If

If Not IsNull(Me.txtDeptName) Then
strWhere = strWhere & "([Dept Name] Like ""*" & Me.txtDeptName &
"*"") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtEmpName) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtEmpName & "*"") AND "
End If

If Not IsNull(Me.txtInjuryCode) Then
strWhere = strWhere & "([OMS Inj Code] Like """ & Me.txtInjuryCode &
"*"") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date of Inj] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date of Inj] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else .
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

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

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top