Create Report from VBA Code

[Matt Greer]

I'm at the point where I'd pay someone good money out of
my own pocket to make this work. -_- 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"). 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.

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.

 

[Duane Hookom]

You really can't create a control unless you are in design mode. You can
open your report in design mode and then use code to add text boxes and
labels to your report. None of this would happen in the Detail_Print event.
You would need to use:
DoCmd.OpenReport "rptYourReport" , acViewDesign
Keep in mind that a report can have only a limited number of controls over
its life-time. Compacting or creating new reports might avoid this issue.

You already know my opinion on the best solution.