Create Report

[adam]

I’m trying to create a report from the “create new invoice” form where I want to include the results of the sub form in the report. But my report shows only the data in the standard form it does not show the data in the sub form.

Does this mean I have to create another query or I have to edit the formula in the Record source property of the report, in order to include the order details query?

Any help on this would be kindly appreciated.

[HansV]

Your subform is based on Order Details Query, which adds fields from the Products table and some calculated fields.

If you want to display the same data in your report, you must base the report on the Order Details Query too; you need to add the Price Type, Customer ID and Receipt No fields to this query.

See the attached version.

Report - Copy.zip

[adam]

The report displays all the rows in the order details query table instead of displaying the products from the order number 0001 only? How could the report be made so that it displays the products for the particular order?

[HansV]

Do you want the report to display only the records that correspond to the current order in the Create New Invoice form?

[adam]

Yes Hans. I'm trying the report to display only the records that correspond to the current order in the "Create New Invoice" form.

[HansV]

You don't have to modify the report for this. Place a command button on the form that opens the report and tells it to display only the records for the current Order ID. This is done with the instruction

Code: Select all

  DoCmd.OpenReport ReportName:="Report1", View:=acViewPreview, _
    WhereCondition:="[Order ID]=" & Me.[Order ID]

The square brackets around Order ID are required because the name contains a space.

See the attached version for the complete code - please take a look at it, I have provided comments to explain the steps.

Report - Copy.zip

[adam]

Thanks for the help and guidelines Hans. I've studied the principle of the code.

Apart from that, you’ve mentioned (in your previous reply) that my sub form is based on Order Details Query, which adds fields from the “Products table” and some calculated fields.

And that If I want to display the same data in my report, I must base the report on the “Order Details Query”; where I need to add the Price Type, Customer ID and Receipt No fields to this query.

Why have you specifically pointed out the fields Price Type, Customer ID and Receipt No to be added to the Query?

Was this because the form already contain the field Receipt No , Customer ID and the price type is to change to either Local or Foreigner for later modification benefit?

Moreover If I want to add the fields; Bill Date Request No and so on to the report (same as Receipt No) should they also be added to the “Order Details Query?

[HansV]

You wanted to display Customer ID and Receipt No on the report, but they were not yet part of the query. The Price Type field is needed for the calculation of Unit Price.

If you want to display other fields such as Receipt Date and Request No on the report, you need to add them to the query too. The report can only display fields that are included in its record source.

[adam]

I've edited the report to include the company information in it. I've written the company information under the header "Report Header" But the company information does not get included in every page of the report.

On the other hand the bill number, receipt number and the rest gets included in every page. I tried by placing the labels under the header "Page header" (Where the receipt number and bill number resides) which then gives a message saying that unassociated label.

How could I make the report to view the company information on every page of the report?

[HansV]

The report header will be displayed/printed once at the beginning of the report, it will not be repeated on other pages.

You should place the company info on the page header, and ignore the warning about unassociated labels.

[adam]

Thanks for the help Hans. How could I bring out the total to the total text box on the report same as the create new invoice form?

I've placed the formula =[Order Details Subform].[Form]![txtOrderDetailsTotal] in the total text box in the report but the total does not seem to appear.

[HansV]

Set the control source of the text box to

=Sum([Line Total])

[HansV]

If you want to refer to a control on a subform, you must do so like this:

=[Forms]![Name of the main form]![Name of the subform]![Name of the control]

In your example:

=[Forms]![Create New Invoice]![Order Details Subform]![txtOrderDetailsTotal]

[adam]

Thanks for the help Hans. I did try by using the formula =[Forms]![Create New Invoice]![Order Details Subform]![txtOrderDetailsTotal] and it works for me.

Apart from that, When I try to open a report from the command button on the create new invoice form with a order number 1 which contains four products in the sub form. the report opens it does not show the all the products in one page.

For example it shows each product row in a different sheet in print preview mode and the total text box in odd pages. How could I overcome this?

[HansV]

Have you grouped the report by product? Or have you set the Force New Page property of the Detail section to Before Section?

In which section have you placed the total text box?

[adam]

The total text box is placed on the detail section and the Force New Page property of the Detail section is set to none.

[HansV]

Try placing the total text box in the page footer instead of in the Detail section.

[adam]

Now the total text box is on every page. My intention is to make the total text box to appear on the last page of the report.

Suppose if the report contains two pages, the total text box to appear on the last page of the report.

By the way still the report shows each product row in a different sheet (odd page) in print preview mode

[HansV]

If you want the total only on the last page, place it in the Report Footer section.

I'd have to see (a stripped down, compacted and zipped copy of) the database to know why you get only one product per page. You can remove the company name etc. (but don't change the size of the sections).

[adam]

I cannot upload it as the forum is telling me that he file is too big, maximum allowed size is 256 KiB. since the zip file is 562kb