Runtime vs Design Time Problem (1 Viewer)

[zooropa66]

Dear All,

I hope someone can help explain why i can achieve the following at design time but not at runtime.

I enter text into a textbox txtEntry on a form frm_MainForm
e.g. enter abcd then click a command button to execute some code with the following effect:

A new column "abcd" is added to a table tbl_mytable
A label lbl_MyLabel with caption = "abcd" is added to form myForm
A textbox txt_myTextBox with controlsource = "abcd" is added to form myForm

This all works just fine when i run the form in designtime but not runtime

I won't post code at this step as i'm probably overlooking something simple like "You Can't Do this at Runtime"

Thanks

 

[zooropa66]

I'm still not sure why it worked in design but not in run mode but i fixed the problem by deleting the parentheses from the following line

i.e. changed

CurrentDb.Execute ("ALTER TABLE tbl_mytable ADD COLUMN abcd;")

to

CurrentDb.Execute "ALTER TABLE tbl_mytable ADD COLUMN abcd;"

 

[Galaxiom]

A requirement to add fields to tables and controls to forms during runtime is usually an indication of inappropriate data structure.

 

[boblarson]

A requirement to add fields to tables and controls to forms during runtime is usually an indication of inappropriate data structure.

I SECOND that statement!

 

[pbaldy]

I agree with Galaxiom, and I'll add that steps 2 and 3 won't work in an mde/accde, nor I suspect if the user has the runtime version of Access. If you're developing for either of those environments, I smell trouble ahead.

 

[zooropa66]

Thanks to everyone for your replies.

From reading this and other forums, I got the impression that what i was trying to do was not such a good idea.

The problem i have is that i need to be able to add new product types and select which process steps are associated with each type (so you have in effect a truth table) e.g.

Process Step ID Process Step Type A Type B Type C
1 Step 1 1 1 1
2 Step 2 1 0 1
3 Step 3 0 1 0

etc

As this is displayed via a Query in a Subform (Datasheet) then there needs to be a label / textbox pair for each of the fields above (except Process Step ID which i choose not to display)

I know that i can approach this another way so that i don't generate controls using DDL > where you just put many more label / textbox pairs on the form than you need (and i have seen code that enables you to not display those that aren't associated with a field). Does that seem like a better approach or is there a smarter way to go about this?

Thanks Again

 

[Galaxiom]

Your process steps need to be stored in another table with relationships to the parent record. This way you can add as many new process steps as you wish by adding records rather than fields.

Your forms are going to have to change a lot by it is really important you make this change to the underlying structure.

 

[zooropa66]

Hi GalaxiomAtHome and everyone else who helped point me in the right direction. Thanks for preventing me travelling down a road of pain.
I tried your suggestion and created a junction table between the Process Step and Type tables and everything fell into place beautifully. I can't believe how hard i'd made it for myself. I'm not sure if i need to mark this post as resolved or whether an administrator does this.