If you add, or edit, records in a Form then any Combo or List box based on that form’s Datasource is no longer up to date.
I’ve seen many attempts to handle this and most are very clunky, others difficult to maintain. What we should be doing is requerying all the Lists in our Application when, and only when, the data changes.
So here is an easy method. First we need two global functions –
Public Function RefreshForms()
Dim frm As Form
For Each frm In Application.Forms
RefreshControls frm
Next frm
End Function
Public Function RefreshControls(frm As Form) As Boolean
Dim ctl As control, lng As Long
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox
ctl.Requery
lng = ctl.ListCount ‘ populates the list so that the first time you try to you CAN go to the bottom
Case acSubform
ctl.Form.RefreshMe
End Select
Next ctl
End Function
Then all we need to do is call RefreshForms in the After Update event of any form that can add or edit records.
RefreshForms iterates through the Forms collection. The Forms collection only includes Open Forms as opposed to the CurrentProject.AllForms collection which includes all forms open or not.
So for each form, RefreshForms calls RefreshControls passing it the Form as an Object (Specifically as a Form Object).
RefreshControls then iterates through all the controls on that form and, if it finds a Combo or List Box, it gets requeried.
You will also notice the line lng = ctl.ListCount. This forces Access to fully populate the list so you don’t get that annoying behaviour where you attempt to scroll to the bottom of the list but need to keep repeating that action as you only get half way down.
Then we have the case of Sub Forms. Well firstly they are not included in the Application.Forms collection so we then need to call RefreshControls passing the Sub Form as a Form Object. This recursion will then go down as many levels of Sub Forms in Sub Forms as it needs to.
This will effectively requery every Combo and List box in every open form.
This is pretty quick but, if we needed to, we could speed this up by Passing an entity name like “Customer” so that only boxes with names that included “Customer” would requery.
In every form in my applications I put a call from the After Update event that does generic stuff so I only need to put the call to RefreshForms in one place.
In further posts I’ll elaborate on this function, but for now, in its simplest form –
Public Function After_Update(ByRef frm as Form)
On Error Resume Next
With frm
Call RefreshForms
End With
End Function
' In each Forms After_Update Event we call the above procedure, passing it the current form, like this
After_Update Me