Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting Query Based Reports to VBA based.

Converting Query Based Reports to VBA based.

Converting Query Based Reports to VBA based.

I am not entirely new to Access but I'm not sure how to start using VBA to produce reports.  

Right now all of my reports are products of queries; in many cases, cascading queries when not all of the required information can be captured in a single pass.

Can anyone give me a starting point for replicating what I am now doing with queries with VBA?  And, maybe more importantly, would this be adviseable?  I am assuming (always a dangerous path) that using VBA will give me more control over the process and a more elegant user interface (right?).

I've used DLookup some and a lot of SQL code in the VBA behind forms but just don't know where to start with reports.

Thanks in advance.

Larry De Laruelle

RE: Converting Query Based Reports to VBA based.

I use VBA extensively.
But I still prefer to use a query to run a report. Or in some cases a table.
I do however use subreports as many as 3 below to gather information from the main report.

In most case’s the criteria for the query/report comes from the form itself.
So if I have to make a report its usually based on something on a form that in turn is stuffing a criteria in a query which then shows up on the report.
Writing VBA code to create a report to me is defeating the purpose of the flexibility of Access.
Now that's not to say that on some reports I don't use a VBA function, which is in a module to enhance the report.

Keep in mind that even though a particular form is only involved with one table and the report you need has to gather data from more than one table. Its still easier to use a query so you can just add tables that relate to one another and have a very sophisticated report.


Ask me how Bar-codes can help you be more productive.

RE: Converting Query Based Reports to VBA based.

Thanks Doug, I appreciate the advice and will most likely follow it for most of my reports.  Just a couple of questions:

Some of the reports I do involve survey type data and I spend much time doing the copy and paste thing to set up an Excel spreadsheet to produce the charts and graphs.  I am working with anywhere from 3 to 12 or more fields which  have a value (usually a 5 to 1 Likert Scale) stored.

Is there a way to manipulate an Access Query to produce a crosstab-like table with the Scale values as the row headings and the field names as column headings with the number of responses for each rating value/rating topic?

I've been tinkering with this for a while and can't seem to get what I want using Totals and Crosstabs.  That's why I thought a VBA solution might be the way to go.

This is a small thing but one I think would improve my user interface:  Is it possible to display a message on a report in Print Preview that would instruct the user to click on the Printer Icon (or File; Print) if the want to print the report, etc?  

What I would really like is a button on the Report that says "Press to Print" with the appropriate On-Click event code - I haven't been able to get anything like that to work, however.  Unless you have a better suggestion, I've been thinking of putting a non-printing text box on the report with the desired message (doesn't seem like the most elegant solution though).

Thanks again for your help.

Larry De Laruelle

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close