Converting Query Based Reports to VBA based.

[LarryDeLaruelle]

I am not entirely new to Access but I'm not sure how to start using VBA to produce reports.&nbsp;&nbsp;<br><br>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.<br><br>Can anyone give me a starting point for replicating what I am now doing with queries with VBA?&nbsp;&nbsp;And, maybe more importantly, would this be adviseable?&nbsp;&nbsp;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?).<br><br>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.<br><br>Thanks in advance.<br> <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[DougP]

I use VBA extensively.<br>But I still prefer to use a query to run a report. Or in some cases a table.<br>I do however use subreports as many as 3 below to gather information from the main report.<br><br>In most case’s the criteria for the query/report comes from the form itself.<br>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.<br>Writing VBA code to create a report to me is defeating the purpose of the flexibility of Access.<br>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.<br><br>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.<br><br><br><br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[LarryDeLaruelle]

Thanks Doug, I appreciate the advice and will most likely follow it for most of my reports.&nbsp;&nbsp;Just a couple of questions:<br><br>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.&nbsp;&nbsp;I am working with anywhere from 3 to 12 or more fields which&nbsp;&nbsp;have a value (usually a 5 to 1 Likert Scale) stored.<br><br>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? <br><br>I've been tinkering with this for a while and can't seem to get what I want using Totals and Crosstabs.&nbsp;&nbsp;That's why I thought a VBA solution might be the way to go.<br><br>This is a small thing but one I think would improve my user interface:&nbsp;&nbsp;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?&nbsp;&nbsp;<br><br>What I would really like is a button on the Report that says &quot;Press to Print&quot; with the appropriate On-Click event code - I haven't been able to get anything like that to work, however.&nbsp;&nbsp;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).<br><br>Thanks again for your help. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>