Making multiple Invoices automatically

Question

I have a form named "RenewalForm" with a query that is not updatable where customer's records that are up for renewals show up.
I have a code under "ShipName" in that form that when I click, it does the invoice and prints it.
Is there a way that I can do all of them without having to click on "ShipName" for each?

Thank you.

Answer 1

You might find the methodologies I've described in this thread easier to understand if you take a look at a little file named MarioJaile.accdb which I've placed in my public databases folder at:

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

This uses a very simple orders database to illustrate three methods of opening a report of all outstanding invoices, defined by the orders having been despatched, but not yet paid for. The methods are:

1. This is is simplest method and simply restricts the report's RecordSource query on the Despatched and InvoicePaid columns.
2. This restricts the report by including in its RecordSource a query which returns only those InvoiceID despatched, but not yet paid for.
   3. This inserts a list of InvoiceID values into a hidden text box control in the form. This text box is then referenced as parameter in the report's RecordSource query by calling the InParam function in the query's WHERE clause. Note that the hidden text box in the form is only required for this method. If possible use one of the above two methods, which are far simpler.

Answer 2

I can't thank you enough for everything that you have done to help me.
I think that database clears up part of my question, but remember that I have to make the Invoice before I can print it .
Here is the code to make the Invoice:
DoCmd.OpenForm "OrderForm"
DoCmd.GoToRecord acForm, "OrderForm", acNewRec
Forms!OrderForm!OrderDate = Date
Forms!OrderForm!ShippedDate = Date
Sendkey "{ENTER}"
Sendkey "{ENTER}"
Forms!OrderForm!CID = Forms!RenewalForm!CID
Forms!OrderForm!OrderDate = Date
Forms!OrderForm!ShippedDate = Date
Sendkey "{ENTER}"
Sendkey "{ENTER}"
'Forms!OrderForm!OrderBy = Forms!coldstorage2323!OrderBy
Sendkey "{ENTER}"
Forms!OrderForm!EID = Forms!CurrentUser!Text4
Forms![OrderForm]![Notes] = Forms!RenewalForm!ProductDescription
Forms!OrderForm!LotNumber = Forms!RenewalForm!LotNumber
If (IsNull(Forms![OrderForm]![OID])) Then
Forms![OrderForm]![OID] = DMax("[OID]", "ShipAddress") + 1
End If
Pause (1)
DoCmd.GoToControl "OrderSubform"
Forms![OrderForm]![OrderSubform].Form![Box] = Forms!RenewalForm!BillablePallets
Sendkey "{ENTER}"
Pause (1)
Sendkey "{ENTER}"
Pause (2)
Sendkey "ZzStorage01"
Pause (2)
Sendkey "{ENTER}"
DoCmd.GoToControl "combo280"
DoCmd.GoToControl "OrderSubform"
Forms![OrderForm]![OrderSubform].Form![WeightUnits] = Forms!RenewalForm!BillablePallets
Forms![OrderForm]![OrderSubform].Form![TWeightUnits] = Forms!RenewalForm!BillablePallets
Forms![OrderForm]![OrderSubform].Form![ExtPrice] = Forms!RenewalForm!InvAmt
Forms![OrderForm]![OrderSubform].Form![BoxShip] = Forms!RenewalForm!BillablePallets
Forms![OrderForm]![OrderSubform].Form![QShipped] = Forms!RenewalForm!BillablePallets
Sendkey "{F9}"
DoCmd.GoToControl "combo280"
Forms!OrderForm!FOrderPerson = Forms!CurrentUser!Text4
DoCmd.GoToControl! "Button76"
Sendkey "{ENTER}"
'once the Invoice is made in the "OrderForm"" then Control! "Button76" is what prints the Invoice report, please see
'the code for "Button76" below:
DoCmd.OpenReport "Invoice", A_PREVIEW
DoCmd.PrintOut A_PRINTALL, , , , 2, 0
DoCmd.Close A_REPORT, "Invoice"

Answer 3

Your methodology for computing invoices is rather unusual. As far as I can see you are mainly copying values from one form to another. What is not clear is whether that data is then saved to rows in tables before printing. This is really the crux of the matter. If the data is saved, then you should be able to open a report of multiple invoices, in the same way that my simple demo does. It's just a matter of restricting the report's RecordSource query to the relevant subset of rows from the tables to which the data has been saved. My demo does this by restricting the query to those rows where the order has been despatched but not paid for. How you would restrict your data is for you to decide, it's not something I can advise you on, but in principle it would be similar, though more complex. An example of a report where this is done can be found in the Northwind sample database distributed by Microsoft. In this the invoice report is based on the following query:

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address],
Orders.[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal Code],
Orders.[Ship Country/Region], Orders.[Customer ID],
Customers.Company AS [Customer Name], Customers.Address, Customers.City,
Customers.[State/Province], Customers.[ZIP/Postal Code], Customers.[Country/Region],
[Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date],
Orders.[Shipped Date], Shippers.Company AS [Shipper Name], [Order Details].[Product ID],
Products.ID AS [Product ID], [Order Details].[Unit Price], [Order Details].Quantity,
[Order Details].Discount,
CCur(Nz([Unit Price][Quantity](1-[Discount]),0)/100)*100 AS ExtendedPrice,
Orders.[Shipping Fee], Products.[Product Name]
FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders
LEFT JOIN [Employees Extended]
ON Orders.[Employee ID] = [Employees Extended].ID)
ON Customers.ID = Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID])
LEFT JOIN ([Order Details] LEFT JOIN Products
ON [Order Details].[Product ID] = Products.ID)
ON Orders.[Order ID] = [Order Details].[Order ID];

As you can see this in essence does the same as my demo, i.e computing the invoice data from orders data with a query, but with a rather more complex query than my simple examples. I would expect that you would be able to do something similar.

While the above example is the usual way of computing invoices, if your unusual method does result in the data being stored in rows in tables, then there should be no problem in printing multiple invoices once you have worked out the criteria necessary for restricting the report's query to the relevant data. The only requirement would be that the relevant rows for all the outstanding invoices have been inserted into the tables when the report is printed.

Answer 4

Thank you.
Data is saved to different tables.
In order to open the report with multiple invoices, I have to first save the data to the tables that contain the values that will appear in the reports. The only way I know how to save the information to the tables is by copying the relevant values form one form to the other form. It serves 2 purposes, I can look at the information in each form with some of the values different and I can print different reports from each form.
That is why my initial question included if there was a way to make multiple invoices (storing the information in the tables) and then printing those multiple invoices of the information that was just saved.

Again thanks.

Answer 5

The usual method to insert data into tables is by means of an 'append' query, i.e. an INSERT INTO statement where the values to be inserted are and supplied by a SELECT statement. Where data is to be inserted into a number of related tables the rows must be inserted into the referenced table, i.e. that on the 'one' side of the one-to-many relationship, before being inserted into the referencing table, i.e. that on the 'many' side. The following is an example taken from one of my demo files:

INSERT INTO Cities (City, RegionID)
SELECT DISTINCT MasterTable.City, Regions.RegionID
FROM Countries INNER JOIN (MasterTable INNER JOIN Regions
ON MasterTable.Region=Regions.Region)
ON (MasterTable.Country=Countries.Country)
AND (Countries.CountryID=Regions.CountryID);

This inserts rows into a cities table from a non-relational master table imported from elsewhere. Prior to this rows will have been inserted into the Referenced Countries and Regions tables.

However, this is not really the central issue of this thread. The way in which the data is inserted into the relevant tables is, in itself, not material to what you are attempting. It is the fact that the data has been inserted into tables which then allows you to build a query on those tables to act as the RecordSource of a report. If that query were to be unrestricted the report would output all invoices which have ever been created. You want to output only a subset of those invoices, however, so you need to identify the criteria for a query's WHERE clause to return only that subset of rows. I can't advise you on that as I am not familiar with the data, but you should be able to identify what criteria are needed and build the logical expressions for the WHERE clause. Opening the report based on this restricted query will then allow you to print the desired subset of invoices. My demo provided a simple example of this as method 1. The other two alternative methods do the same thing, but are unnecessary, as a simpler method is available. I only included them as examples of methodologies which might be appropriate in the absence of a simple restricted query.