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

Am I to understand that the final query n your last reply enables you to print multiple invoices successfully?

Answer 2

The use of the InParam function in the query should allow it to output multiple rows. In fact that is the whole point of the function. To achieve this the txtInvoiceList in the form would need to contain a comma-separated list of InvoiceNumber values. This is what the hidden text box in the form in my demo does.

So, what you need is a way of populating txtInvoiceList with a list of the numbers of those invoices you wish to print as a single report. As I said before, rather than having to select the invoices to be printed in a list box, as in my demo, you could loop through a recordset of the invoice numbers. The first thing you need, therefore, is to devise a query which returns just those invoice numbers to be printed. I'm not familiar with your business model of course, so I will need to leave that to you, but lets assume you can do this in a query named qryInvoicesToPrint. The code, which should be in the form's module, e.g. as a button's Click event procedure, to populate the txtInvoiceList control would then be:

Const conSQL = "SELECT InvoiceNumber FROM qryInvoicesToPrint"
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(conSQL)

Me.txtInvoiceList  = ""
With rst
    .MoveLast
    .MoveFirst
    Do While Not .EOF
        Me.txtInvoiceList  = Me.txtInvoiceList & "," & Fields("InvoiceNumber")
        .MoveNext
    Loop
End With

If Len(Me.txtInvoiceList) <> 0 Then
    Me.txtInvoiceList = Mid(Me.txtInvoiceList,2)
End If

Having populated the text box, you should then be able to print the multiple invoices as a single report.

Answer 3

When I click on the command button, error message "This query is not updatable"

Here is the qryInvoicesToPrint query:

SELECT ColdStorageInvoicingQuery.BilledDate, ColdStorage2323.LotNumber, ColdStorageInvoicingQuery.ShipName, ColdStorage2323.ProductDescription, ColdStorage2323.[Renewal Rate], ColdStorage2323.BoxesIn AS PalletsIn, ColdStorageInvoiceQuery2.SumOfPalletsOut, [PalletsIn]-[SumofPalletsOut] AS BillablePallets, [Renewal Rate]*[BillablePallets] AS InvAmt, ColdStorageInvoicingQuery.OID, ColdStorageInvoicingQuery.DateIn, ColdStorage2323.CID, ColdStorage2323.Fresh, ColdStorage2323.Frozen, IIf(Month([BilledDate])=2,DateAdd("d",28,[BilledDate]),DateAdd("d",31,[BilledDate])) AS [Month]
FROM (ColdStorage2323 LEFT JOIN ColdStorageInvoicingQuery ON ColdStorage2323.LotNumber = ColdStorageInvoicingQuery.ColdStorage2323.LotNumber) LEFT JOIN ColdStorageInvoiceQuery2 ON ColdStorage2323.LotNumber = ColdStorageInvoiceQuery2.LotNumber
WHERE (((ColdStorageInvoicingQuery.BilledDate)<=Date()-30) AND ((ColdStorage2323.DateClosed) Is Null))
ORDER BY ColdStorageInvoicingQuery.BilledDate, ColdStorage2323.LotNumber;

Answer 4

Firstly, does the code I gave you earlier for populating the txtInvoiceList control, with the correction made to the code, now correctly fill that control with a comma separated list of lot numbers?

If so, then the only step necessary to print the multiple invoices should be to call the OpenReport method to print a report with the query you cited earlier as its RecordSource property:

SELECT DISTINCTROW ShipAddress.OID, ShipAddress.OrderDate, ShipAddress.ShippedDate, [FirstName] & " " & [LastName] AS [Employee Name], Customers.CompanyName, Customers.Address, Customers.City, Customers.St, Customers.PostalCode, ShipAddress.ShipName, ShipAddress.ShipAddress, ShipAddress.ShipCity, ShipAddress.ShipState, ShipAddress.ShipPostalCode, OrderDetails.Box, OrderDetails.Pcs, OrderDetails.WeightUnits, Products.ProductName, OrderDetails.TWeightUnits, OrderDetails.ExtPrice, ShipAddress.Notes, Customers.Wholesale, ShipAddress.PackInstructions, ShipAddress.Cash, ShipAddress.Charge, ShipAddress.COD, ShipAddress.Deliver, ShipAddress.Pickup, ShipAddress.CID, ShipAddress.OrderedBy, Products.PrdtID, Customers.Special, IIf([wholesale]=Yes,[OrderDetails].[WPrice],IIf([Special]=Yes,[OrderDetails].[TDPrice],[OrderDetails].[Price])) AS PPrice, OrderDetails.QShipped, Products.PCode, Suppliers.SupplierBCNumber, Products.Weight, Products.PPCode, Products.Inv, OrderDetails.BoxShip, ShipAddress.Comments, ShipAddress.Pallets, ShipAddress.FOrderPerson2, ShipAddress.FOrderPerson, ShipAddress.PalletsRet, Products.ProductNameSpanish, ShipAddress.LotNumber
FROM Suppliers INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN ShipAddress ON Customers.CID = ShipAddress.CID) ON Employees.EID = ShipAddress.EID) INNER JOIN (Products INNER JOIN OrderDetails ON Products.PrdtID = OrderDetails.PrdtID) ON ShipAddress.OID = OrderDetails.OID) ON Suppliers.SupplierID = Products.SupplierID
WHERE (((InParam([Invoices].[InvoiceNumber],[Forms]![frmInvoicesDlg]![txtInvoiceList]))=True));

However, the InParam function in the above references the column [Invoices].[InvoiceNumber], but I cannot see any references to the Invoices table in the query's FROM clause.

Moreover it would assume that the InvoiceNumber column referenced by the InParam function in the above query and the LotNumber column, a list of which is contained in the txtInvoiceList control, are one and the same thing. Is that the case? I note that LotNumber in the query is a column of the ShipAddress table.

None of this should involve the execution of an 'action query', so I cannot understand why you should be experiencing the 'This query is not updatable' error. Does the error dialogue have the option to debug the error: if so what line of code is highlighted? If not you would need to set a breakpoint at the top of the relevant code and step into the code line by line until the error occurs. You'd then be able to see what line causes the error.

Answer 5

Forget the fact that my demo uses a list box. In your case the txtInvoiceList control should be a text box control, not a list box. The InParam function has two arguments, the name of a column in a table or query, and a comma-separated value list of data. Lets say you were to have a table of invoices whose primary key is InvoiceID, and you wanted to print a report for invoice ID numbers 123, 456, and 789, then the value in the text box would have to be 123,456,789. You'd then need a query which calls the InParam function with the InvoiceID column name and a reference to the txtInvoiceList control as its two arguments. This query would be the report's RecordSource.

However, if the InvoiceID values for the invoices to be printed can be returned by a qryInvoicesToPrint query, then an alternative solution would be to simply join that query to the report's RecordSource query on InvoiceID:

SELECT ReportsQuery.*
FROM ReportsQuery INNER JOIN qryInvoicesToPrint
ON ReportsQuery.InvoiceID = qryInvoicesToPrint.InvoiceID;

Because an INNER JOIN requires matches in both tables this new query will be restricted to those InvoiceID values returned byqryInvoicesToPrint, so there would be no need to call the InParam function at all, and no need for the txtInvoiceList control in the form. You'd simply make the new query the report's RecordSource property.