How to Print only one orderID at a time

[ManuelLavesa]

Hello All, I am having problems with Printing a report. I made a Sale Order Database that keeps the same OrderID for the different items on the order. When I try to Print the Invoice. the report looks like it is only one page but the output prints as many copies as items that are in the order, as they have the same OrderID. Is there a way of filtering this so that I only get one copy of the invoice.
The command line that I am using for Opening the report is:

DoCmd.OpenReport "InvoiceReport", acViewPreview, , "fkOrderID=" & Me.OrderID



which filters it for the right OrderID, but I can not go any further. Thank you for your help

[ItsMe]

Sounds like you need to include an additional attribute in your filter criteria. Is there a column, in addition to your OrderID column, that distinguishes the desired records from the others? What is the name of this column? Add that column to your criteria.

[ManuelLavesa]

Hello ItsMe Thank you for your reply. I just don't know how to Add more criteria. I just thought you can have one criteria in the statement.
Cmd.OpenReport "InvoiceReport",acViewPreview, "fkOrderID="& Me.OrderID
Could I Add more criteria? If so how would you do it? I have another column named InvoiceNumber That also has the same number for all the items ordered.

[June7]

As many criteria as you want - just keep concatenating literal text and variables with AND and/or OR operators. Something like:

"fkOrderID=" & Me.OrderID & " AND ItemStatus='Sent'"

However, I don't think the issue is filtering but report design. I suspect you have data in wrong report section. If you want to provide db for analysis, follow instructions at bottom of my post.

[ManuelLavesa]

Attachment 21963 Hello June7 you might be right that my report design could be improved. I am sending you my database for review. To see the problem go to New Sale Order button. Enter a customer and 2 or 3 products. then press the Print Invoice button. and Then export to PDF and you will see that there are as many copies of the invoice as there are items that you ordered. I just want one copy. I Export to PDF to save on paper, but it also happens when printing. And it happens to all my reports. Thank you for your help

[June7]

This is a report/subreport arrangement. Don't put OrderDetails in the main report and don't put Customers and Orders tables in the subreport.

Alternatively, use one report (no subreport) along with report Sorting & Grouping features.

NOTE: several fields in Customers table have spaces and punctuation (underscore is acceptable exception) in names. Better would be Email_Address.

[ManuelLavesa]

Thank you for all the suggestions. I will start making new reports to fix this.

[ManuelLavesa]

I made a new report with no subreports and grouped by OrderID and fixed the original problem, meaning I don't make extra copies of the report. Now the problem I am having is in Printing Reports with new data. That is if I use the DataEntry Mode on the form and enter new data, the printout looks fine with all the formatting etc but the cells are empty (No Data) . Then when I use the form without dataEntry Mode and search for the last order I entered it prints fine with all the data. The same problem happens when I go to new record on the form and enter a new record. So the question is what is generating this problem the report or the form when entering a new record ? Maybe the data is not sent to the report until it is saved on the tables and that is why I get blank cells. Thank you for all your help.

[June7]

Yes, record is committed to table when move to another record, close form, or run code (one way: DoCmd.RunCommand acCmdSaveRecord). Must commit the record before it is available for report.

[ManuelLavesa]

Yes that did the trick... Thank you June7