Create Report COMPLETELY with VBA 2

[Quintios]

This thread describes a lot of what I need to do:

thread703-562204 I need to make the columns fill the page. From what I gather from that thread, a dynamic crosstab report will have empty space as the boxes collapse down across the unused text boxes (i.e. if you have all 12 months as columns, but the dynamic query only needs months 02-06, they'll collapse to the left leaving empty space on the right).

The crosstab report I'm running contains Text (I use First - Value) instead of a Sum or Average. Thus, I want the text boxes to be evenly justified across the page. This is easily done using the Design Wizard run against the crosstab query, however, I do not know of a way to run the Design Wizard several times within a report in order to obtain the different sets of columns needed to complete the report. Is there a way to do that? If not, I will need to create a report completely with VBA in order to have justified text boxes across the page for my dynamic crosstab query.

Does this make sense?

thx!

Q-

 

[smedvid]

Ok... I've done something similiar many moons ago under FoxPro. So here is one idea.

My solution was to create a Report table. This report table would contain the columnar data as it needed to print. The table layout contained a header id, page count, and total pages also. I predteremined that only x items would appear on a page vertically. I also created a Report Header table. This would contin the various headers needed as the report scrolled horizontially.

I don't have the details, and the code is long gone... htwh,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Quintios]

But how would you programmatically set the width of the headers so that they were evenly spaced across a page? This isn't something I can do in advance as the number of column headers is going to change.

thx!

Q-

 

[smedvid]

In my case, I had a variable number of columns for headers. It was based on a couple of things. Remember I had 2 tables.

I would pre-populate the Header_Info table. I knew how many columns were the maximum per page. So, I had (2) tables. One header table and then the report table. Within the report table I had a Header_ID that linked to the Header table.

My approach was to take what the report would look like in an Excel spreadsheet and create table(s) in MS Access that can be used to generate the same cross-tab report.

I will see if I can find the code, it was a long time ago. And it did take lots of code as I recall.





Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[MichaelRed]

If the basis (RecordSource) for the report is an XTab, then hte number of Columns will normally vary, but they CAN be fixed.

XTab queries have a "ColumnHeaders" property, which you CAN populate at design time. ALL the columns named here WILL apprar in the results, and NO other columns will be seen.

You can (alternatively) create another recordset (refer to as "rsCols" herein) containing the desired colunms and use rsCols as the outter join to the field with the columns in the XTAB and use the rsCols in the results set of the query.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

Also, not that doing the report in VBA has been discussed in these fora in the past, complete with an (simpllistic) example.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Quintios]

Either I don't understand your responses, or I'm not getting across what I want to do... *grin*

I'll simplify it a bit because your response to this will allow me to use it in all instances.

I have a query that handles the recordsource for the report perfectly. It's exactly what I need, however, each time I run that query it's going to change the number of columns. How do I use VBA to create a report, create headers, make text boxes, position the text boxes, set the size of the text boxes, position the records on the report, set the page header and footer, set the font, etc. etc. etc.

What VBA commands are there for setting up a report? What resources are available to give me the language syntax for VBA that can be used in the manner I described above?

thx!

Q-

 

[MichaelRed]

Hopefully, I'm not THAT far out of context.


Resources:

The ubiquitous {F1} (a.k.a HELP: Topic ==> CreateReport)

These fora: Advanced Search: KeyWord "CreateReport" (and / or "CreateForm&quot

Some reasonably sophisticated text / tutorial on Ms. A. / VBA.

A bit of thoughtful consideration.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Quintios]

Yup, you hit the nail on the head there Mike (may I call you mike?)

I found this on ExpertsExchange:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210594

that basically mirrors what you suggested.

Also on EE, somone suggested creating a template for the report which is an excellent idea. That'll save some programming and compile time.

I've been working on and off (mostly off) this pet project of mine for 2 years, this is like, the last problem that needs to get solved in order for other people to be able to use it (it's a single-user development tool). If you track down my posts you'll see that most of them head in the same direction. As I learn more I'm able to better word my questions. This dynamic query thing is killing me but I think I'm going to be able to solve it. If only Access would switch from Portrait to Landscape for one of my subreports... *grin*

Thanks Michael.

thx!

Q-

 

[MichaelRed]

Is the EE example more complete than the sample posted here on Tek-Tips?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Quintios]

Not at all. Basically they told the original poster not to do it, that it would be more work than it's worth.

I think I'm basically trying to write my own wizard here. Not on purpose, but I think the project is going to call for it.

OK, I've got the little bit of code to create a report. Now, how do I add a text box to the report? I realize that you can modify the properties of the text box to indiate the recordsource, the position, font, and other stuff. What I don't know how to do is actually add it to the report itself.

Any suggestions?

The help section on CreatForm is helping a little bit as well, but it only goes so far.

I'm going to start hitting my books to see if something is there on this topic. Any hints, as always, are appreciated.

thx!

Q-

 

[Quintios]

OK, here's more hints:

thread703-716261

thx!

Q-