Link to home
Create AccountLog in
Avatar of JVSNJ
JVSNJFlag for United States of America

asked on

MS Access Dynamic Reports

I have a union query that I want to make a report from by clicking on a button on a form.  It uses date columns in which how many date columns there are depends on many different dates are on the table that the union query accesses and that more or less date columns can be put for the union query if the dates are changed in the table.  The thing is when I add to the union query, I have to make a whole new report when the number of date columns decrease or increase but I want to make sure that the report for the union query automatically gets the right number of date columns as the amount of data increases or decreases in the query.  In short, I want a temporary or updateable report to be made with the click of a form button through vba code that displays the current data in the union query, not have to make a new report every time the union query's data changes.  Can this be done?  Please let me know and thank you.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

I generally use the Report Open event to open a copy of the recordset that will be used for the report, and change the captions of the labels base on the column headers in the actual query.

Take a look at the attached database.CrossTabReport.mdb
Avatar of JVSNJ


That looks fine but I want to be able to add or remove a date column depending on my union query when I don't know the minimum or maximum amount of dates I could have or it might be up to a year's worth with 365 days.  I was thinking of maybe dynamically recreating the report through VBA each time the query is updated.
1.  You cannot present up to 365 days on a report, at least not horizontally.  You are limited by the size of the paper.  So, figure out what the maximum number of columns you can present is and limit the report to that many columns.

2.  If you notice, in the example I provided, each of the columns and column headers is initially hidden.  You then use the code in the Open event to determine which of the columns to unhide.  That is your best bet.

3.  Forms and reports are limited in the number of controls that can be placed on them (over their lifetime).  I think the number is 255, but am not entirely certain of that.  If you create a single report, and try to use the CreateControl method to create controls on the report, it will, over time lose that ability.
Today is your lucky day.  I just finished an Access Archon article on this very topic (creating reports based on crosstab queries).  It hasn't been published yet, but here is a link for downloading the article and a sample database:

and here is a screen shot of one of the reports:
User generated image
This report can display eight days of data; it shows just six because two days were omitted since they were holidays.  The technique is to fill borderless text boxes with data as needed; the unused ones are invisible.  You need to consider several factors in creating such a report:  the maximum number of columns, the paper size, and the font size.
I don't think there is any way to display 365 columns of data on a report.  You may need to trim down your results to something more manageable.  One of the reports in my sample database shows 30 columns (the column headings are vertical), and that is probably about the limit, for letter landscape paper.

You could make a PivotTable instead, but you would have problems printing it.
How about a set of monthly reports?  That way the Day columns would be no more than 31, which is doable.  You could print them as a set from code.
Avatar of JVSNJ


I am working on this as volunteer job for someone and we actually talked about a possiblity for a 2-week order sheet with a max of 14 days.  So if there is a sample you can provide for reference, I would really appreciate it.  By the way, the zip file link above doesn't work.  It goes to a webpage that mentions "file not found," but thank you anyway.
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You should be able to modify one of the reports to show 14 days.  The two-week one shows 10 days.