There are a number of ways you can do this but the following should be simple
enough to implement.
First create a report which returns the orders for ALL job numbers. The
report can then be filtered to a selected job number by opening it from a
form. The form can either be one bound to a table or query, or can be an
unbound dialogue form. In the former case you'd filter it to the current
record's job number; in the latter case you could simply enter the job number
in a text box, but a better way would be to have a combo box which lists the
job numbers in order, so the user simply selects one from the list.
Whichever method you choose lets assume the control is called JobNumber, and
this is also the name of the underlying field in the table.
On the form place a button, or two buttons, one to preview the report, the
other to print it. The code which goes n the Click event of the button
depends on whether the JobNumber field is a number or a text data type. If
it’s a number data type the code would be:
Const conREPORTCANCELLED = 2501
Const conREPORT = "YourReportNameGoesHere"
Dim strCriteria As String
On Error Goto Err_Handler
strCriteria = "[JobNumber] = " & Me.[JobNumber]
If Not IsNull(Me.[JobNumber]) Then
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No job number selected.", vbExclamation, "Invalid Operation"
End If
Exit_Here:
Exit Sub
Err_Handler:
Select Case Err.Number
Case conREPORTCANCELLED
' anticipated error so do nothing
Case Else
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select
Resume Exit_Here
If the job number is a text data type you'd change one line to:
strCriteria = "[JobNumber] = """ & Me.[JobNumber] & """"
The code to print the report merely requires the acViewPreview to be changed
to acViewNormal.
To preview or print the report just open the form. If its bound to the
table or query navigate to the required job number record; if its an unbound
dialogue form enter or select the job number. Then click the relevant
button. The error handling takes account of the possibility of the opening
of the report being cancelled, e.g. by code in the reports NoData event
procedure if there are not yet any orders relating to the selected job
number, which raises error number 2501.
Ken Sheridan
Stafford, England
