Printing Subreports

For examples of the sort of unbound forms Scott is referring to in his last post you might like to take a look at MultiSelect.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how to open a report whose results are restricted by means of controls in a form.  It demonstrates means of doing this in a variety of contexts.

For an example of a report opened from a bound form to show data for the current record only, the InvoicePDF demo in the same OneDrive folder illustrates this.  In this case the report's query includes a parameter which references the form's order number, rather than the report being filtered by the WhereCondition argument of the OpenReport method. The reason this method is used is that it also enables the report to be output directly as a PDF file, which is the main purpose of this demo.  Theoretically it would be possible to add the rptIInvoice report to this form as a subreport, and also preview it from the Open Invoice button in the form.

Yes it certainly would. This is another not recommend practice. Key fields and their foreign key equivalents should all have the same names. If there is no CustNo field in the Recordsource of the report, then it would certainly cause a problem try changing it to "QNCLI =" & Text25.

However, I still think you have made a lot more trouble for yourself than necessary. A form that is ONLY used to open a report for preview should be unbound. it should have one (or more) controls to enter criteria and another control to open the report(s). 

In your case, I would use a Combobox to select a Customer by name (but return the customer number) and a button to open the report. Very simple.

I am still getting the same Syntax error.

My form is run off a table where the customer # in that table is spelled as "CustNo" whereas the query where I am running my report it is spelled as "QNCLI".  They have a parent/child relationship but would this still make a difference?  

Aha. Text25 is on your form, NOT on the Subreport, which is where you are running the code from. That's why the error.

Either put the button to run the report on the form (which is what I recommended) or change the code to:

DoCmd.OpenReport "srptCode_QandJ_Credits", acViewPreview, , "[CustNo]=" & Forms![CUSTOMER PROFILE]!Text25

But as I said earlier, by embedding the subreport on the form, you are being redundant. All you need is a form with a control to enter the Customer and a button to open the report in Print Preview.

Ok I changed the report name as you suggested and put the new code but it's now saying "Data member not found' for my Text25?

Could this error be because my search button is on the form, whereas this new button I am adding is on the report?