open report with record from form

[muppetmad]

I hae a button on my form to open a report. However, I get all the records in the database when I only want to see the report with the current record from the form on it. How do I narrow my report?

 

[trucktime]

Current

Check this link:

http://www.access-programmers.co.uk/forums/showthread.php?t=87064&highlight=print+current+record

Or search the Forum, this is a frequently asked question.

 

[godofhell]

You could also set the Report Record source to be the same query used for the form.

 

[Tim L]

You could also set the Report Record source to be the same query used for the form.

godofhell, this will still return the whole set if more than one record is produced by the query.

Muppetmad, you could use a modified version of the original query for the report though; by using a field that identifies the displayed record uniquely and adding a reference to the field (or the control on the form used for that field) in the query criteria for the field. The code to put in to the query would be along the lines of:

Forms![[B]formName[/B]]![[B]fieldName[/B]]

Replacing formName and fieldName accordingly. You can use the 'build' wizard in the query builder to help you get the reference correct.

Tim

 

[MarkK]

The DoCmd object allows you to specify a filter for the report when you open it. Use the ID field of the current record in the form, which might be called dataID.

DoCmd.OpenReport strReportName, acViewPreview, , [COLOR=DarkRed]"dataID = " & Me.dataID[/COLOR]

The item in red is an SQL WHERE clause without the word WHERE, and filters the data appearing in the report.

 

[godofhell]

Tim, you are correct if the results of the query include multiple records, they will also output.

MuppetMad, to answer your question simply write the statement below on your on click event of the button.
DoCmd.OpenReport "ReportName", acViewPreview, , "dataID = " & "'" & Me.dataID & "'"

LagBolt, your statement would ask for the Name entered on the textbox since you missed the SQL ' identifiers.
The correct statement is:
DoCmd.OpenReport "ReportName", acViewPreview, , "dataID = " & "'" & Me.dataID & "'"

Not: This statment will prompt you for the Name you entered on the text box.
DoCmd.OpenReport strReportName, acViewPreview, , "dataID = " & Me.dataID

 

[Rich]

LagBolt, your statement would ask for the Name entered on the textbox since you missed the SQL ' identifiers.
The correct statement is:
DoCmd.OpenReport "ReportName", acViewPreview, , "dataID = " & "'" & Me.dataID & "'"

Not: This statment will prompt you for the Name you entered on the text box.
DoCmd.OpenReport strReportName, acViewPreview, , "dataID = " & Me.dataID

Lagbolt is not incorrect per se, it depends on the data type in the textbox

 

[MarkK]

Guru?

 

[muppetmad]

Well, several of these methods worked. However, when I compact & repiar my database or run code to turn off the shift key on startup. It stops drawing the field information from the form to go on the report.

muppetmad

 

[asgard]

Well, several of these methods worked. However, when I compact & repiar my database or run code to turn off the shift key on startup. It stops drawing the field information from the form to go on the report.

muppetmad

I'm trying to fix this too but these methods aren't helping

 

[secy]

This is really worked


Sample:

DoCmd.OpenReport "REPORT NAME", acViewPreview, , "[PRIMARY KEY] = " & Me.PRIMARY_KEY



Use:
DoCmd.OpenReport "Purchase Order", acViewPreview, , "[Order Number]=" & Me.Order_Number


But I could not found [Order_number] in any form or query?