Problem with Reporting Form

[Guest]

Hello:

I am using a form with a series of list boxes in an option group that allows
users to select criteria for their reports. Here is a snippet of the code I
am using to pull up the report.

Sub PrintMasterReports(acViewPreview As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the Pending Reports option group.
' Then close the Print Sales Reports Dialog form.

Dim strTradePartner As String
strTradePartner = "Vendors.VendorID = Forms![frmReports]!lstVendors"

Dim strBuilders As String
strBuilders = "Builders_BuilderID = Forms![frmReports]!lstBuilders"

Dim strIssues As String
strIssues = "Issues_IssueID = Forms![frmReports]!lstIssues"

Dim strCommunity As String
strCommunity = "Subdivisions_SubdivisionID =
Forms![frmReports]!lstCommunity"


Select Case Me!optVendorReports
Case 1
If IsNull(Forms![frmReports]!lstVendors) Then
DoCmd.OpenReport "rptVendorReport", acViewPreview
Else
DoCmd.OpenReport "rptVendorReport", acViewPreview, ,
strTradePartner
End If

If the list box in question does not have any values selected then the
report should return all of the records in the query. If it does, then it
should return just the records that meet the criteria that is selected.

My problen is that the report is populating with all of the records, even
when I select one or 2 values from the list box.

Thanks for any ideas

Brennan

 

[Nikos Yannacopoulos]

Brennan,

"when I select one or 2 values from the list box: implies this is a
multi-select listbox, right? Well, a multi-select listbox can only be
read by looping through the ItemsSelected; Forms![frmReports]!lstVendors
will always return null, no matter how many selections are made - which
explains the behaviour you are getting. You need to change the
strTradePartner building snippet to:


With Forms![frmReports]!lstVendors
If .ItemsSelected.Count <> 0 Then
strTradePartner = ""
For Each itm in .ItemsSelected
strTradePartner = strTradePartner & "Vendors.VendorID = " & _
.ItemData(itm) & " OR "
Next
strTradePartner = Left(strTradePartner, Len(strTradePartner) - 4)
End If


Also, in your Case structure, you need to change the IF statement from:

If IsNull(Forms![frmReports]!lstVendors) Then

To:

If Forms![frmReports]!lstVendorsItemsSelected.Count = 0 Then


HTH,
Nikos