Question Change name of control vba

[byTimber]

I have a series of duplicated controls on a form which I need to rename.

I have tried:
Sub NumberTextBoxes()
Dim objTB As TextBox
Dim i As Integer
DoCmd.OpenForm "Form3", acDesign

i = 1
For Each objTB In Form3.TextBoxes
objTB.Name = "Textbox " & i
i = i + 1
Next objTB
End Sub

But no joy.
Can this be done?

Thanks...

 

[RainLover]

This is a strange request.

First of all you could have named them manually by now.

Second (If I read your code correctly) you are going to name them *1, *2, *3 etc.

This is not a good naming convention.

They sould be named something appropiate to the Data that is recorded.

But I assume they are all simillar which means your Table has been designed incorrectly.

Could you please supply more information.

 

[byTimber]

Thanks for fast response Rain (bucketing down here in Colchester UK).

This is my project:
I have a series of user statements to follow up on .
txtClientStatement1
optTrueFalse1
txtEvidenceNeeded1
otptEvidenceOKorNotOK1
txtAspirationalNow1
txtAmendAspiration1

Fields in Table Statements match these control names and the datasource for each control is set to that table column.
(Don't even ask what the hell is all this about!- would take forever to explain!)

This first Statement and the other controls are on 2 lines of a form.

I need to duplicate this layout below the first one as many times as I think I'll need (probably a lot).
I can duplicate the first set of controls and produce a page of them all with the same Name and ControlSource.

With help from the site I have managed to change the controlsource once I have the control name. But I cannot find out anywhere if I can find a code to change the eg txtStatement1 name to txtStatement2 etc.

My intention would be to grab the number off the end of the txtStatement and attach it to the end of the other controls (having removed their default number (1) and then reroute theirControlSource.

Obviously this is pointless if the're all named txtStatement1. Hence my need to rename them sequentially.
Hope this isn't totally incomprehensible!

Any thoughts much appreciated.
Roger ..

 

[spikepl]

RainLover is onto something. Controls holding the same type of information but labled 1, 2 ,,, N are a sign of a structural problem in the database.

You say you have fields in tables matching these. That means you have put the same type of data in separate containers (in this case columns) instead of keeping the same type of data in the same container, and just adding a column to tag each item.

There will be hell to pay for this, in queries, forms and reports, and this is why normalization rules say you should not do such things.

 

[byTimber]

I think that is why I'm having the problems - I have changed the procedure on your advice and it seems to be the way forward....

Many thanks for your time..
Roger ...

 

[RainLover]

I think you need two tables.

One for client details like Name Address etc.

The second would have the fields that you mentioned.

Create a Form SubForm situation.

The Main Form = Table One and the Sub Form Table Two. They would be joined by The Primary key (AutoNumber) in table One and a Number Field (Long) being the Foregin Key in Table Two.

You couls have as many Rows od Data as you wish. Give the Second Table a Primay Key of Autonumber plus Something to identify the row such as Date.

The Primary Key and Foregin Key should never be seen by end users.

Some would argue that with Normalisation I have not gone far enough but without more info I think this will do.

Hope I am on the right track.

 

[NigelShaw]

Hi

I had a routine a while ago that I made to bulk change form properties. If I can remember where I stored it, I'll post it. Basically, in vba, it opened forms in design mode, made changes ( think it was the name ) then saved.

Nidge

 

[Pat Hartman]

byTimber, your schema is not normalized and so is causing considerable unnecessary work. We can help you normalize it and you can spend your time on more productive efforts.

 

[byTimber]

Pat, I am trying to achieve a process which will be flawless once, with the help of this site, I can get over one more hurdle which is to address the controlsource of a control and change it in code.
Best wishes,
Roger..

 

[byTimber]

I have succeeded in changing the names and data control sources now.
Thanks to all who supplied me with the clues.....

 

[RainLover]

Pat

You know what is going to happen.

Unfortunately others can't see it.

 

[Pat Hartman]

Rain,
Some people have to do it the hard way.
Pat