Is there a way to dynamically change the column captions on an ACCESS 2010 subform

Hi Experts,
Is there a way to dynamically change the column captions on an ACCESS 2010 subform.

Thank you very much in advance,
Who is Participating?
Dale FyeCommented:
unfortunately, if you are actually using the query as the source object, it will not have an associated label, and you obviously cannot create one.

One method to address this would be to create a form (datasheet view) based upon this query.  Set the RecordSource of this form to your query and add controls for all of the current fields.  Make sure there are enough textboxes for each of the columns that could show up in the query.

Now things get a bit more complicated.  If you have columns (row headers) which will always show up in the query, then you can set the ControlSource of those controls to the appropriate column name.  For all of the other columns, delete the ControlSource.  Also, for all of these other controls, go through and change the control names to "lbl_4", "txt_4", "lbl_5", "lbl_6", ...

The numbers of those should correspond to the columns of the query where the data will come from.  Now, in this forms Open event, you will need some code that changes the label caption and the textbox controlsource for each of the fields that is a column header.  Code would look something like:
Private Sub Form_Open(Cancel as Integer)
On Error Goto ProcError

Dim rs as DAO.Recordset
Dim intfld as integer
Set rs = me.recordsetclone

for intFld = 2 to rs.fields.count-1
    me.controls("lbl_" & intFld).Caption = rs.fields(intFld).Name
    me.controls("txt_" & intFld).ControlSource = "[" & rs.Fields(intFld).Name & "]"

    On Error Resume Next
    set rs = nothing
    Exit Sub

    msgbox err.number & vbcrlf & err.description, , "Form_Open Format Controls"
    debug.print "Form_Open Format Controls", err.number, err.description
    debug.print "rs.Fields.count  = ";rs.fields.count
    debug.print "form.controls.count = ";me.controls.count
    Resume ProcExit

End Sub

Open in new window

This particular code assumes that you have two "Row Header" columns in your query, and that the rest are all variable based on the data in your query.   When this form opens it will fill in the captions of the labels with the name of the field, the error handler here is very simplistic, but you could test for more columns in the query than controls on the form and create a more user friendly error message.
Gustav BrockCIOCommented:
Yes, if the control has an associated label:

Me!SubformControlName.Form!FieldName.Controls(0).Caption = "New Caption"

Dale FyeCommented:
Is your subform a form, or is the SourceObject of the subform control actually a query?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James BaileyProject PlannerCommented:
Is the form in datasheet view, perchance/
mainrotorAuthor Commented:
Dale Fye,
The SourceObject of the subform is actually a query.  Here's a sample of the code:

    Dim pSQL As String

    pSQL = "SELECT * FROM Staging_Table ORDER BY Staging_Table.Tech_Name"

    Me.dataAvailabilitySubform.SourceObject = "Query.qryStagingTable"
    Me.dataAvailabilitySubform.Form.RecordSource = pSQL

Open in new window

mainrotorAuthor Commented:
James Bailey,
My subform is in datasheet view.  How can I change the column names dynamically?

Data sheet view
Thank you,
Gustav BrockCIOCommented:
If the control does not have an associated label:

Create a label.
Mark the label and press Ctrl+X
Mark the column and press Ctrl+V


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.