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

I tried to print multiple invoices from your "Create Multiple Invoices" form and after I chose a couple and click on "Create Invoices" (Single file) I get an error "The output to action was canceled", if I choose "Create Invoices" (Multiple files) I get an error " Path Not Found"

Answer 2

In my demo the button's Click event procedure outputs the selected invoices to a single PDF file, hence the need for a path to the file. In your case you want to print multiple invoices, for which the event procedure's code would be modified as follows:

   Dim varItem As Variant

    With Me.lstInvoices
        If .ItemsSelected.Count > 0 Then
            'build list of invoice numbers in hidden control
            Me.txtInvoiceList = ""
            For Each varItem In .ItemsSelected
                Me.txtInvoiceList = Me.txtInvoiceList & "," & .ItemData(varItem)
            Next varItem
            'remove leading comma
            Me.txtInvoiceList = Mid(Me.txtInvoiceList, 2)
            DoCmd.OpenReport "rptInvoiceMultiple", View:=acViewNormal
        Else
            MsgBox "No invoice selected.", vbExclamation, "Invalid Operation"
        End If
    End With

Note that you will need a hidden text box control named txtInvoiceList in the form, to act as a parameter. You will also need to call the InParam function in the WHERE clause of the report's RecordSource query with:

WHERE (InParam([Invoices].[InvoiceNumber],[Forms]![frmInvoicesDlg]![txtInvoiceList]) = TRUE

You will need to add the following module, originally published by Microsoft and containing the InParam and GetToken functions, to your database to enable the InParam function to be called:

Option Compare Database
Option Explicit

Function GetToken(stLn, stDelim)

Dim iDelim As Integer, stToken As String

iDelim = InStr(1, stLn, stDelim)

If (iDelim <> 0) Then
    stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
    stLn = Mid$(stLn, iDelim + 1)
Else
    stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
    stLn = ""
End If

GetToken = stToken

End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' to be referenced, in which you can insert a list of values.
' The values inserted are interpreted as if you
' had entered them within the parentheses of the IN() operator.
'============================================================
Function InParam(Fld, Param)

Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)

If IsNull(Fld) Then Fld = ""
    Do While (Len(Param) > 0)
    stToken = GetToken(Param, ",")
    If stToken = LTrim$(RTrim$(Fld)) Then
        InParam = -1
        Exit Function
    Else
        InParam = 0
    End If
Loop

End Function

Answer 3

Thank you very much for your efforts.
I think I am over my head with this code, I did everything per your instructions and when I put in the code for the event procedure and do a Debug, Compile it stops at
With Me.lstInvoices, with the message "Method or data member not found", I did put a text box and name it lstInvoices to see what happened to the code and then it stop at
.ItemsSelected same message.

Answer 4

The ItemsSelected collection is a property of a multi-select list box, not of a text box, hence the error. It is a collection of variants, each of which points to a row selected in the list box. My demo uses a list box to select one or more invoices, so the use of this property is appropriate to my code. If you want to define the invoices to be printed in some other way then you will need to amend the code accordingly. If, for instance, you can define the invoices to be printed by a query which returns a set of distinct invoice numbers, then you could establish a recordset based on the query's SQL statement, and loop through the recordset to build a comma-separated list of invoice numbers in a hidden text box control in the form, and reference the control as a parameter in the report's RecordSource query by means of the InParam function, as with my demo.

If you can let us know just how you will define those invoices to be printed, then we would be able to advise you further. At present we can only provide examples of how it might be done.

Answer 5

Thank you very much your time and patience is greatly appreciated.
Here is the SQL I have to show the Customers that are due for renewal in a form named "RenewalForm" in Datasheet view (would like to have it in a multi-select list box) :

SELECT ColdStorageInvoicingQuery.BilledDate, ColdStorage2323.LotNumber, ColdStorageInvoicingQuery.ShipName, ColdStorageInvoicingQuery.OID, ColdStorageInvoicingQuery.DateIn, ColdStorage2323.BoxesIn AS PalletsIn, ColdStorageInvoiceQuery2.SumOfPalletsOut, IIf(Month([BilledDate])=2,DateAdd("d",28,[BilledDate]),DateAdd("d",31,[BilledDate])) AS [Month], ColdStorage2323.CID, ColdStorage2323.[Renewal Rate], ColdStorage2323.ProductDescription, ColdStorage2323.Fresh, ColdStorage2323.Frozen
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;

Once I see the names of the Customers in the above form, I clicked in the "ShippedName" box (I have an event procedure on click property) please see below

Private Sub ShipName_Click()
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 = Forms!Coldstorage2323!DateIn
'Forms!OrderForm!ShippedDate = Forms!Coldstorage2323!DateIn
'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}"
Pause (2)
DoCmd.Close acForm, "RenewalForm"
DoCmd.OpenForm "RenewalForm", acFormDS, , , , acIcon

The above code will fill an "OrderForm" form before it prints a Report named "Invoice", "Button76" is the command with code to print the Report named "Invoice" . Here is the SQL from the "OrderForm" (we use this form for more than just the "RenewalForm"):

SELECT DISTINCTROW ShipAddress.OID, ShipAddress.CID, Employees.EID, ShipAddress.EID, Customers.CompanyName, Customers.Address, Customers.City, Customers.St, Customers.PostalCode, ShipAddress.ShipName, ShipAddress.ShipAddress, ShipAddress.ShipCity, ShipAddress.ShipState, ShipAddress.ShipPostalCode, ShipAddress.ShippedDate, ShipAddress.OrderDate, [FirstName] & " " & [LastName] AS [Employee Name], ShipAddress.OrderedBy, ShipAddress.Notes, ShipAddress.PackInstructions, ShipAddress.Cash, ShipAddress.Charge, ShipAddress.COD, ShipAddress.Deliver, ShipAddress.Pickup, Customers.CLimit, Customers.Wholesale, Customers.MailStatement, ShipAddress.Void, Customers.COD2, Customers.Cash2, Customers.Charge2, Customers.Deliver2, Customers.Pickup2, Customers.Weekday, Customers.Weekday2, ShipAddress.Credit, ShipAddress.FOP, ShipAddress.IP, Customers.EID, Customers.Special, Customers.Retail, Customers.Phone, Customers.Fax, ShipAddress.FU, Customers.Inactive, Customers.DelDay, ShipAddress.OrderTime, ShipAddress.FOrderPerson, ShipAddress.Comments, ShipAddress.Pallets, ShipAddress.PrintTime, ShipAddress.FOrderPerson2, ShipAddress.EIDC, ShipAddress.EIDC2, ShipAddress.PalletsRet, ShipAddress.LotNumber
FROM (Employees INNER JOIN (Customers INNER JOIN ShipAddress ON Customers.CID = ShipAddress.CID) ON Employees.EID = ShipAddress.EID) INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.ShippedDate)>=Date()-3) AND ((Customers.Inactive)=No));

Here is the SQL for the report named "Invoice" (we use this report for more than just the "RenewalForm")

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));

Again thank you for your time and efforts.