Adding controls with VBA - depending on table columns

[Danyul]

Hi all,

I'd like to be able to display a form that automatically formats its own detail section on opening.

Each time the form is opened I need it to reference a table and add text box and label for each column title in the table (the label will hold the column title, the text box will hold record values dependant on the values of some 'master controls' permanently in the form header).

I can handle the inter-relating code between the master controls and any sub controls (as long as the form names them as they are automatically added), my difficulty is in adding the controls, naming and arranging them (I am expecting to have to automatically add upto 100 controls (!)).

Any help greatly appreciated.

Regards

Dan

 

[THoey]

Dan,

I really don't think so. Just manually handling up to 100 text boxes on a form would be almost impossible, much less doing it in code. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Danyul]

mmm, Maybe I didn't explain that one very well..

I'm refing the solution as I go along,

What I need is the code that adds a text box and its asoc. label to the design section of a pre-created form.

**********************************
For column_no = 1 to last column

*Add a text box to the form
*Name the text box and give its label the column heading

next column_no

***********************************

I appreciate the need for 100 text boxes on a form appears unwieldy at present but its an end user requirement!


Regards

Danyul

 

[THoey]

I guess I did understand you correctly. I still don't think you can do it. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[jes]

Do you want this form to be opened in form or design view?

 

[SIG357]

I know for sure that you can't add controls trhough code to a form that is presently in form view. It has to be in design view before you can.

 

[Danyul]

'lo all

Thanks for your replies.

Since posting I have *partially* solved the problem but need a little more help.

If I open the form in design view I have been able to add the required no. of text boxes and assoc. labels by using the CreateControl method (a new one on me!).

However I am now trying to get the textbox to dlookup a record value from the table.

if I use:

************************************************************
with txt
.ControlSource = DLookup("[" & colname & "]", "OutputWithValues", "[REF] = 'N0123A'&quot
end with
************************************************************

it works but puts the result of the sql string in the box in design mode, thus it won't dispay in form mode (it just gives #Name?).

So I thought i needed to put the dlookup string in quotes..

i.e
*********************************************************
.ControlSource = "=DLookup('[' & colname & ']', 'TassOutputWithValues', '[UIN] = ' & myrandom & ')"
**********************************************************

where myrandom = "NO123A"

but then it tells me the string is only allowed to be 255 characters, even though it seems to be less than this.

Can anyone help !!

Regards

Dan