Specifing a single record for a report

[Guest]

I have a reprt that I will need run after every order that is entered and
will need to see just the information from that order. Is there a way that I
can either set up a button in the orders form I have created or to have a
data entery box when the report is opened asking for what order? Any and all
help is appreciated.

 

[Guest]

Set up a button in the orders form you have created so when pushed it runs a
report based on a query that has criteria from the form.

Most order forms have an order number field - use that as criteria in the
query like this --
[Forms]![YourFormName]![YourOrderNumberTextBox]

In design view of the form click on the button and set Double Click property
to run a macro. Create the macro to run the report.

 

[Guest]

Another way is to create a report which would show all orders and filter it
when you open it from a button on the orders form. You also need to ensure
that the new record is saved before opening the report or it won't be
included. The code for a button's Click event provedure would go like this,
on the assumption that the OrderID field (or whatever you've called it) is a
number data type:

Dim strCriteria As String

' first make sure an order has been created
If Not IsNull(Me.OrderID Then
strCriteria = "OrderID = " & Me.OrderID
' ensure record is saved
RunCommand acCmdSaveRecord
' open report in preview
DoCmd.OpenReport "rptOrders", View:=acViewPreview,
WhereCondition:=strCriteria
End If

Whre rptOrders is the name of the report. If the OrderID field is of text
data type you'll need to wrap the value in quotes:

strCriteria = "OrderID = """ & Me.OrderID & """"

Ken Sheridan
Stafford, England

 

[Guest]

Ok I have attempted to do this but I'm getting an error and am unsure as to
how this would be fixed.

Here is the code I've writen tell me if anything needs to be changed if you
could please.

Private Sub Bill_of_Material_Button_Click()
Dim strCriteria As String
If Not IsNull(Me.Order_Number) Then
strCriteria = "Order_Number = " & Me.Order_Number
RunCommand acCmdSaveRecord
DoCmd.OpenReport "Bill_of_Material", View:=acViewPreview,
Where Condition:=strCriteria
End Sub

Any and all help is greatly appreciated.

 

[Guest]

You have a space in WhereCondition in the following line. It should be a
single word:

DoCmd.OpenReport "Bill_of_Material", View:=acViewPreview,
WhereCondition:=strCriteria

Also it should be entered as a single line of code. It wraps over two lines
in the window here, but in the VBA window should be continuous.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you! I have one more question about this topic.
I was asked if there would be a way to make access select the current record
when the button was pushed. So say I have record 88750 open and that is the
record I want the report to pull. Is there an easy way to have access
automaticly know this is the record I want?

 

[Guest]

That's what the reference to Me.Order_Number does. Me is simply a shorthand
way of referring to the current form (technically speaking it refers to the
current instance of the class, but just think of it as referring to the
form). So if the form is at the record for order number 88750
Me.Order_Number will return that number, so the expression "Order_Number = "
& Me.Order_Number will evaluate to:

"Order_Number = 88750"

and this will be assigned to the strCriteria variable. When you open the
report with the WhereCondition argument set to strCriteria it filters the
report to "Order_Number = 88750" and the report thus only include the
record(s) which match that criterion.

Ken Sheridan
Stafford, England