Link to home
Start Free TrialLog in
Avatar of Rich
RichFlag for United States of America

asked on

Looking for example of creating report in Access with VBA

I am looking for a sample of creating a report and populating the detail record(s) fields using VBA. I have to gather date using several queries and then summarize to build the report, and doing so programatically seems to be the only option (the standard grouping features, etc. with the access reports don't cut it). I understand how to reference the report unbound fields, but am not sure how to reference repeating fields in the details and other group levels.

I have the initial report structure built in access and am not sure if I can use that as a starting point or I will have to create it from scratch with code.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

gagnonconsulting,

  "I have to gather date using several queries and then summarize to build the report, and doing so programmatically seems to be the only option"
I have never run into a situation (using several queries and then summarize) where I could not create the report in design view or using the wizard.

Is this an issue with summarizing the data? (and not really a report creation issue?)

If so, there are, SubReports, Totals (Group By), and Crosstab queries, ...ect,  that will summarize the data before it goes into the Report.

Obviously, you can summarize complex data in the report as well.


  "the standard grouping features, etc. with the access reports don't cut it"
Can you explain this, by providing a concrete example?

Again, I have only had a few instances where I just could not create the Report with Access.
(And in those instances, it was probably because I just missed something simple, or did not know how to manipulate the report in the correct way)
Besides, the same options you have access to via code, are the same ones you have with the Report wizard or in design view.
So what would you do differently in code that you could not do with the wizard or in design view?

Lastly creating reports in code is a not an easy task:
1. There is a whole other set of VBA commands you must familiarize yourself with (InSelection, CreateControl, ...ect)
2. You will run into the 700 object limit in Access reports because every time you "create" the Report, this number will increase.

So, ... can you explain, in detail, what it is you are trying to accomplish, that you feel you can only do by using code?
;-)

JeffCoachman

Avatar of Rich

ASKER

basically, I have data from a bunch of stores on a bunch of dates. The stores are grouped by district. The report needs to gather data from between a start and end date and summarize the total sales amount (and other values) across that date range, and then in the next row, get the same data from the previous year's same date range, and display it. Then, it needs to display the difference between the current and previous years data, and the percent change. This needs to happen for each store, and then the same calculated difference and percent change for each district (across several stores) and then again for all stores.

gagnonconsulting,

Obviously we would need to see some of this:
    "data from a bunch of stores on a bunch of dates"
...agreed?
;-)

So how far have you gotten on this report yourself?
Or are you asking for help in building the entire Reoprt?

JeffCoachman
Avatar of Rich

ASKER

Well, I have been able to get most of it working by using the onFormat of the Print Preview and some global variables to summarize the data in VBA code. I had to make the assumption that there would only be one record per store on the requested data and zero or one record for the previous year for the same store. The onFormat of the various grouping levels is where I calculated the differences and percent change and filled in the appropriate report variables, but when I got to the final, total stores summary, found that the onFormat events were being called twice (apparently Access loops through the report data twice when being created). Anyway, that could have been solved, but when I went to add a date range, my (static) logic of one record per store was out the window.

I can attach an image of the report in the current state, but it only works for the single date, and still has double sized Network totals.
Avatar of Rich

ASKER

The biggest reasong that I don't believe that the standard report grouping summarization features do not work is that I need the difference from current to previous year, and the percent difference. I understand how to do the sum, avg, and other calculation for each group, but this assumes that the data is consistent within the group.

To simplify, I have a table with storeID, date, and amount. The storeID is in a separate table with the location and district. I need to provide a start and end date as input to the report. When the report is run, I need to have the amount (summed from rows between the start and end date) from a store on the first row and the amount from the same store and date range, but from the previous year in the next row. Then I need a calculated row showing the difference between the current and past year. This should be repeated for all stores, grouped by district. There should be similar summaries at the end of each district, and then a grand summary over all districts.

To make it even more complicated, a store opening date will be later added, and I will  need additional grand summary info to see the difference between stores less than a year old and the older store.  
report.gif
Avatar of Rich

ASKER

Please ignore report.gif in the previous post - it is obsolete.
gagnonconsulting,

If the data is properly structured, you can do anything you want in a Report.
I personally have created much more complex reports than what you are requesing here, without using code.

How much time and frustration you want to spend doing it, is another story.

Again, the same tools available in VBA are exactly the same in design view, (or with the wizard to some extent)
So again, I am confused as to how using VBA code will make creating the report easier?
In fact learning to use VBA to do this, will take quite a while.

Can you provide a sample database, so I don't have to guess at the structures of the tables?

Thanks

JeffCoachman
Avatar of Rich

ASKER

I am sure that you are correct, but I am pretty new with access. I was unable to upload the accdb file, but have saved it as an mdb file (huge size difference, by the way!)

The report in question is the bottom button (View Report) of the startup form, and a useful start date is 10/10/2008. Keep in mind that I want to be able to add and end date and see data within this range.

Thanks for looking at this.

Rich
SF-Sales-rev3.mdb
gagnonconsulting,

Thanks, I will look at this tonight.

By the way, you can upload a 2007 dadatbase by simply appending a ".mdb" to the file name:
For example, rename this:
    SalesContacts.accdb
... to this:
    SalesContacts.accdb.mdb

JeffCoachman  
Avatar of Rich

ASKER

I hadn't thought of that. I will keep that in mind. However, it is 20 MB versus 1 MB. Don't know what they added to the file!
Make sure you run the compact/repair utility, before posting.

Also create a db just for posting and delete anything that is un-needed.

JeffCoachman
I get this message when I open either of the two reports.
100.JPG
Avatar of Rich

ASKER

I have seen that to when I open the report directly. When I open the report through the statup form using
     DoCmd.OpenReport "Store Sales for Week", acViewPreview
it works correctly. I believe that it is because [Forms]![Startup]![StartDateToView] is not set, which is used as the date for the report ("Store Sales Report for Date" query). Since [StartDateToView] it is not defined as a Parameter Query at the top of the SQL, then it doesn't ask for it, and simply gives the error.
OK,

But it is not clear which report is opening from the Startup form.
Even when I try I have nabsolutely no Idea what date to provide.
When I type: 10/1/2008 I get an error.

Can you please post a sample that I can simply open and see the issue/data?

JeffCoachman
100.JPG
Avatar of Rich

ASKER

The sample that I am posting is my actual code, and it works for me. I have not seen the error that you are getting (see last paragraph for update on this). I enter 10/10/2008 into the Start Date, press the View Report button, and get a print preview of the report. If you look at the properties of the startup form, particularly the View Report Button, you will see the onClick event calls the following VBC procedure PreviewStoreData_Click() - see attached Code Snippet. That procuder calls
DoCmd.OpenReport "Store Sales for Week", acViewPreview
which opens the report in preview mode. The onFormat events of the report, and some of its headers and the detail section capture and calculate the data and print summaries on the report.

I just tried 11/3/2008, the date you entered, and I do see that error. I believe that it must be due to the fact that there is no data for that date. That is an error condiition I have not specifically checked for, but obviously, will have to deal with.
Private Sub PreviewStoreData_Click()
    If IsNull(Me.StartDateToView) Then
        MsgBox "You must choose a Start Date"
        Me.StartDateToView.SetFocus
        Exit Sub
    End If
'--    If IsNull(Me.EndDateToView) Then
'--        Me.EndDateToView = Me.StartDateToView
'--    End If
        
    DoCmd.OpenReport "Store Sales for Week", acViewPreview
    DoCmd.Close acForm, "Startup"
End Sub

Open in new window

OK, report is open.

To keep this question focused, what is the most pressing issue for this report?

JeffCoachman
Avatar of Rich

ASKER

My original question was how to create reports programatically - looking for an example. I have searched the texts and online, but have not found any examples of creating reports other than using the layout tools in Access.

I have explained in my first several comments the requirements of the report, and you have commented that I should be able to do it without VBA. Bottom line, I need to modify this report (or use a different tactic in the first place) to include a start and end date, and collect data from the current year (based on the start date) and display it (sum of total amount for the date range), and then display the same sum of total amount from the previous year, same date range. Then display a row showing the amount change and percent change. Since I don't know how to do this using the Access Report tools, I can probably figure it out programatically using various recordsets, but I am not sure how to create the report using VBA.
Again, there is nothing you can do by code that will make any of this any easire.
This is why there is not that much info available on this.
No developers do it.

So, if that is truley your ultimate question, then we are at an impasse.

Your requirements:
1. I need to modify this report (or use a different tactic in the first place) to include a start and end date
2. Collect data from the current year (based on the start date)
3. Display it (sum of total amount for the date range),
4. Display the same sum of total amount from the previous year, same date range.
5. display a row showing the amount change and percent change.

This is actually 5 separate questions, not one.
You are only allowed 1 question per post.
See here: (https://www.experts-exchange.com/help.jsp#hi23)

So is there One of these Five topics we can focus on here or in this question?
Or is this an all-or-nothing proposition?

JeffCoachman
Avatar of Rich

ASKER

Jeff,

If I have to boil this down to one single quesiton, then your #5 above would be the closest, and specifically stated for my purposes "How does one display a row in a report that is the result of a calculation using data from multiple, separate, specific rows of a query?" The specific calculations that I need are the amount and percent change from the sum of rows between one date range and the sum of rows from that same date range in the previous year.

I know the algebra required for the calculation.

I am not asking anyone to do my work for me, just looking for examples of how to do this type of report, in whatever methodolgoy is most appropriate.
OK,
How would this look?
All data on 1 row, or each year on separate rows?

JeffCoachman
Avatar of Rich

ASKER

I need the first row to have the data from the requested date range (summed totals if it consisits of more than one row) and the next row have the corresponding data over the same date range - 1 year, and then below these two rows, the difference and percent change from the between the two preceeding rows.
OK

Will post something later today
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rich

ASKER

Excellent! That is exactly what I was looking for! I will use your as an example to get my code working in a similar fashion. The SQL you provided is really the part that brings it all together for me. I really appreciate the help.

Thank you very much.
Great,

Thanks to you as well,

Once I figured out what you needed, is was quite an interesting project.
;-)

JeffCoachman