Creating a report entirely through code

  • Thread starter Thread starter Marco Castro
  • Start date Start date
M

Marco Castro

I want to create a report that is really different from the usual ones. I
can't seem to even get close to getting what I want using the wizard and/or
messing around with the visual settings so I thought that I could do it all
from code. Unfortunately I'm getting a few different errors. I tried to
create a simple label to display on the report (dim test as label) but I got
an "Object variable or With block variable not set" when I tried to set its
properties. I then tried to declare it as "dim test as new label" but then
I get the error "ActiveX components can't create object". So how does one
go about doing this or is it not even possible?
 
I had that problem a long time ago, and finnaly decided to
create a label on the report itself.
Name-it something like: 'lblTitle' for example.

Then, in the 'On open' event of this report, you can
affect the desired string to-it.
lbltitle.Value="Test1"

Everytime this report is loaded, the "test1' will show
somewhere on your report.
You can also play around with its 'visible' property if
you need to...

Hope this helps,
PAtrick
 
Creating a report using the normal visual tools, is >way easier< than
creating one entirely through code. I think you'd be best advised to work
out how to do it normally. Perhaps ask some specific questions about the
problems you had, when you tried to do it normally.

HTH,
TC
 
The big problem with the report I want to create is that the columns should
be created by reading values in a table. I can get this done by using a
cross tab query but unfortunately the report won't update itself whenever I
add a new value (and hence a new column) to the table. That means the
report would have to be recreated every single time a new value is added. I
figured that if I did it all from code I could get around this problem but
I'm not having much luck so far.
 
Ok, I understand your problem.

I misunderstood what you meant by "creating the report at runtime". I
thought you meant, to actually create a new report, out of nothing at all,
using appropriate VBA statements (CreateReport, CreateControl etc.). But you
actually meant, to alter the content & layout of an existing report, at
runtime.

Here's how you might do it. Create a new report with 10 unbound textboxes in
a row. (Unbound means, make sure there is nothing in their controlsource
properties.) Call those textboxes txt1 through txt10. Put the name of your
crosstab query in the report's recordsource property (in the normal manner.)
Put the following code in the Report_Open event:

(UNTESTED)

dim db as database, qd as querydef, n as integer
set db = currentdb()
set qd = db.querydefs (me.recordsource)
for n = 1 to 10
if n <= qd.fields.count then
me("txt" & n).controlsource = qd.fields(n-1)
else
me("txt" & n).visible = false
endif
next
set qd = nothing
set db = nothing

That code will "bind" the textboxes to the current columns of the crosstab
query, whatever those might be. It assumes that there are 10 or less columns
in the crosstab query. If there are less than 10, it will make the extra
textboxes invisible. With a bit more code, you could reposition the boxes
horizontally, at runtime, to make them occupy the whole width of the report,
when there were less than 10 columns.

The above code is fairly shonky, but it might do what you want.

HTH,
TC
 
I just found a way to create a control at run time for both forms and
reports. Here is the code if you're interested.

Dim ctlLabel As Label

Set ctlLabel = CreateControl(InsertReportNameHere.Name, acLabel, , _
"", "", 0, 0)

That will create a label in a Form. If you want to create one for a Report
then use CreateReportControl.

You can also use DeleteControl and DeleteReportControl to clear the
controls.
 
Marco said:
I just found a way to create a control at run time for both forms and
reports. Here is the code if you're interested.

Dim ctlLabel As Label

Set ctlLabel = CreateControl(InsertReportNameHere.Name, acLabel, , _
"", "", 0, 0)

That will create a label in a Form. If you want to create one for a Report
then use CreateReportControl.

You can also use DeleteControl and DeleteReportControl to clear the
controls.
Click to expand...


PMFJI, but this is a terrible idea. You would not be
creating controls at runtime, you would be switching into
design time activities in the middle of a running
application. This will open the door for all of the things
that can go wrong during application design without the
safety of the developer's backup procedures, awareness of
corruptions and application bloat.

You should seriously reconsider using TC's approach.
 
There is a hard limit ( of 754 ) on the total number of controls you can
create on a form or report *over the lifetime of the form* . Deletes do not
reset this number, so you will soon run out of available controls.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Well, I'll stick my head on the chopping-block here, & say: there is no way
that will work!

You can not use CreateControl to create a new control >in the currently
running form or report<. You can only use it to create a new control in some
other< form or report that has been opened in design view using
Click to expand...
OpenForm/Report ... acDesign. I have even tried having a form close itself &
re-open itself in design view(!) Not event >that< will let you add a new
control to the currently running form.

So, have you actually used that code to successfully create a new control in
the >currently running< form or report? If so, please post the code in its
entirety, so I can check it independently. (I don't have Access on this PC.)
I could use that feature in rare cases. But as the others have said, it is
not< a technique that you would want to use on a regular basis.
Click to expand...

Cheers,
TC
 
Yes, just tried it on A2K2 and no, you cannot create a control on the
current form, from code *on that form*. But you can do it from code on
another form. Even more interesting is that you *can* do it many more than
750 or so times, provided you delete the control after each creation.

Try the following code, run from a command button on Form1:

Dim ctlLabel As Label
Dim k As Integer
Dim strLabel As String

DoCmd.OpenForm "Form2", acDesign

For k = 1 To 10000
Set ctlLabel = CreateControl("Form2", acLabel, acDetail, "", "", 1100, 100,
1200, 50)
strLabel = ctlLabel.Name
Set ctlLabel = Nothing

DeleteControl "Form2", strLabel

Next k

That runs to competion, creating and deleting 10,000 labels.
If you leave out the DeleteControl command, it runs 864 times.

Interesting exercise, but I will stick to making controls visible/invisible
as needed. Creating controls smacks a bit too much of self-modifying code,
and that leads to nightmares !

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Adrian said:
Yes, just tried it on A2K2 and no, you cannot create a control on the
current form, from code *on that form*. But you can do it from code on
another form. Even more interesting is that you *can* do it many more than
750 or so times, provided you delete the control after each creation.

Try the following code, run from a command button on Form1:

Dim ctlLabel As Label
Dim k As Integer
Dim strLabel As String

DoCmd.OpenForm "Form2", acDesign

For k = 1 To 10000
Set ctlLabel = CreateControl("Form2", acLabel, acDetail, "", "", 1100, 100,
1200, 50)
strLabel = ctlLabel.Name
Set ctlLabel = Nothing

DeleteControl "Form2", strLabel

Next k

That runs to competion, creating and deleting 10,000 labels.
If you leave out the DeleteControl command, it runs 864 times.
Click to expand...

Very Interesting!

Does your conclusion remain valid if you only create 100
controls,then try to switch the form to Form View for user
data entry? If it does, I guess you'd then have to switch
it back to design view so you could delete the controls
before closing? the form.

Ahh, who cares - since no one's going to do all that messing
around, it's just an academic exercise.

Interesting exercise, but I will stick to making controls visible/invisible
as needed. Creating controls smacks a bit too much of self-modifying code,
and that leads to nightmares !
Click to expand...

Good idea!
 
I'm not sure what you mean by "developer's backup procedures, awareness of
corruptions".

Are you worried that it could corrupt the data in the database? It
shouldn't be a problem in this case since I'm using this for creating a
report and not a form where they have to enter data in. I'm actually just
using labels for the entire thing.

I thought about using TC's approach but I found it to be to limiting. My
problem is that each of the columns in the report has to be read from a
database. So if I create 10 invisible labels for the records what happens
when they add a new column in and there aren't enough labels for each of the
columns? I know that it would be really easy to go in and add another label
but I'm the only one in the company that would know how to do that. If I
ever leave they would be out of luck. The way I've created it now they can
add as many columns as they want and it will always work.
 
You're right, you can't create a control while the form/report is running.
You must first switch it to design mode. I'm not even sure if it's possible
to do that from code. Either way in my case I just needed this capability
to do a weird report so I do all of the control deleting/creating inside of
a button on a form. Then once I'm done I just bring up the report for the
user to see.
 
Marco said:
I'm not sure what you mean by "developer's backup procedures, awareness of
corruptions".
Click to expand...

Maybe you don't do it, but most developers backup the front
end mdb file before making any significant change (including
modifying code modules, forms or reports. These are large
objects that require a lot of manipulations in the mdb file,
thus greatly increase the probability of corruption. When
you're working in the database making these kinds of
changes, you start to notice the little signs that some kind
of a corruption has started to occur (e.g. the compile
button doesn't gray out after a compile, a control starts to
behave in a funky way, etc). You are in then in a position
to take immediate remedial actions.

When you use code to make design changes in a running
application, users are unaware of these symptoms and will
thrash around trying to finish their job until the
application crashes and, more often than not, don't have a
recent backup to get them back up and running.

Are you worried that it could corrupt the data in the database? It
shouldn't be a problem in this case since I'm using this for creating a
report and not a form where they have to enter data in. I'm actually just
using labels for the entire thing.
Click to expand...

No, I'm worried about the module, form and report objects
being corrupted. Presuming that you follow good design
practices, your data is in a backend mdb file and should be
safe from this kind of issue.

I thought about using TC's approach but I found it to be to limiting. My
problem is that each of the columns in the report has to be read from a
database. So if I create 10 invisible labels for the records what happens
when they add a new column in and there aren't enough labels for each of the
columns? I know that it would be really easy to go in and add another label
but I'm the only one in the company that would know how to do that. If I
ever leave they would be out of luck. The way I've created it now they can
add as many columns as they want and it will always work.
Click to expand...

Not a good reason. If you think they might have 10 fields
in the report, precreate 40 or 60 invisible controls.
Generally, the width of the paper is a limit on how many
fields a report can reasonably display so I generally use
that as a guidline.

All of the things you can do after using CreateControl can
be done (and more easily) by setting control properties on
an existing control.
 
I do backup the front-end before making any changes. I never knew that
there was an official term and procedure for it tough. Copy and paste
always worked great for me. I'm not sure why you're fighting this issue so
much. In my case this is a very simple database, it doesn't have any
complicated procedures or modules anywhere in it. Apart from some out of
the ordinary reports everything in it was done using the built in wizards.
I'm not even sure why you would use access if you wanted to create something
more complicated, for anything like that we use mysql and build specific
applications for those databases with .net. It doesn't take that much
longer then trying to build it through access and since were not limited by
the functionality of access we can build a better interface for the user. I
would prefer it if we could do this for all of our databases but with
something as simple as what I'm working with right now it really wouldn't
make any sense. Still I'm not an access expert by any means and I've seen
people build allot of impressive databases with it. Just work with what you
know I guess.

Marshall Barton said:
Marco said:
I'm not sure what you mean by "developer's backup procedures, awareness of
corruptions".
Click to expand...

Maybe you don't do it, but most developers backup the front
end mdb file before making any significant change (including
modifying code modules, forms or reports. These are large
objects that require a lot of manipulations in the mdb file,
thus greatly increase the probability of corruption. When
you're working in the database making these kinds of
changes, you start to notice the little signs that some kind
of a corruption has started to occur (e.g. the compile
button doesn't gray out after a compile, a control starts to
behave in a funky way, etc). You are in then in a position
to take immediate remedial actions.

When you use code to make design changes in a running
application, users are unaware of these symptoms and will
thrash around trying to finish their job until the
application crashes and, more often than not, don't have a
recent backup to get them back up and running.

Are you worried that it could corrupt the data in the database? It
shouldn't be a problem in this case since I'm using this for creating a
report and not a form where they have to enter data in. I'm actually just
using labels for the entire thing.
Click to expand...

No, I'm worried about the module, form and report objects
being corrupted. Presuming that you follow good design
practices, your data is in a backend mdb file and should be
safe from this kind of issue.

I thought about using TC's approach but I found it to be to limiting. My
problem is that each of the columns in the report has to be read from a
database. So if I create 10 invisible labels for the records what happens
when they add a new column in and there aren't enough labels for each of the
columns? I know that it would be really easy to go in and add another label
but I'm the only one in the company that would know how to do that. If I
ever leave they would be out of luck. The way I've created it now they can
add as many columns as they want and it will always work.
Click to expand...

Not a good reason. If you think they might have 10 fields
in the report, precreate 40 or 60 invisible controls.
Generally, the width of the paper is a limit on how many
fields a report can reasonably display so I generally use
that as a guidline.

All of the things you can do after using CreateControl can
be done (and more easily) by setting control properties on
an existing control.
--
Marsh
MVP [MS Access]


Click to expand...
 
Marco said:
I do backup the front-end before making any changes. I never knew that
there was an official term and procedure for it tough. Copy and paste
always worked great for me.
Click to expand...

That's good, and Copy/paste is a convenient way to
copy/backup a file.

Other than "Good Practices", I don't think there is an
"official" term for individual styles of software design and
implementation (although some people have applied a name to
a specific set of techniques).

I'm not sure why you're fighting this issue so
much. In my case this is a very simple database, it doesn't have any
complicated procedures or modules anywhere in it.
Click to expand...

I'm not trying to fight anything here. I'm just trying to
pass on some of the things I've learned over the years.
You said yourself somewhere else in this thread that
self-modified code is not a good idea.

Apart from some out of
the ordinary reports everything in it was done using the built in wizards.
I'm not even sure why you would use access if you wanted to create something
more complicated, for anything like that we use mysql and build specific
applications for those databases with .net. It doesn't take that much
longer then trying to build it through access and since were not limited by
the functionality of access we can build a better interface for the user. I
would prefer it if we could do this for all of our databases but with
something as simple as what I'm working with right now it really wouldn't
make any sense. Still I'm not an access expert by any means and I've seen
people build allot of impressive databases with it. Just work with what you
know I guess.
Click to expand...

I guess it's just part of human nature to prefer the tools
you already know how to use.
 
Back
Top