Create Report from VBA Code 2

[Quintios]

I have a crosstab query that has different numbers of columns depending on one field in the query, called "Sequence". Depending on the value of Sequence (an integer), there could be anywhere from 3 to 15 columns.

I'm trying to create a report from VBA code using DAO to find out how many columns there will be, and then adding the code to add the extra fields in the Detail section of the module behind the Report as well as name the columns (they will be different in each report depending on the "Sequence&quot. There are a few "common" text fields that the report will have regardless of the value of sequence. Problem is, I get the error "You must be in Design view to create or modify controls." So I tried hard coding the control creation into a module, limiting myself to 20 columns. The code is starting to get pretty darn ugly, in my opinion, and I still think using this method I'll be unable to accomplish my goal.

I've been given other "solutions" to this problem which basically entails having columns that are hidden depending on the results of the query. This is fine when you run a report that is using, say, names of months or something that is already set. The wrinkle here is that the names of the columns change also; they are NOT the same from Sequence to Sequence, so having a standard report and then programmatically hiding columns will not work for me.

So I decided to come here and ask the question, how do I programmatically add controls to a report based on the information from a query in the setup I've described above? I'd post the code I have so far but there's a lot of it and I don't think it would help. If you know what I'm doing then I'm guessing you probably won't need to see it.

Here's the code behind the module when the Sequence needs three columns:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Set lblLabel1 = CreateReportControl(strReportName, acLabel, acDetail, , , 60, 60, 5100, 600)
lblLabel1.Name = "lblLabel1_Label"

Set ctlText1 = CreateReportControl(strReportName, acTextBox, acDetail, , "", Left:=(intDataX), Top:=intDataY)
ctlText1.Name = "ctlText1"

Set lblLabel2 = CreateReportControl(strReportName, acLabel, acDetail, , , 60, 60, 5100, 600)
lblLabel2.Name = "lblLabel2_Label"

Set ctlText2 = CreateReportControl(strReportName, acTextBox, acDetail, , "", Left:=(intDataX), Top:=intDataY)
ctlText2.Name = "ctlText2"

Set lblLabel3 = CreateReportControl(strReportName, acLabel, acDetail, , , 60, 60, 5100, 600)
lblLabel3.Name = "lblLabel3_Label"

Set ctlText3 = CreateReportControl(strReportName, acTextBox, acDetail, , "", Left:=(intDataX), Top:=intDataY)
ctlText3.Name = "ctlText3"
End Sub

That's where I get that error about being in Design View.

I would happily give all my points to anyone that can help me arrive at a solution to this. I left out the references to the values of the Text fields; that will be my next "thingy" to tackle, passing the information in the recordset from one module to the other. Of course, ignore the positional elements in the code as well, they change as I work on formatting.

To get the points I expect to be given enough information to be able to create a report that will have a variable number of columns. What would really be extra-nifty is if the report would be able to work based on grouping by "Sequence", such that based on the query behind the report when the Sequence number changed, the report would be able to switch the number of columns.

thx!

Q-

 

[dhookom]

You are working way too hard and heading off in the wrong direction. Consider the Crosstab Reports download at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

This crosstab report can handle any number of columns dynamically. It is much more efficient than making columns hidden or methods suggested in the Solutions.mdb.

If this works for you and you have good money to pay out of your pocket, consider:

http://www.tek-tips.com/supportus.cfm

or
Indianhead Special Olympics
2023 Fairfax St.
Eau Claire, WI 54701
(tell them Duane sent you)

If you have questions about the solution, come on back!

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Quintios]

I think if I can automate the 'number of columns' (easy to do with a recordset) value I might be able to leverage your code.

I looked at this before but dismissed it as not being usable in my situation. I don't remember exactly why so I'll review your code again.

Thanks! I'll get back to you.

thx!

Q-

 

[MichaelRed]

I assume the actual creation of the report object is not the issue, but the knowing the 'structure' (schema) of the XTab query, e.g. just counting the columns? If so just look into the columncount property if a recordset.fields.count property.

MichaelRed
m.red@att.net

 

[Quintios]

Michael,

No, I can count the columns easily simply using a record set based on that particular entry in the database. Based on the "Sequence" value I can count how many of these items are in that sequence and the columns can be set accordingly.

I'm having trouble with several things. The biggest issue right now is giving fields names, for example. Each label and text box must have a name, as well as a .Name property. The .Name is easy to set, however, I can't use code to create variables within a module, unless I create another module. So I have to physically set the module up with enough variable names to account for potentially 20 columns. Dim lblLabel1 as Label, Dim txtText1 as Text, and so forth. I created a Case If block to handle all 20 possibilities, creating labels and text boxes in the report to handle the columns.

I'm going in circles here. Lemme get a good night's sleep and start again tomorrow. I'll post the code.

Part of the problem I think is that when I designed the table structure I adhered to the rules as best I could, resulting in a lot of many-to-many relationships. I may have been better off simplifing the db by having two main tables with lots of empty fields, but a lot less tables. It was a big wide spreadsheet that contained about 18 common columns to each record, but then the subsequent 20 columns were specific to each record, so, according to the rules those needed to be split off into "subset tables". But I digress.

Sleep...

thx!

Q-

 

[dhookom]

Quintios,
Your biggest issue is why I created the crosstab report solution that I suggested. A little bit of code creates "aliases" for all your columns so you deal with the same aliases every time.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

Again, there are some examples of dynamically creating both forms and reports in these fora. I must admit that I', not at all sure that I fully understand the issue. You note that you can find the number of columns in the resulting (XTab) query, so likewise you should be able to get the field names, so creation of labels (and their caprions), text boxes to represent the fields and the fixed elements would seem to be quite easily done from that. Again, I am sure this is doable.


MichaelRed
m.red@att.net

 

[Quintios]

I haven't had a chance to work on the ideas posted here, but the issue I'm having is that I have to hard code in stuff and that limits the number of columns I can have.

Example, the number of columns comes back to be six, so I have to put up a total of 12 objects into my report in addition to the "standard" information that would go in there. So, in order to do that, I need to have Dim'd 12 variables. If I needed seven columns, I'd have to have 14 variables ready to go.

I could create a huge Case If type thingy where each case would hash through the creation and position of each text/label on the report.

I can reasonably say that no one would EVER need 20 columns, but, on the off chance that someone did, my database wouldn't have enough variables and the code would either break, or the report would be wrong.

I guess this is just going to require a lot of code and that's the way it has to be.

I really need to sit down with dhook's code and parse through it and see what's going on. At first glance I'm not seeing the "hook".

Thanks to both of you. Please keep this in your watch list. I'll post when either I get a solution, or I hit the wall again.

thx!

Q-

 

[dhookom]

Coding and flexibility is one of the strengths of my solution. There are 76 lines of code in a module for generating the report:
9 of these lines are blank (formatting)
11 are header comments
10 are for error handling
7 dim variables
33 are actual "code"
There is no code in the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

Have you actually searched these fora for "CreateReport" and / or "CreateForm"? The example posted (as far as I recall) does not require / use any arrays for control names -or any other purpose.


MichaelRed
m.red@att.net

 

[Quintios]

I apologize for not giving you guys more attention, but my primary job function is not a database administrator/programmer, and as this is a side project my main responsibilities sometimes take precedence.

As I explained before in a previous post

"I haven't had a chance to work on the ideas posted here"

and

"I really need to sit down with dhook's code and parse through it and see what's going on. At first glance I'm not seeing the "hook".

Thanks to both of you. Please keep this in your watch list. I'll post when either I get a solution, or I hit the wall again"

Thank you again for your help. I will post again when I've gone through this thread adequately.

thx!

Q-