Solved

Looking for example of creating report in Access with VBA

Posted on 2008-10-30
25
1,154 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:Rich
  • 13
  • 12
25 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22847689
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

0
 

Author Comment

by:Rich
ID: 22849202
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.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22850698
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
0
 

Author Comment

by:Rich
ID: 22851362
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.
0
 

Author Comment

by:Rich
ID: 22851474
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
0
 

Author Comment

by:Rich
ID: 22851487
Please ignore report.gif in the previous post - it is obsolete.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22853482
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
0
 

Author Comment

by:Rich
ID: 22853666
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22854154
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  
0
 

Author Comment

by:Rich
ID: 22854223
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22855071
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22855575
I get this message when I open either of the two reports.
100.JPG
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Rich
ID: 22862361
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22866085
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
0
 

Author Comment

by:Rich
ID: 22866665
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22873829
OK, report is open.

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

JeffCoachman
0
 

Author Comment

by:Rich
ID: 22875795
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22882624
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: (http://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
0
 

Author Comment

by:Rich
ID: 22885466
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22889792
OK,
How would this look?
All data on 1 row, or each year on separate rows?

JeffCoachman
0
 

Author Comment

by:Rich
ID: 22894745
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22895592
OK

Will post something later today
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22900004
Try this

Examine it *carefully*

You should be able to adapt it to work in your database


JeffCoachman
Access-EEQ23864128CompareCurrent.mdb
0
 

Author Closing Comment

by:Rich
ID: 31511932
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22915701
Great,

Thanks to you as well,

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

JeffCoachman
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now