Loop through controls on subform (1 Viewer)

[jaryszek]

Hi,

i have MainForm with 2 subforms like in attachment.

1) frmSourceSubform
2) frmSourceApplicationSubform

And now i want to run code like below (to show text "working!") when i will select "Linux" on frmSourceSubform. Problem is when frmSourceApplicationSubform is empty, i can not retrieve ctl.Recordset.Name.
If i add something,

Debug.Print ctl.Recordset.Name

is working.

Private Sub Command2_Click()
Dim frm As Access.Form
Dim frmSelected As Access.Form
Dim TableStr As String

sfrm = Me.Name

Set frm = Forms(sfrm).Controls("frmSourceApplicationSubform").Form

Dim ctl As Control

On Error GoTo ErrorHandler

    For Each ctl In frm.Form.Controls
            If ctl.ControlType = acComboBox Then
                Debug.Print ctl.Recordset.Name
                'frm.LstFieldsToAdd.RowSource = "tblWorkload"
                [B]MsgBox "working!"[/B]
            End If
        Next ctl
    
Exit Sub
ErrorHandler:
MsgBox "There is no combobox field"
End Sub

To mirror error:

1. Go to frmMain
2. select record with "Linux" source
3. Click "Run macro" button.

you will see msgbox message (no combobox field).

Please help,
Best,
Jacek

 

[MajP]

I do not get the purpose of this, but if you want to really do this. There are cases when the recordset of the combo is not yet set eventhough it has a rowsource. Here is a workaround if you really need to do this

If ctl.ControlType = acComboBox Then
                Set ctl.Recordset = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenDynaset)
                Debug.Print ctl.Recordset.Name
                'frm.LstFieldsToAdd.RowSource = "tblWorkload"
                MsgBox "working!"
            End If

 

[Minty]

I'm puzzled by the purpose of this as well.
What does it achieve ?

 

[jaryszek]

aaa great working like a charm,

thank you.

This code is listing all available fields from specific combobox on specific subform.
I have a lot of continuos forms with only one field as combobox.

Thanks to it i can automatically choose fields which i want to add for subform and specific combobox from another subform.

So automatically i am populating choosen combobox and subform using available fields.

Best,
Jacek