How to Build Form/Report Dynamically

[Guest]

I am trying to make BOM report with recursive function that returns array of
elements of the BOM.
Each element in a record corresponding to a few controls in a form/report.
This way I can create a form/report that displays my BOM in Form/Report
Design view.
Here comes my problem: I need to run this as onOpen/onPage procedure of the
Form/Report. At the same time as I am switching from Design View to Normal
View onOpen/onPage event kicks in again, messing things up. How do I add
controls without switching to Design View? I have seen a lot of advices to
use preset control and switch their visibility. I am not sure how to
implement this method as I have no idea how many records my BOM may return.
It maybe 1 or 2 or 50 or more.
I would hate to create 100 controls to display 1 or 2 of them only.
Is there a solution for me?

y700

 

[Marshall Barton]

I am trying to make BOM report with recursive function that returns array of
elements of the BOM.
Each element in a record corresponding to a few controls in a form/report.
This way I can create a form/report that displays my BOM in Form/Report
Design view.
Here comes my problem: I need to run this as onOpen/onPage procedure of the
Form/Report. At the same time as I am switching from Design View to Normal
View onOpen/onPage event kicks in again, messing things up. How do I add
controls without switching to Design View? I have seen a lot of advices to
use preset control and switch their visibility. I am not sure how to
implement this method as I have no idea how many records my BOM may return.
It maybe 1 or 2 or 50 or more.
I would hate to create 100 controls to display 1 or 2 of them only.
Is there a solution for me?

You can't! And if you could, it would be a very bad idea
for many reasons.

For a form, you need to put the data in a table and bind the
form to the table. Even then, you may not get a decent
looking display.

For reports, precreate a sufficient number of invisible
controls on an unbound report. Then use the report's Open
event to initialize the array index:
arrayindex = 0

The Detail section's Format event can be used to process
each row in the array one at a time making the appropriate
controls visible/invisible and whatever else you want to do.
The last lines in the event procedure would be like:
Me.NextRecord = arrayindex < UBound(array)
arrayindex = arrayindex +1
to tell the report you are done with an array row and
whether there are more to process or not.

You can do quite a bit with an unbound report, but there are
several features that are useless.

 

[Guest]

Unfortunately, I have expected this answer.
So I did a mixed approch. I populated the 'temp' table and created report
bound to that table. It works.

Approach to pre-create hundreds of control to use just a few of them seams
like big waist of energy and resources, human and computer. That is why smart
people came with the concept of dynamic arrays. It is pity that Access does
not support such concepts as dynamic forms.

E.D.

 

[Tony Toews [MVP]]

Unfortunately, I have expected this answer.
So I did a mixed approch. I populated the 'temp' table and created report
bound to that table. It works.

Approach to pre-create hundreds of control to use just a few of them seams
like big waist of energy and resources, human and computer. That is why smart
people came with the concept of dynamic arrays. It is pity that Access does
not support such concepts as dynamic forms.

For your situation a continuous form would work well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/