CreateControl: Proper Use & Beyond

[mbreu996]

Scenario: Need a dynamic data entry form. The user generates this form by specifying which parameters (field names) will be on it. In one case it may be only 1 field, in another case it could be 25 fields. The parent form is static, these fields are only found on the subforms.

I have been able to make this entry form using a number of steps, one of which is adding the controls (textboxes) to the subforms. I noticed my form generation routine was taking a very long time to execute, and I isolated the createcontrol step as the "slowest".

Take the simplified example below.

1)Subform On Load Event
Private Sub Form_Load()
testaddcontrols_v2
End Sub

2)Dummy Array
Public Sub testaddcontrols_v2()
Dim ary(2) As String
ary(0) = "a"
ary(1) = "b"
ary(2) = "c"
Addcontrols_v2 ary()
End Sub

3)CreateControl Sub
Public Sub addcontrols_v2(fieldnames() As String)
For i = 0 To UBound(fieldnames)
tFIELD = fieldnames(i)
Application.DoCmd.OpenForm "T_TEMPlab_SUB", acDesign
Set ctrlFIELD = CreateControl("T_TEMPlab_SUB", acTextBox)
ctrlFIELD.Name = tFIELD
Set ctrlFIELD = Nothing
Next

When I run this I can actually see design view being opened and it is a couple of seconds before it completes. Why is this so slow? In my actual application there are several subforms so the processing time adds up to be very problematic, especially on our slower computers. It appears that simply opening a form in design view is a time consuming step. Any way around this?

If there is no way to avoid this I could use a different solution. If the subforms already have all the potential fields added to them (85 currently) i can use the "columnhidden = true" method to hide the ones the user does not want to see. This seems ridiculous though to have such "wide" tables when only 10% of the fields will be used at any given time.

This issue has been plaguing me for months, any insight is greatly appreciated.

P.S. The underlying issue is improper workflow. This database is for a water quality laboratory. The hardcopy datasheets the user uses to enter lab results are improperly structured - they have multiple parameters per record when the correct relationship is 1 parameter per record (this is how the table the data is actually saved in is structured). The people at the lab do not understand proper structural relationships or data management, in fact the hardcopy form the user uses to enter lab results is actually what we would call a "report" - it is formatted in a way the end user can easily understand the data and compare parameters across sites, but is pure mayhem when used as a result collection/data entry form. The lab does not like changing anything, especially the underlying nature of their workflow, and I was basically hired to "band-aid" the situation.

 

[DCrake]

So lets say you acheive you goal and get it to create the controls dynamically. What are you going to do with these controls? How are you going to bind them to tables and their coressponding fields. How are you going to implement any validation on said controls? All seems far to convoluted and extreme to actually get it to work. And don't forget, when you have completed this task, have you considered how the app is going to bloat with the new forms/controls/etc.?

 

[mbreu996]

To clarify I did achieve my goal of the dynamic form, but it was taking too long to execute on certain computers which is when I began to question it.

Unfortunately i did put in the time to write tons of code to "save" the data (maybe 200 hours as I had to teach myself everything). Part of this is a series of QC routines. I sure learned a lot about using "Application.DoCmd.RunSQL" to translate the data.

You are 100% correct, I had to basically reinvent the wheel - had to write extra code that does the same thing already built into access when normalisation not violated. I created a huge amount of work for myself.

I did not notice the app bloat with the addition of these forms/controls because they are deleted and remade every time - at any given moment it is only 1 group of forms/controls.

Overall I am currently leaning towards telling the lab they can enter data the way I tell them (1 parameter per record) or they can get someone else to make the database.

Out of curiosity, was I correct in noticing that opening a form in design view is "slow"?

 

[DCrake]

Even when using the Access Wizard to make forms based on underlying tables/queries, if the recordsource is very wide it can take time to process it. However could you not make the form hidden in any way during the build process?

 

[mbreu996]

Hiding the form would make the procedure more aesthetically pleasing to the user but this would not alleviate the slow processing time, or would it?

 

[mbreu996]

Thanks for the assistance, it is much appreciated. Hopefully I won't keep learning my lessons the hard way!

 

[ChrisO]

You might also like to consider that you will not be able to make the database into an MDE file.