Software

Ask something
Discussion Locked

Access-Print report from a form

0 helpful
2 replies

by haarsaker

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have made a Menu based Access 2000 application - but have one problem.

I want to - from a single record view form - to start preview of a defined report - with just the one record that is visible in the form.

Both the form and the report is based on the same query, and the result og the query displays more than one record - thus enabling me to page forward/backward from record to record in the form.

So I guess the question is really - from a multi-record query used in a form - how do you select the currently displayed record and start a report limiting it to just the one selected record.

Anyone ?

2 replies

by techsupportspecialist

You should create a separate query for your report. Create a new query and simply use it to query the other query. Add ALL the fields from the query into this new one. Find your Primary Key field in this query. Under criteria, add the following:
[Forms]![FORMNAME]![PRIMARYKEY]
That's the syntax. Make sure your primary key is on that form. If it's not on it, just add it and make it invisible. In the criteria above, substitute the form name for FORMNAME and the primary key for PRIMARYKEY. Make sure the Record Source for your report is pointing to this new query.

Good luck!

by adca

Personally I don't like embedding form control names in the query--but that suggestion will work, no doubt about it. I would take a different tack and use the wherecondition argument of the DoCmd.OpenReport method. Assuming you are using a command button, click event to get the preview AND that you have a primary key field you can use to uniquely identify the record, then a little bit of VBA something like this should do the trick:

Private Sub cmdPreviewRpt_Click()
Dim strReport As String
Dim strWhere As String

strReport = "Your Report Name Here"
strWhere = "[Name of your primary Key Field] = """ & Me![Name of Your Primary Key Field] & """"

DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

The """ ensure a true quote at the beginning of the criteria string and the """" ensure a true quote at the end of the string.

If your primary key field is numeric, change the strWhere assignment to:

strWhere = "[NameOfKeyField] = " & Me![NameOfKeyField]

HTH,
Paul

This discussion is closed to new replies.