How to generate a report based on a Query

[aholts]

Hello,

I am having a challenge attempting to automate generated reports in access from a query. I currently have the ability to receive a user input which creates a query of one specific record based on that records number. I take that same user input value to also generate a report for that value. More specifically the user inputs a integer value and clicks a search button, My query "qry_searchrecords" is activated and has a user defined function called GetRecID and I pass the value too the point where I can generate a report called Rpt_View_Report.

What I would like to do is bypass the user input and get values directly from the main table where all the data is stored. Because I would eventually like to give the user the ability to export all the reports but separately or create other queries and export those reports separately.

Please help with any ideas or suggestions!

Thank you!

 

[ranman256]

You will need a separate query.. for everything (all data without the filters)
Using your existing report...bind it to the query for All data.

If the user filtered version uses a form for its filtering, then FILTER the ALL report like: docmd.openreport rptALL,,,filter

But if the report uses parameters , then you will have to have 2 reports (save As )
the user version
and the All version ,without params.

 

[GinaWhipp]

This can be easily modified to produce a report based on selected criteria, as well as, showing all if no criteria is selected using one report...
Microsoft Access tips: Search criteria

You can also add an Export to Excel button using something like this...
Code Snippets | BTAB Development

 

[aholts]

Thank you for your responses you are right I think I will have to develop something that utilizes user input. This is where I eventually would have to go anyways I was just hoping that I could test my code without building a small form but I can do that no problem. Thank you for your suggestions and reference links!

 

[GinaWhipp]

Glad to help! If you get stuck just post back...

 

[aholts]

Ok The following is what I have been able to come up with so far.....
I was able to successfully bypass using a button click to get the single record query that I am looking for so I can generate a report, However I am stuck where I can't actually get the report based on the query that is generated.

 ' I use this function as the "criteria" in a query that I created called Copy Of qsel_searchrecords (This is just a test query) it takes the first record from the main table and puts it in the query. 
' I am trying to use the results that I get from that query and generate one report based on that query result. I then plan to loop this routine through the main table. 
'My Question is How can I generate the report based on the qry result or how to copy the qry result to another query so I can use that data for the report???

Public Function GetRecPomID2() As Integer
Dim strdata As Variant
Dim pomIDval As Integer
Dim POMID As Field
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb()
Set rs = db.OpenRecordset("Tbl_MainRRD_Data", dbOpenTable)
Set POMID = rs.Fields("POMID")

'Once this block is finished the Query is created with the first record from the Main table
pomIDval = POMID
strdata = pomIDval
GetRecPomID2 = strdata 
'Debug.Print strdata

'I attempted to use a SQL statement to simply copy from the query result to the new query to generate the report from however I am not sure how to access my query result to copy from. I assumed it was already open but apparently not since I get an error on this line.
DoCmd.RunSQL "SELECT* INTO Copy Of Qry_RRD_Modify_Report_Data FROM Copy Of qsel_searchrecords;"

'I was hoping to open the report here, where the reports record source could either be the initial query result (Copy Of qsel_searchrecords) or the query which data was copied into (Copy Of Qry_RRD_Modify_Report_Data)
DoCmd.OpenReport "Copy Of Rpt_ViewMod_Reports", acViewPreview

End Function

I hope I provided enough commentary to be clear on what I am trying to accomplish. Thank you for your help in advance!

 

[GinaWhipp]

A report cannot access a query unless it is saved. So, the only way I can think this will work is to give your Report a Record Source and then save your query to the exact same name as that Record SOurce and then the report should run.