Change name of control as form opens

[Happy YN]

One of my forms is in datasheet view. The column headers display the "name" of the text boxes. I want to be able to display a different column header under certain circumstances so it would seem i have to change the name property of the text box. I have put in the open event of the form me.txtbox1.name= myvariable (myvariable is a previously defined string), however it does not seem to register.
Further more how could i cope with all the code such as doubleclick in txtbox1 if its name is now changed?
Is there any way I could just change the column header caption and leave the control alone?
Whilst on the subject does anyone know how I can run code through clicking on those column headers. They seem to be dead! How do programs such as outlook work where you can click on a column header to sort by it etc (a tiny arrow sometimes appears?)
Thanks in anticipation

 

[Rich]

Use a continuous form where you can change the caption of labels

 

[Happy YN]

Thanks Rich
your answer basically amounts to the fact that I cant do what I want in datasheet view but there are other considerations which force me to use datasheet view.
also you have not answered the fact that there are programs which allow clicking on the header.
I thought of pasting a label on top of the column header but I can't get to it in datasheet view - there must be some way??
Thanks anyhow

 

[Rich]

I can't think of anything that a datasheet does better than a continuous form

 

[Happy YN]

user can change column widths and even their order

 

[arvindn]

I just wonder why Microsoft couldn't merge the functionality of a continuous form and datasheet to devise a "perfectsheet". Another one of Microsoft's idiosyncrasies.

Coming to the point, if u are using a form then change the captions of the attached labels with each text-box/combo-box. If u are using a table fill in the caption property of each field in table design view.

 

[Happy YN]

Thanks arvidn
But unfortunately the label captions have no bearing in datasheet view, only the names of the textboxes appear as column headers

 

[arvindn]

Try it out practically. See that the labels are attached labels. There's no question of it not working. I use it for my applications.

 

[Happy YN]

What are attached labels?

 

[arvindn]

An attached label is a label that has its parent property set to some other control, just as a subform has its parent property set to the main form.
U will understand practically when u open a new form in design view, and drag a field from the field list to the form. When u select the text-box, the label is also selected. This indicates that the label is attached. I suggest u drag some fields and change labels' captions and view the new form in datasheet view to get my point. That is the best recourse.
If by mistake the form u are using has detatched labels, then select the label, press Ctrl+X and then select the text-combo box and press Ctrl+V to attach it.

 

[Happy YN]

Just tried it Thats excellent and saved me alot of aggro!
But I otice that attached labels have no click events (or any events) as opposed to labels created individually. Does that mean there is no way to click on the column header
Thanks again for this brilliant advice

 

[arvindn]

As i said before, there are some inexplicable things that Microsoft does. One of them is making datasheet "a gorgeous cripple" . The click events will not be available to u for datasheet column headers whatever u do.

I have one more suggestion for u. If it is crucial to ur application that u be able to change the column headings, i suggest that u use the datasheet form as a subform on a dummy main form to provide all sorts of functionality. Atleast that's what i had to do to provide form headers,footers, command buttons for functionality.

 

[Happy YN]

Thanks for your time! and advice
My joy is short lived
I now discover that I cannot even set the caption of these attached labels in code! They are more or less a non entity of their own. Being able to click was not crucial but why can't I set their caption using code?
They don't even appear in the dropdown box in the vba?
Sorry to pester
your idea of a subform came to me but you could not adjust the headers together with the datasheet grid?!
Edited:Happy again It DOES work! The caption is reset every time the formopens
Thanks alot!