list box filters report

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
 
G

Guest

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
 
G

Guest

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Select List Box 4
using dates in a multiple select list box 3
help with list 2
Two Multi-Select List Boxes as Filters 11
Error 13 - Type Mismatch 5
list box selection as filter 1
ADP list box filter 3
Two multi-select list boxes 3

Top