OK, now to my report for my order form

Technics

Member
Local time
Today, 13:08
Joined
Aug 9, 2025
Messages
68
Through great help from this forum I now have a working order form/report that looks great. I would like to get some ideas on advancing the report. I'm looking into doing a check box or list box and also have looked at info on wherecondition but just not sure which would be the best and simplest way to go. Here is what I am trying to do. You can see by the photo that I have an order form and when I click the Invoice button it prints the report/invoice below with the one record from the order form. I would like to put multiple records on the same report like the other illustration below. That report was produced through cut and paste. It does not actually print like that.

ordinvoexample.jpg
 
You just select all the records for that report.

So you will have a header section
Then a detail section for all the records.
Then perhaps a footer section for totals.

Not sure why sub reports would be required?
 
For best results you would probably use subreports, but combine these with grouping the parent report. This gives you flexibilty over the layout of the invoice which grouping alone does not. You'll find an example in the attached little demo file. The Open Current Invoice button on the invoice form opens the current invoice as a report. In this most of the invoice is returned in the parent form's group headers/footers, and detail sections, but the aggregated amounts paid are returned independently in a subreport, whose total value is subtracted from the computed invoice total to give the amount due.
 

Attachments

Through great help from this forum I now have a working order form/report that looks great. I would like to get some ideas on advancing the report. I'm looking into doing a check box or list box and also have looked at info on wherecondition but just not sure which would be the best and simplest way to go. Here is what I am trying to do. You can see by the photo that I have an order form and when I click the Invoice button it prints the report/invoice below with the one record from the order form. I would like to put multiple records on the same report like the other illustration below. That report was produced through cut and paste. It does not actually print like that.

View attachment 121836
Hi
See the modified Order Form in the attached.

Now when you create a New Order and add multiple items you can then Click the Command Button "Preview order"
 

Attachments

I would like to get some ideas on advancing the report. I'm looking into doing a check box or list box and also have looked at info on wherecondition but just not sure which would be the best and simplest way to go. Here is what I am trying to do. You can see by the photo that I have an order form and when I click the Invoice button it prints the report/invoice below with the one record from the order form. I would like to put multiple records on the same report like the other illustration below.

The attached little demo file illustrates how line items from multiple orders per customer can be consolidated into a single invoice. If you take a look at the orders form you'll see that each line item in the order details subform includes an Add to Invoice check box. This adds that item to a single draft invoice per customer. The draft invoices can be viewed by means of the View Outstanding Invoices button on the opening menu form. At this stage they have no invoice numbers. Line items from orders can continue to be added to the draft invoices until you are ready to convert them to actual invoices.

The draft invoices can be converted to actual invoices by means of the Complete Outstanding Invoices button. The completed invoices can be viewed by means of the View Complete Invoices button, which opens a report in Print Preview. This report is based on the following query:

SQL:
SELECT
    Invoices.InvoiceNumber,
    Invoices.InvoiceDate,
    Orders.OrderID,
    Customers.CustomerID,
    [FirstName] & " " & [Lastname] AS Customer,
    Customers.Address,
    Cities.City,
    Regions.Region,
    Countries.Country,
    Items.Item,
    OrderDetails.Quantity,
    [Quantity] * [OrderDetails].[UnitPrice] AS Amount
FROM
    (
        Countries
        INNER JOIN Regions ON Countries.CountryID = Regions.CountryID
    )
    INNER JOIN (
        Items
        INNER JOIN (
            (
                Cities
                INNER JOIN (
                    Customers
                    INNER JOIN Orders ON Customers.[CustomerID] = Orders.CustomerID
                ) ON Cities.CityID = Customers.CityID
            )
            INNER JOIN (
                OrderDetails
                INNER JOIN Invoices ON OrderDetails.InvoiceID = Invoices.InvoiceID
            ) ON (Customers.CustomerID = Invoices.CustomerID)
            AND (Orders.OrderID = OrderDetails.OrderID)
        ) ON Items.ItemID = OrderDetails.ItemID
    ) ON Regions.RegionID = Cities.RegionID
WHERE
    Invoices.Complete = TRUE;

This report returns the invoices, grouped by customer. The only criterion is Invoices.Complete = TRUE, so the line items are drawn from the multiple orders per customer. In this report each invoice is numbered with the next available invoice number.

At present there is no facility in the demo to print completed invoices selectively - it's all or nothing. I do have a routine in another demo, however, which allows one or more invoices to be selected in a multi-select list box, and opened as a single report. I'll try to get this incorporated into the attached file over the next few days. This will then allow one or more selected completed invoices to be printed.
 

Attachments

Users who are viewing this thread

Back
Top Bottom