Gtetting Dtasheet field names

[Middlemarch]

debug.print Form_frmMain.subDisplay.Form.ActiveControl.Control s(0).Caption



I find this gives the name of the active column (field).
What I'd like to do is put the names of all visible columns (in the datasheet) into a combo box.
But I can't work out the syntax to get them - and also if the combo box should use a query or a value/list.
Thanks for any help.

[June7]

Trying that code and get error message "The expression you entered refers to an object that is closed or doesn't exist." I think problem is that my labels are not associated with textboxes because they are in header section.

With redesigned form, this works:

Private Sub Form_Load()
Dim ctl As Object
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then Debug.Print ctl.Controls(0).Caption
Next
End Sub

Build combobox item list with ValueList parameter.

[Middlemarch]

Thanks June, That is almost it. I'm getting some text boxes that aren't shown on the Form though.
As I look at the datasheet subForm, do you know how to retrieve just the Column or Fields names that are visible.
This is the name above the first row, with a grey backcolor.
The Form name is frmMain and the subForm name is SubDisplay

[June7]

Try adding And ctl.Visible to the If condition.

[Middlemarch]

For some reason that didn't work, but I can hard wire the caption names.

[June7]

What does "didn't work" mean?

Works for me.

[Middlemarch]

Understood, but not here. It still added captions that weren't on the Form to the combo box.
I think I had If ctl.ControlType = acTextBox and ctl.Visible = True Then...
Which I've changed to

For Each ctl In Form_frmMain.subDisplay.Controls
        If ctl.ControlType = acTextBox Then
            fb = ctl.Controls(0).Caption
            If InStr(fb, ":") = 0 Then
                Combo0.AddItem fb
                Combo1.AddItem fb
            End If
        End If
    Next

The colon is only in the caption for those not on the Form.

I am getting confused with all the various names. A bigger problem now is an error in the combo change event.= Form_frmMain.subDisplay.Controls(Combo0).Value
Microsoft Access can't find the field 'xxx' referred to in your
expression.
Correct as xxx is the caption. Knowing the caption value, can I get its control Name ?

[June7]

I don't understand how could get captions that are not on form - this makes no sense.

Getting control name associated with given caption would require looping through controls and testing
If .Caption = strCaption Then x = .Name

[Middlemarch]

I think it better to hard code everything. There's only 19. Trying to get names, properties details from these controls gives so many errors it's not funny!

I did try in a vain attempt to follow your suggestion

Private Sub Combo0_Change()
For Each ctl In Form_frmMain.subDisplay.Controls
 If ctl.Caption = Combo0 Then Debug.Print .Name
    Next
End Sub

I was surprised the combo0 change event did not know what Combo0 was, even though I could print it in the debug window.
It didn't know ctl.Caption either (Object doesn't support this property or method)
So a bit of a logic change here needed to get this working.
Thanks for all your help thusfar.

[June7]

What is value of Combo0 - an ID or a descriptive text?

Private Sub Combo0_Change()
Dim ctl As Object
For Each ctl In Form_frmMain.subDisplay.Controls
    With ctl
    If .ControlType = acTextBox Then
        If .Controls(0).Caption = Me.Combo0 Then Debug.Print .Name
    End If
    End With
Next
End Sub

Now if any textboxes could not have associated label, would need more code to deal with that possibility.

[Middlemarch]

Combo0 holds text, the caption names.
Actually that did it. I dunno why I had so much trouble. Thanks for sticking with me Here's the code

Private Sub Combo0_Change()
    Dim ctl As Object
    For Each ctl In Form_frmMain.SubDisplay.Controls
        With ctl
        If .ControlType = acTextBox Then
            If .Controls(0).Caption = Me.Combo0 Then Contents = .Value
        End If
        End With
    Next
End Sub


Private Sub Form_Load()
    DoCmd.MoveSize 10000, 8000, 9000, 6000
    Me.Caption = "Move Field Contents"
    Dim fb
    Dim ctl As Object
    For Each ctl In Form_frmMain.SubDisplay.Controls
        If ctl.ControlType = acTextBox Then
           fb = ctl.Controls(0).Caption
            If InStr(fb, ":") = 0 Then
                Combo0.AddItem fb
                Combo1.AddItem fb
            End If
        End If
    Next

    CurrRow = Form_frmMain.SubDisplay!Opus
    Contents = Form_frmMain.SubDisplay.Form.ActiveControl
    Combo0 = Form_frmMain.SubDisplay.Form.ActiveControl.Controls(0).Caption


End Sub

Context is a text box holding the values in the Combo0 field.