Run a report using data in an open form

[jgarcia187]

I have a report that runs based on parameters set in the underlying query. For example, in the "account number" field in the query I've entered [enter account number] in the criteria area. This allows the user to select a certain record. The record opens with all relevant data on a form. From the form, the user can then run specific reports about that record that also use the same [enter account number] criteria. I know there is a way to have access copy the account number from the open form and use it to run the report but I can't figure out the macro. Please help...

[ajalwaysus]

You make this code the record source of the report and then execute the report from the form.

SELECT * 
FROM Table1
WHERE Table1.Field1 = Forms![FormName]!FieldName

-AJ

[jgarcia187]

Interesting. That works but then I lose all the other data fields that are shared between the table record and the report. Do I have to write the same code for every shared control on the report?

[ajalwaysus]

If I understand you, then you need to write code like this:

SELECT * 
FROM Table1
WHERE Table1.Field1 = Forms![FormName]!FieldName1
AND Table1.Field2 = Forms![FormName]!FieldName2
AND Table1.Field3 = Forms![FormName]!FieldName3
 

Let me know if this is what you were after, otherwise please post a more specific explanation of what you need to do.

-AJ

[jgarcia187]

I’m using reports to build documents. My goal is to allow the user to only enter the record specific information once and then print all documents (separate reports) necessary. The user will enter a form and input the common field one time. That form will have all available documents on it as buttons. When the user presses a button, I need Access to use the data on the open form as criteria to open the report and display all data fields available in the record.

When I use the code you suggested, only the one field is selected, not the entire record.

For example. In the table let's say there are 24 fields. On the form, the user will enter data for one field "account number." Based on the report selected, Acess should use the account number field to go to the table and pull 12 fields of the 24 into the report.

I could make the report's record source a query where the criteria is [enter account number] but then the user has to enter the account number for every report they run...tedious.

I tried both ways you suggested but can't get it to pull in the entire record.

Clearly I’m not a professional…ju st a recreational user. Not sure if I’m explaining my problem correctly.

[ajalwaysus]

I don't think I still understand what the problem is. If you pass the ID of a desired record to your report, your report should be able to then run off that single ID and pull back all information in the table if you are using an SQL statement as you record source. If you have multiple reports that use that ID then it should be no different.

If possible, could you remove all sensitive data from your tables then upload the mdb in a zip file to this forum, perhaps going straight to the source would help me understand your issue.

-AJ