Filter subreport using "IN"

[bigalpha]

I'm using the following code to filter a report by using a multi-select box.

Private Sub cmdOpenReport_Click()
  On Error GoTo Err_cmdOpenReport_Click

  Dim strWhere      As String
  Dim ctl           As Control
  Dim varItem       As Variant

  'make sure a selection has been made
  If Me.ManifestList.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 manifest"
    Exit Sub
  End If

  'add selected values to string
  Set ctl = Me.ManifestList
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the report, restricted to the selected items
  DoCmd.OpenReport "rptManifestLetterToState", acPreview, , "ManifestDataIDPK IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
  Exit Sub

Err_cmdOpenReport_Click:
  MsgBox Err.Description
  Resume Exit_cmdOpenReport_Click

End Sub

I have a report that contains a subreport. The report is simply an image of a word document. The subreport will contain the actual data.

When I open the subreport, the IN query works perfectly. When I open the main report, I only get the first record listed in the IN query. I have the master/child links set properly.

I've searched and fiddled and can't figure this out.

 

[jdraw]

Put a
Debug.Print strWhere

right after

strWhere = Left(strWhere, Len(strWhere) - 1)

to see what is being rendered.

 

[pbaldy]

Sounds like the string is fine, but it won't on the subreport like that. I might pass the string in OpenArgs and filter the subreport in the report's open event with it.

 

[bigalpha]

Sounds like the string is fine, but it won't on the subreport like that. I might pass the string in OpenArgs and filter the subreport in the report's open event with it.

Yeah, the string filters the subreport properly when I open only the subreport. Do you have a good starting place for learning about OpenArgs? So far, I haven't learned to code yet.

 

[pbaldy]

This is more complicated than you need, since you'll only have one value, but perhaps it gets you started:

http://www.baldyweb.com/OpenArgs.htm

In your case, I'd pass strWhere and use it to set the filter property of the subreport. This is untested, but worth a shot.

 

[pbaldy]

Another thought just came to mind. You could make strWhere a public variable, and use it in the open event of the subreport to set it's own filter. A little like this:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[bigalpha]

Thanks, Paul. I'm going to scope these out and see if I can get something implemented.

 

[bigalpha]

Okay, so I'm doing something wrong.

I changed

DoCmd.OpenReport "rptManifestLetterToState", acPreview, , "ManifestDataIDPK IN(" & strWhere & ")"

to

[SIZE=3]
DoCmd.OpenReport "rptmanifestlettertostate", acPreview, , "ManifestDataIDPK IN(" & strWhere & ")", , "ManifestDataIDPK IN(" & strWhere & ")"

for the button that open the report.

I then added this in the Report_Load for the subreport inside the main report:
[/SIZE]

Private Sub Report_Load()
Dim strOpenArgs() As String

strOpenArgs = Me.OpenArgs
Me.Filter = strOpenArgs
Me.Filter = True

End Sub

The sub report still shows only one record, so I'm sure that I've messed up syntax somewhere.

 

[pbaldy]

The OpenArgs belong to the main report, not the subreport. The more I think about it the more I like the second method anyway. The subreport renders first, so the main report's open event may be too late to apply a filter (that's just speculation though). Also, make sure you don't have master/child links on the subreport container.

 

[bigalpha]

The OpenArgs belong to the main report, not the subreport. The more I think about it the more I like the second method anyway. The subreport renders first, so the main report's open event may be too late to apply a filter (that's just speculation though). Also, make sure you don't have master/child links on the subreport container.

Ah, I didn't know the subreport rendered first. I'll move the OpenArgs to the main report and give it a shot. If it doesn't work, then I'll have to do some searching on how to set strWhere as a public variable.

 

[pbaldy]

It might work, I'm just not sure. If you go the other way:

Public strWhere As String

In a standard module.

 

[bigalpha]

Thanks for your patience! I think that using it as a public variable is probably easier than trying to set up OpenArgs. Guess I'm off to do more research!

 

[pbaldy]

No research needed. You're already creating the variable, just use it like the link in post 6 in the open event of the subreport.

 

[bigalpha]

I did
Me.Filter = strwhere
Me.FilterOn = True
But got an error: procedure declaration does not match description of event or procedure having the same name.

 

[pbaldy]

Can you post the db here?

 

[bigalpha]

Sorry for the delay, was off of work dealing with a stolen vehicle

Would you just like the reports we're talking about or the entire database?

 

[pbaldy]

Bummer, I've had to deal with one of those. All I need is the parts necessary to recreate this problem, so that if successful it would work.

 

[bigalpha]

Okay, here are the relevant parts of the database. I sanitized the table of data.

In case you are wondering - I imported my word doc as an image file because I couldn't figure out how to take my data and export it into a word doc (which I imagine would be the better way).

Edit: Totally off topic. This is what they did.

 

[bigalpha]

So I got an email that someone replied to this but I don't see the reply!

 

[bigalpha]

Bumpity bump bump!