I have one app where I have a lot of list boxes on several open forms. I use some public subs to requery them. I usually call it from the on close event of any form that alters data.
The first sub loops through the forms collection and if a form is open it calls the second sub to iterate through the controls and requery the list boxes.
Code:
Public Sub RequeryAllLists()
Dim frm As Variant
On Error GoTo RequeryAllLists_Error
For Each frm In CurrentProject.AllForms
If frm.IsLoaded Then
Call ReQListBoxes(Forms(frm.Name), False)
End If
Next
On Error GoTo 0
Exit Sub
RequeryAllLists_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RequeryAllLists of Module modLBX"
End Sub
Code:
Public Sub ReQListBoxes(frm As Form, Optional NullValue As Boolean)
Dim ctl As Control
Dim ctl2 As Control
On Error GoTo ReQListBoxes_Error
For Each ctl In frm.Controls
If ctl.ControlType = acListBox Then
If NullValue = True Then
ctl.Value = Null
End If
ctl.Requery
End If
If ctl.ControlType = acSubform Then
For Each ctl2 In ctl.Form.Controls
If ctl2.ControlType = acListBox Then
If NullValue = True Then
ctl.Value = Null
End If
ctl2.Requery
End If
Next
End If
Next
On Error GoTo 0
Exit Sub
ReQListBoxes_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ReQListBoxes of Module modLBX"
End Sub
HTH