Need multiselect listbox for subreport to work

[traycee]

Hi!

I've been working on this problem for the last week and while I can get close...it's never close enough.

I have a report with a subreport. The report is rptMailingDateAndSum based on the query qryMailingDateAndSum. It's subreport is rsubMailingDateAndSum based on the query qselMailingDateAndSum. The two reports are tied together by the DonorID field they have in common. (Works fine if I eliminate the two listboxes for the subreport.

I have a bunch of multiselect list boxes setup and they work fine for those based on the main report. The two list boxes that need to filter the subreport however do not work. The two that don't work are lboTFlag and lboNAppealNO. The following is where I'm sure the problem is. This code is on the onclick event of the command button on the form that runs the report and contains the list boxes.

Private Sub cmdOK_Click()
Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTDonorName)
strWhere = strWhere & BuildIn(Me.lboTBusinessName)
strWhere = strWhere & BuildIn(Me.lboTFlag)
strWhere = strWhere & BuildIn(Me.lboTZip)
strWhere = strWhere & BuildIn(Me.lboTCounty)
strWhere = strWhere & BuildIn(Me.lboTCity)
strWhere = strWhere & BuildIn(Me.lboTRecordType)
strWhere = strWhere & BuildIn(Me.lboNAppealNo)
Debug.Print strWhere
CurrentDb.QueryDefs("qselMailingDateAndSum").SQL = "SELECT tblDonor.[DonorID#], [tblAppeal.AppealName] AS AppealNo, [tlkpAppealNames.AppealName] AS AppealName, tlkpFlag.Flag, tblAppeal.DateRcvd FROM (tblDonor INNER JOIN (tblAppeal LEFT JOIN tlkpAppealNames ON tblAppeal.AppealName = tlkpAppealNames.AppealNameID) ON tblDonor.[DonorID#] = tblAppeal.[DonorID#]) LEFT JOIN (tblflag LEFT JOIN tlkpFlag ON tblflag.FlagItem = tlkpFlag.FlagID) ON tblDonor.[DonorID#] = tblflag.[DonorID#]WHERE (((tblAppeal.DateRcvd) Between [Forms]![fdlgMailingDateAndSum]![txtBeginDate] And [Forms]![fdlgMailingDateAndSum]![txtEndDate]));"
DoCmd.OpenReport "rptMailingDateAndSum", acViewPreview, , strWhere
Me.Visible = False
End Sub

I'm sure I've done something stupid on the Currentdb.querydefs statement. I'm not really very good with that.

Thanks in advance for any help.

 

[Remou]

Are you getting any errors on setting the SQL for the querydef? What does strWhere look like?

 

[traycee]

Maybe my logic is wrong. Is it possible to filter a main report based on a value from a subreport? User enters date range, currency range, chooses a flag(s) from the listbox but the flag is a field on the subreport.

SQL seems fine, I'm just not sure I'm pointing to the subreport properly. Report runs but when I select an item from a list box based on the subreport and click OK I get an 'enter parameter value' popup box. I can then manually type in the flag value, but of course the report doesn't filter...it just shows all the data...correctly but not filtered.