How to print report based on what user is looking at

[alexandervj]

I have a form/subform with the subform as a continuous form that looks like a spreadsheet. I want a way for the user to hit a 'Print Report' button after selecting various filters in the subform and it builds a report based on the filters selected. Is this possible? How would I go about this? Thanks

[pbaldy]

This might work:

DoCmd.OpenReport "ReportName", acViewPreview, , Forms!FormName.SubformName.Form.Filter

[alexandervj]

Thanks pbaldy! Seems to partially work - it opens the report for whats filtered in the subform but looks like its for multiple IDs in the form. So it opens up a report showing 2 groups, 1 group is the one I'm looking at in the form, then another group looks almost random. Is there a way to add a where to this? Like 'where ParentID=[ParentID_FK]?'

[pbaldy]

Oh, it probably isn't considering anything in the master/child links. How about:

DoCmd.OpenReport "ReportName", acViewPreview, , Forms!FormName.SubformName.Form.Filter & " AND ParentID = " & Me.ParentID

adjusting for your names of course.

[alexandervj]

trying to do something like this, although syntax is off im sure

DoCmd.OpenReport "rpt_Parts", acViewPreview, , [LotID]= Me.LotID_FK And [RunID]= Me.RunID_FK And Forms!frm_Parts_Datasheet.frm_Parts_Spreadsheet.Fo rm.Filter

So that it filters based on two "where ID=ID_FK" 's and also the part you gave me at the end. Thanks!

[alexandervj]

Awesome! That worked for adding 1 where clause, if I wanted to add 1 more would it look like...

DoCmd.OpenReport "ReportName", acViewPreview, , Forms!FormName.SubformName.Form.Filter & " AND ParentID = " & Me.ParentID " AND ParentID = " & Me.ParentID

[pbaldy]

Close; you missed an &.

[alexandervj]

Aha, how about this...

DoCmd.OpenReport "ReportName", acViewPreview, , Forms!FormName.SubformName.Form.Filter & " AND Parent1ID = " & Me.Parent1ID & " AND Parent2ID = " & Me.Parent2ID


Thanks again

[pbaldy]

Looks good...if it works.

[alexandervj]

Works great! And one more question - this keeps the same filters as in the report, is there a way to keep the same sort as well?

[pbaldy]

Are they doing their own filtering/sorting via built-in methods? I always control it all, so in my case they would make their selections and I would read the sorting selections in the open event of the report and apply them. I'm not sure how to do it with the user doing it on their own. You can test the Order By property of the form, but I'm not sure how to apply that to Sorting and Grouping on the report.

[alexandervj]

yeah, theyre doing their own sorting/filtering via built in methods. Its ok no worries...
On another note: I am also trying to open an identical form but with only parts with deleted field = true or in the "reject/trash" location. So should have the same WHERE statement but with those 2 conditions added. I tried this...
DoCmd.OpenReport "rpt_Parts_Inactive", acViewPreview, , Forms!frm_Parts_Datasheet.frm_Parts_Spreadsheet.Fo rm.Filter & " AND LotID = " & Me.LotID & " AND RunID = " & Me.RunID & " AND Deleted = True OR GeographicalLocation = 'Reject/Trash'"
but that obviously gives parts according to all the AND statments as well as parts in the OR statement, so it will return all parts in the reject/trash, even if they dont have the right LotID, and RunID,...
how can I group those last 2 statements? The AND Deleted = True OR GeographicalLocation = 'Reject/Trash'"

Thanks again!

[pbaldy]

How about:

...AND (Deleted = True OR GeographicalLocation = 'Reject/Trash')"

[alexandervj]

thats what I thought, but when I tried it nothing came up at all.. hmm..

[alexandervj]

Figured it out, forgot to change the form/subform name Thanks