list box filters report

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

Guest

I have a listbox named programlstbx which the user selects a couple programs
to make labels for mailing purposes. I have it coded as follows

Private Sub LabelsLink_Click()


On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "rptLabels ucp_consumers"

'Loop through the ItemsSelected in the list box.
With Me.lstbxProgram
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter
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 = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub



which i got from 'Purpose: Open the report filtered to the items
selected in the list box. 'Author: Allen J Browne, 2004.
http://allenbrowne.com






In this section of code If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

I also have another field i want it to look in altprogram1. Is there a way
to put like an or operator in or something to that extent that when a user
selects a value from the listbox that it will check the defaultprogram field
and also the altprogram1 field?

Any help would be appericated.

Thanks

Jeremy
 
stag246 said:
I have a listbox named programlstbx which the user selects a couple programs
to make labels for mailing purposes. I have it coded as follows

Private Sub LabelsLink_Click()


On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "rptLabels ucp_consumers"

'Loop through the ItemsSelected in the list box.
With Me.lstbxProgram
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter
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 = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub



which i got from 'Purpose: Open the report filtered to the items
selected in the list box. 'Author: Allen J Browne, 2004.
http://allenbrowne.com






In this section of code If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

I also have another field i want it to look in altprogram1. Is there a way
to put like an or operator in or something to that extent that when a user
selects a value from the listbox that it will check the defaultprogram field
and also the altprogram1 field?

Any help would be appericated.

Thanks

Jeremy
Click to expand...

If I understand right, you want all records where [defaultProgram] OR
[altprogram1] match the items selected in the list box.

Try changingthe code to: (watch for line wrap / untested line added)

'------- SNIP --------
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left(strWhere, lngLen) & ")"
'*** FOLLOWING LINE ADDED to check another field
strWhere = strWhere & " Or [altprogram1] IN (" & Left(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left(strDescrip, lngLen)
End If
End If
'-------Snip----

HTH

Steve
 
That worked. Thanks alot.

Jeremy

SteveS said:
stag246 said:
I have a listbox named programlstbx which the user selects a couple programs
to make labels for mailing purposes. I have it coded as follows

Private Sub LabelsLink_Click()


On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "rptLabels ucp_consumers"

'Loop through the ItemsSelected in the list box.
With Me.lstbxProgram
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter
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 = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub



which i got from 'Purpose: Open the report filtered to the items
selected in the list box. 'Author: Allen J Browne, 2004.
http://allenbrowne.com






In this section of code If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

I also have another field i want it to look in altprogram1. Is there a way
to put like an or operator in or something to that extent that when a user
selects a value from the listbox that it will check the defaultprogram field
and also the altprogram1 field?

Any help would be appericated.

Thanks

Jeremy
Click to expand...

If I understand right, you want all records where [defaultProgram] OR
[altprogram1] match the items selected in the list box.

Try changingthe code to: (watch for line wrap / untested line added)

'------- SNIP --------
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left(strWhere, lngLen) & ")"
'*** FOLLOWING LINE ADDED to check another field
strWhere = strWhere & " Or [altprogram1] IN (" & Left(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left(strDescrip, lngLen)
End If
End If
'-------Snip----

HTH

Steve
Click to expand...
 
You're very welcome. Glad I could help.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


stag246 said:
That worked. Thanks alot.

Jeremy

SteveS said:
stag246 said:
I have a listbox named programlstbx which the user selects a couple programs
to make labels for mailing purposes. I have it coded as follows

Private Sub LabelsLink_Click()


On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "rptLabels ucp_consumers"

'Loop through the ItemsSelected in the list box.
With Me.lstbxProgram
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter
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 = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub



which i got from 'Purpose: Open the report filtered to the items
selected in the list box. 'Author: Allen J Browne, 2004.
http://allenbrowne.com






In this section of code If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left$(strDescrip, lngLen)
End If
End If

I also have another field i want it to look in altprogram1. Is there a way
to put like an or operator in or something to that extent that when a user
selects a value from the listbox that it will check the defaultprogram field
and also the altprogram1 field?

Any help would be appericated.

Thanks

Jeremy
Click to expand...

If I understand right, you want all records where [defaultProgram] OR
[altprogram1] match the items selected in the list box.

Try changingthe code to: (watch for line wrap / untested line added)

'------- SNIP --------
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[defaultProgram] IN (" & Left(strWhere, lngLen) & ")"
'*** FOLLOWING LINE ADDED to check another field
strWhere = strWhere & " Or [altprogram1] IN (" & Left(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Program: " & Left(strDescrip, lngLen)
End If
End If
'-------Snip----

HTH

Steve
Click to expand...
Click to expand...
 
Back
Top