Changing the field caption in Datasheet view?

[ImStuk]

I'm sure this is simple, but I just can't figure it out...

I have a sub form that is a Datasheet view. The caption for each field is the name of the text box. How do I change the caption without changing the name of the text box? By the way...the record source is from a Query.

 

[hymn]

look under label in help

Hope this helps
Hymn

 

[RickSpr]

What Hymn is saying is that on a form datasheet, the column headers normally come from the associated control's attached label's caption.

If you built the form without labels (or deleted them later), you can add labels to existing controls. Create an unattached label and set its properties. When it's ready, cut it to the clipboard, select the control you want to attach it to, and paste.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Blorf]

In datasheet view, the name of the control, not the label, crontrols how it displays (I am pretty sure)

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[PaulF]

You can have a defined title appear when in dataset or query view by setting the Field's "caption" property in the table. Ex: for a field named "HDate" with its caption property set to "Hire Date", Hire Date will appear as the field name when you view the data in datasheet view or in a query.

PaulF

 

[RickSpr]

I just tested it, ChaZ, and the column heading comes from the label--even if the label was paste-attached after the control was added.

If you have a counterexample, how did you do it?


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Blorf]

Rick, I am confused.

You are refering to the caption in the table yes? I was refering to changing the property on the form. I think we are both right.

I was interested in this thread, because for about a year, I have been trying to figure out how to change the field headings in datasheet view on the fly. I can do it with code before I open the form, but not while it's open, which defeats my purpose.

I don't like the feel of continious forms, so I havn't given up on my quest.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[RickSpr]

ChaZ,

No, I'm not talking about a table property at all. By default, when you add most kinds of control to a form, Access also creates a label control for it. (This is controlled by the properties of the default control AutoLabel property). This label is internally "attached" to the control; when you select either and drag it in Design View (other than by using the "handle" in the upper left corner), both the label and its control move together.

For controls with such attached labels, the Caption property of the label control becomes the column heading in Datasheet View.

Here's how to change the datasheet column heading for a control at run time:

Public Sub SetDSColHeading(Ctrl As Control, Heading As String)
' Sets the column heading for a control in Datasheet View. This is the same
' as setting the attached label's Caption.
    Dim ctl As Control, intType As Integer
    
    If Ctrl.Controls.count > 0 Then
        For Each ctl In Ctrl.Controls
            With ctl
                On Error GoTo ErrorHandler
                intType = .ControlType   ' Late bound! Could raise error
                On Error Resume Next
                If intType = acLabel Then
                    ' For an OptionGroup control, this could be a label
                    ' *within* the control, rather than an attached label, so
                    ' we need to check that the top left corner of the label
                    ' is outside of the Ctrl control
                    If .Left < Ctrl.Left Or .Left >= Ctrl.Left + Ctrl.Width _
                    Or .top < Ctrl.top Or .top >= Ctrl.top + Ctrl.Height Then
                        ' This is the attached label
                        .Caption = Heading
                        Exit Sub
                    End If
                End If
            End With
        Next ctl
    End If
    ' The column heading can't be set because there is no attached label.
    ' Take appropriate action here.
    Exit Sub
ErrorHandler:
    If Err.Number = 3270 Then ' Property not found
        ' The ControlType property is implemented by "subclasses" of the
        ' Control class. It's conceivable that we might be looking at a
        ' control that doesn't implement this property. If so, it's not
        ' a label control anyway, so just set intType to 0 to make the
        ' code skip over it.
        intType = 0
        Resume Next
    End If
    ' Unknown error occurred
    Err.Raise Err.Number
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

I forgot to mention, in response to PaulF's post:
This is true, but it only works before the attached label is created. It works because the table Caption property is the default value for the Caption property of an automatically created Label control. If you set the table Caption property after the Label control is created, it has no effect on the Label's Caption, and therefore none on the datasheet column heading.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Blorf]

O my gosh.

RickSpr, you just solved an ancient problem for me!

Ok, my problem is that I have been using the datasheet form wizard to create datasheet forms and manipulate after the fact.

Doing so does not create the linked labels, so changing the labels has no effect.

If I use the wizard and create a tabular form or any other type of form, the labels are linked, and I can use the magic listed above, even if I switch to datasheet view as default.

I am extremely excited.

Thank you thank you thank you!

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[RickSpr]

You're welcome. I've never used the Datasheet Wizard, that I remember, so I didn't realize it doesn't create labels.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Blorf]

It does, but they are not linked to the fields.

This I didn't know either until I tried your suggestions.

Thanks again,
ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert