Syntax for Datasheet Caption reference in VBA - MS ACCESS 2007

[Jean9]

How does one refer to the the Datasheet Caption property of a field in a Datasheet form in VBA? I can set it on the Other tab of the Property Sheet but need to set it on Form Load rather than a fixed value for the form. I can't seem to find any syntax out there for referencing it.
Thanks in advance!

 

[dhookom]

This works for me to change the caption from EmpID to Employee:

Private Sub Form_Load()
    Me.EmpID.Controls(0).Caption = "Employee"
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Jean9]

I find that I have to set this from the parent form (my datasheet form is a subform). I get a Run-time error

'2467': The expression you entered refers to an object that is closed or doesn't exist

when I use the following code

Me.SFSB_SCHEDULE_TMP!COL1.Controls(0).Caption = Me.COL1

where Me.COL1, though a text field, contains the value I want to use as the column header in the datasheet.

Note: I got the same error when I used this same type code on the subform where I referred to the parent form:

    If Not Me.Parent Is Nothing Then
        If Not Nz(Me.Parent!CbxWC, "") = "" Then
            Me.COL1.Controls(0).Caption = Me.Parent!CbxWC
        End If
    End If

 

[dhookom]

The following code assumes you have a text box in the subform named "COL1" and a control (maybe a text box) in the main form named COL1.

Me.SFSB_SCHEDULE_TMP.Form.COL1.Controls(0).Caption = Me.COL1

You haven't stated the context of why you are attempting to do this or which event in the main form contains the code.

Duane
Hook'D on Access
MS Access MVP

 

[Jean9]

Tried the code:

Me.SFSB_SCHEDULE_TMP.Form.COL1.Controls(0).Caption = Me.COL1

Got a Run-time error '2455':
You entered an expression that has an invalid reference to the property Form/Report

Got the same error when I tried:

Me.SFSB_SCHEDULE_TMP.Form.Controls("COL1").Caption = Me.COL1

COL1 is the name of an unbound text control on the parent form and a bound text control on the subform. The field names are generic because the subform's recordsource (a table) was created to act something like a pivot table where fields COL1 through COL9 could contain data from any number of current work centers. So, I am trying to accomplish the following:
1) User selects on the parent form whether they want to see data for one work center (ie "LINE1", "LINE2", etc or "All")
2) If it's just one value, then after much behind the scenes code, the subform's recordsource is only going to contain COL1 data but the subform's COL1's datasheet caption needs to be equal to the value the user selected on the parent form (ie "LINE1", "LINE2", etc).
3) If the user selected "All" then code gathers a listing of all the work centers to gather data for, gathers the data, and populates the subform's recordsouce with up to 9 columns of data. The subform's COL1, COL2, COL3, etc datasheet caption needs to equal the parent form's values for COL1, COL2, COL3, etc. For instance parent form COL1 value = "LINE1". The subform's COL1 datasheet caption needs to be "LINE1".

The COL fields on the parent form are going to be hidden once this setting the datasheet caption thing works. It will only be on the parent form so that there will be a value to use to set the subform field's datasheet captions. Or, in a perfect world, get rid of the parent form COL fields altogether and when I get the listing of work centers, just use those values to update the subform's datasheet captions with....

 

[dhookom]

Do your text boxes on the subform have "attached" label controls? The attached label controls are referenced by ".Controls(0)". If not, the solution I suggested won't work.

It sounds like your subform could contain a number of fields and may vary.

Have you considered simply creating a crosstab query and setting the Source Object of the subform control to something like "Query.MyCrosstabQueryName"?


Duane
Hook'D on Access
MS Access MVP

 

[Jean9]

The text boxes on the subform have no attached label controls. The subform has a set number of columns (10 including a column that is being used to identify for the user what the data in COL1 through COL9 represents). I just need to be able to set the caption of those columns to say what work center that data represents. The way the data has to be gathered doesn't lend itself very well to a crosstab query. I won't bore with any specifics but the overhead for what is being gathered must be huge based on how much time it takes to gather it.

The form needs to look kinda like (please excuse the formatting)

Week of: LINE1 LINE2
1140-BOOKED # #
1140-REMAIN # #
1140-CAP # #
1140-BOOKED $ $
1140-REMAIN $ $
1140-CAP $ $

Where LINE1, LINE2 might one day be LINE1, LINE2, LINE3. The column for LINE3 would be there it just wouldn't have any data in it until the time that it does and then the caption has to say LINE3.

The datasheet caption of the field can be set in design mode of the form on the Other tab of the Property Sheet for the field, so it stands to reason that I should be able to set that programmatically, right? This should be simple, right?

 

[dhookom]

The "Name" property of the text box provides the caption if there isn't an attached label. The Name property can only be set in the design view of the form/subform. Have you considered attaching label controls?

Can you provide the Record Source of the subform (or a couple different Record Sources)?

Are you dynamically setting the control sources of the text boxes?

I think there might be a better solution for you but you haven't provided much information about your specifications.

Duane
Hook'D on Access
MS Access MVP

 

[Jean9]

With the subform:
I tried attaching a label, making the caption "LINE" in its property sheet but the label doesn't show on the datasheet on Form View. I put the label in the page header, it didn't show. I put the label in the form header, it didn't show. So a field label wasn't the way to go so's far that I can tell.

The control sources of the textboxes always remain the same. Just what the data in the textboxes represents might differ from one time to the next. That's why changing that column's datasheet caption would be helpful. When you say, can I provide the record source of the subform, are you asking for data or the name of it, or....and based on the answer how do I provide that (in the case of data)?

I wish I could show you a screenshot of the property I want to change at run time...

 

[Jean9]

Okay, don't throw your hands up in frustration...I think I got it.
So the reason the label thing that you suggested wasn't working was that I still had the Datasheet Caption value for that field filled in.
What I did:
With the subform:
Deleted the value in the Datasheet Caption property
Associated a label with each field that I needed a variable caption for (like you told me to, sorry..)
With the parent form:
Via code in the appropriate spot:

If Not Nz(Me.COL1, "") = "" Then Me.SFSB_SCHEDULE_TMP.Form.Controls("LblCOL1").Caption = Me.COL1

Is there an "embarassed to be me" emoticon?
Thanks for your help!!

 

[dhookom]

Glad you got this sorted out.

I still think there might be a more efficient solution but we might not want to go there at this time.

Duane
Hook'D on Access
MS Access MVP