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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Yes, if the control has an associated label:

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

Dale FyeOwner, Developing Solutions LLCCommented:
Is your subform a form, or is the SourceObject of the subform control actually a query?
James BaileyProject PlannerCommented:
Is the form in datasheet view, perchance/
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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


Dale FyeOwner, Developing Solutions LLCCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.