Open an Query Based Access Report on a specific record

[Guest]

This is my second attempt at explaining this! I hope this makes sense.

I have a Customer Order Form showing individual customer data from a
Customer Table.
Each customer has a unique ID, and this is displayed on the form.

I also have a report that is generated from a Query using data from a second
table with additional information about each customer.

Records from both tables are linked by the Customer ID.

This Query asks for the Customer ID in a dialogue box before running the
report.

I would like to have a button on the Order Form that automatically enters
the Customer ID currently showing on the Order Form into the dialogue box,
allowing the report to open showing details of the currently viewed customer.

I want to avoid entering the Customer ID number every time I want to view a
report on the customer I am currently working with on the Order Form.

Can you help?

 

[Rick B]

DID YOU SEARCH? These posts remain out there for years so you can read
answers to previous questions. Use your resources. This is a common
question!!!

Since you want is fed to you...


Either change your query to pull the number from the form instead of asking
the user...
=Forms![MyFormName]![MyFieldName]


** OR**

Take the criteria out of your query so the report pulls ALL records, then
use the following:

Private Sub cmdPrint_Click()

Dim strWhere As String

If Me.Dirty Then 'Save any edits.

Me.Dirty = False

End If

If Me.NewRecord Then 'Check there is a record to print

MsgBox "Select a record to print"

Else

strWhere = "[ID] = " & Me.[ID]

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

End If

End Sub



Notes: If your primary key is a Text type field (not a Number type field),
you need extra quotes: strWhere = "[ID] = """ & Me.[ID] & """"

If you want the report to print without preview, replace acViewPreview with
acViewNormal.