Print Subset of Records from From

[ssthornton]

I have a data entry form that displays invoices with the usual buttons to navigate (First, Last, Previous, Next) through the records, as well as a preview and/or print a report based on the current record. The formatting of the report is based on choices the user makes on the form.
The user can apply a filter by form that returns a subset of the records. Can anyone suggest a method of printing each of these invoices with "one click" rather than printing the first, clicking MoveNext, print, MoveNext etc
I would put me at the beginner to advanced beginner level
Thanks in advance for any suggestions

[June7]

What report formatting is influenced by user choices on form?

The report needs to be designed to print headers and to start new page for each customer.

Then options: Provide the report with filter criteria that would limit the report's recordset. The criteria can be passed by query parameters that reference the controls on form as inputs or with the WHERE CONDITION argument of DoCmd.OpenReport or set the report's Filter property to the form's Filter property.

[ssthornton]

Sorry, I was incorrect on the report formatting. The form frmOrderEntry displays invoices and the report rptInvoices has its filter set to the order currently being displayed, as follows [OrderID]=[Forms]![frmOrderEntry]![OrderID]

I have created a copy of rptInvoices named rptYellows (literally to print copies of each invoice, if required, on yellow paper) which is based on the same query. From a form called frmPrintReports I have written the following code to loop through each invoice and print the yellow copy if appropriate

Dim stDocName As String
Dim stDailyReport As String
Dim intNumberRecords As Integer
Dim InvoicePaid As Boolean
Dim PrintYellows As Boolean
Dim InvoiceDate As Date

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()



' Do While Not (rs.EOF)
Set rs = db.OpenRecordset("tblInvoices")
Me.txtCriteria = rs!OrderID
Me.txtCriteria2 = rs!CustomerID
Me.txtCriteria3 = rs!Date

InvoicePaid = DLookup("[Paid]", "tblInvoices", "[OrderId] = Forms!frmPrintReports.txtCriteria")
PrintYellows = DLookup("[PrintYellows]", "tblCustomers", "[CustomerId] = Forms!frmPrintReports.txtCriteria2")
InvoiceDate = DLookup("[Date]", "tblInvoices", "[OrderId] = Forms!frmPrintReports.txtCriteria")

If InvoiceDate >= Me.BeginningDate And InvoiceDate <= Me.EndingDate And InvoicePaid = False And PrintYellows = True Then
stDocName = " rptYellows"
DoCmd.OpenReport stDocName, acPreview, [OrderID] = [Forms]![frmPrintReports]![txtCriteria]
Else
MsgBox "Not printing Invoice # " & Me.txtCriteria, _
vbExclamation, conAppName
End If



' rs.MoveNext

'Loop
rs.Close
Exit Sub

End Select

Exit_cmdPrintAllReports_Click:
Exit Sub
Err_cmdPrintAllReports_Click:
MsgBox Err.Description
Resume Exit_cmdPrintAllReports_Click

End Sub

I am having trouble with the correct syntax for passing the orderID to the report. Should I leave the rptYellows filter blank?
Thanks

[June7]

Must concatenate variables into the WHERE argument. Reference to form control is a variable. So each of the DLookup's is wrong.

DLookup("[Date]", "tblInvoices", "[OrderId] = " & Forms!frmPrintReports.txtCriteria)

The space in front of the form name will probably be ignored but no reason to have it in there.
" rptYellows" should be "rptYellows"

The DoCmd syntax is wrong, use:
DoCmd.OpenReport stDocName, acPreview, , "[OrderID] = " & [Forms]![frmPrintReports]![txtCriteria]

[ssthornton]

That worked perfectly. The sub now runs through the invoices and prints them if appropriate This will save me quite some time so thank you very much! There is one small report formatting error (that is not critical) that occurs. I format some labels on the report based on the value of the field Category, in tblInvoices, which is defined as a number. The data entry person (me) chooses one of six options from an option group on the data entry form. The report then formats the labels based on a select case as follows

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Select Case Category
Case Is = 1
Me!lblRegKey.Caption = "P.I.D."
Me!lblRegKey2.Caption = "Doc. #"
Case Is = 2
Me!lblRegKey.Caption = "Action #"
Me!lblRegKey2.Caption = "Plaintiff"
Case Is = 3
Me!lblRegKey.Caption = "Routing Slip"
Me!lblRegKey2.Caption = "Inc. Number"
Case Is = 4
Me!lblRegKey.Caption = "Routing Slip"
Me!lblRegKey2.Caption = "M.H.R. #"
Case Is = 5
Me!lblRegKey.Caption = "Routing Slip"
Report!lblRegKey2.Caption = "Doc. ID"
Case Is = 6
Me!lblRegKey.Caption = "Ship #"
Me!lblRegKey2.Caption = "Ship Name"
End Select
End Sub

Other than that the reports render correctly so I can remove that formatting if neccessary, which allows the report to print, but it would be nice to have it. The error is invalid use of ! or dot . operator or invalid use of parenthesis

[June7]

Note that when using tags, the / goes to the front of the tag, like: [/code]. I edited your posts to reflect that.

Try Me. instead of Me!

The dot is more appropriate for use with controls and the ! for fields. The dot will also provoke intellisense popup tips.

[ssthornton]

After changing the code per your suggestion and still getting the same error, I looked at the only other code in the sub (I assumed your suggestion was correct, so it had to be something of mine) Turned out the control behind the Select statement was wrong. I have a hidden field Category on the report, which was "getting" its value from frmOrderEntry. I changed the control source of Category to tblInvoices and everything now works. Thanks again for your suggestions and insight. This is a valuable forum and much appreciated