Report Questions and basics

[miken5678]

I didnt get much of a response last time so i figured i will try to restate this.

I have one main table with 20 other tables that are linked depending on a user selection.

I will basically have 30+ reports one to two per table based on a user selection from the main table. ie who the data entry person selects the report to go to.

I have seen some of boblarsons examples but for someone with little vba knowledge is there a preferred way to group do a ton of reports?

My reports are going to be done weekly so i assumed i would need xx number of queries from which to link the reports to. However I am stuck other than making the query have a [] variable for the start and end dates of the week. I would love to take two variables and use them for all reports but that most likely puts it into extreme vba coding. I have seen some of boblarsons report examples but they appear to have a ton of vba coding behind the scenes.

The next question i have is when the reports come out i will have a blank area for them to enter text/response in. My main concern is what is the best output format to allow ease of data entry for another person that would maintain some sort of formatting from the reports? I realize pdf is the ideal choice but unless you have the pro version you cannot edit. I have yet to use word but heard some formatting does not carry over. Is excel the best choice for formatting items? If it is how does excel seperate each record from the report.

I have about 4 days to get something out quickly and then the report will be run on every friday so something easy to do without much vba is the preferred route.

Any help is appreciated

 

[miken5678]

can anyone point me in the right direction for producing multiple reports with a date variable?

 

[speakers_86]

A couple of things here. First you seem like you want to stay away from vba. Thats fine, but with 20 tables and 30 reports, I find it hard to believe that you are so uncomfortable with vba. Also, you mentioned how many tables and reports you have, does that mean you have no queries? If so, then you must be doing something wrong.

As for the question about reports, reports are not intended for data entry. That is what a form is for. Maybe you can enter data in a report, but in my limited experience, I have not come across this.

You asked if pdf was good for formatting. In a word, no. The whole idea of pdf is to prevent changes, not encourage them.

Now your original question. You want to enter your report parameters one time and pass those parameters on to multiple reports, right? Im sure it can be done, but I dont know how. I would try to google that for the answer.

 

[miken5678]

there is limited vba that runs the current forms but I have yet to use vba to do any sort of reporting.

My goal is to just have a format where the reviewer can put comments in the report. These comments dont need to be saved to the db but are rather for the reviewers notes that can be saved to their pc.

I realize i will have to make a query for each report however on some of the report samples that i have seen there is a bit of vba coding to bring up a query and input date or name variables based on what you put in the form and what report you select. Then the report is automatically run. I did find a few examples but many were way beyond my limited vba experience.

I guess it is safe to assume that excel would be the top choice then word when it comes to reports to allow some blank edit boxes for comments(without taking into considering excels size limitation).

 

[speakers_86]

I dont know why, but the first thing I thought of when I woke up this morning was how to do this. I think the answer can be found in a sample database here on this site. There is one that shows how to make a custom parameter prompt. Find that, then look at the on click event of the submit button. The vba should pass the parameters on to the report. All you will have to do is repeat that line of code for whatever number of reports you need. Good luck!

 

[miken5678]

i think i follow what you are saying but let me see if this is along the same lines that i am thinking

I ended up starting my reports today just running a query for the data i need and putting in a >= [start date] and <= [end date] in the date criteria field. Each time the report is run the prompt pops up. I take it what you are talking about somehow links a form/module to the date criteria within a query?


I saw one that boblarson did and was hoping he would chime in. It was a pop up form that you made a selection and picked a report. From there it would fill the criteria and run the selected report. To add new reports you just saved the reports as a rpt file but i have as yet figured out how to change vba or have enough experience in it to tweak it. Mainly because on some of the reports they will have an extra criteria than the others. Kinda of an if the item is selected. Thanks for point me in the right direction. I will be looking.

 

[SJCYork]

Hi

First post so hopefully this comes through ok.

If I understand you right you want to be able to run many reports effectively using the same paramters (ie Start and End Date) without having to constantly fill in the parameter pop-ups?

For this I usually create a Menu form with a button for each report. 2 x fields for Start Date and End Date. And then in the data tab of your report set the parameters to your form field values:

@Sdate=forms!frmReports!SDate, @Edate=forms!frmReports!eDate

I only write my databases using SQL backend and havent used Access as a neckend for a long time so not sure if this would work the same for Access.

Hope I have understood your problem correctly!

 

[miken5678]

That is exactly the idea i had. Some of the tables for the many reports are different but the table from which the main start date and end date are criteria against are within every report. I just did not know how to link a field to this criteria like i can do easily in excel when i write a query in there as it is super easy to link it to a given sell in the spreadsheet.

 

[miken5678]

ok, i just tried to export to excel and it doesnt come out like anything i expected. It is even missing data that is within the report itself. (and there are no subforms) I did run it within word and it preserves some of the formatting but it does not show blank fields or allow for easy commenting after the fact..

Anyone point me in the right direction on this.?

 

[miken5678]

it looks like word doesnt work and allow for an easy format for users to edit.

I have sense gone to excel and frozen the panels so that i get the policy number and column headings fixed as you scroll. It is much easier to have someone edit 115 lines in excel than 115 pages in word.

Still looking on a way to link or use a universal set of criteria for start/end date for one field across multiple qry/reports without having to key it in each time.

Also is it worth exporting reports to excel vs just exporting the qry results to excel? I guess the only difference is the ability to somewhat name the fields in the report for the column heads to end up in excel correctly