Nested For Each statements

[Mark Wild]

I'm trying to write a procedure that will requery the combo boxes on any loaded forms to enable users to add a record, return to the original form and view the new data in the combo.

As the input form can be called from a number of other forms, I can't name the form that needs requerying.

Below is my attempt so far. I am trying to identify the forms that are open, then pass this to the second For Each statement to look for combo boxes.

Any advice on how I can pass the object over to the second statemet or an easier way of doing this would be appreciated.

Dim obj As AccessObject
Dim dbs As Object
Dim ctl As Control
Dim str As String

Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
If obj.Type = acForm Then
str = obj.Name

For Each ctl In Forms!(str)
If ctl.ControlType = acComboBox Then
ctl.requery
End If
Next ctl

End If
End If
Next obj

Set dbs = Nothing

Thanks,

 

[WayneRyan]

Mark,

I didn't have any existing code like this, but this may work.

For i = 0 To Forms.Count - 1
   Set frm = Forms.Item(0)
         For Each ctl In frm
             If ctl.ControlType = acComboBox Then
                ctl.Requery
             End If
             Next ctl
Next i

hth,
Wayne

 

[Mark Wild]

Wayne.

Worked a treat. Many thanks.

M