Refresh contents of List Box as new items added to Form (1 Viewer)

David Ball · Jun 12, 2018

Hi,

I have a table that contains just an autonumber ID field and a text field, ModelName, where model names of cars can be entered.
I have a Form where the user can see what models are already in the table and then add new models or edit existing models.
On the form I use a List Box to display the models that are already in the table and then the user can select a command button that opens another Form where new models can be added or existing models edited. (I’m not sure if a list box is the best way of showing the contents of the table but I couldn’t think of a better way. I don’t expect that a large number of models will ever be entered).
The problem is that the new entries don’t appear in the list box until I close the form and then re-open it. I tried to get around this by adding another command button that requeries the list box when selected. This works but I would rather have the new entries appear when the form has a new record entered. This could be triggered by either the form being closed or scrolled to the next record after a new record is entered.
Can this be done and, if so, which event would I put the requery code behind?
Thanks very much
Dave

pbaldy · Jun 12, 2018

If the entry of new items will always be done from the second form, you could use the close event of that form, or the activate event of the form with the listbox. You can open the second form with acDialog and requery the listbox immediately after. The requery won't happen until the second form is closed.

moke123 · Jun 12, 2018

I use 2 subs when dealing with list boxes. the first sub will re-query the list boxes on the specified form. The second sub will re-query all the list boxes on all open forms. I usually run them OnClose of the data entry form.

this will also requery listboxes in a forms subform

Code:

Public Sub ReQListBoxes(Frm As Form, Optional NullValue As Boolean)

    Dim ctl As Control
    Dim ctl2 As Control
    
    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

End Sub

_____________________________________________________

Public Sub RequeryAllLists()

    Dim Frm As Variant
       
    For Each Frm In CurrentProject.AllForms

        If Frm.IsLoaded Then

            Call ReQListBoxes(Forms(Frm.Name), False)
            
        End If

    Next

End Sub

to requery one specific forms listboxes...

Code:

Call ReQListBoxes(Forms("YourFormName"),false)

to requery all list boxes on all open forms...

Code:

Call RequeryAllLists

jleach · Jun 12, 2018

Twice this year I've run into a few cases where this type of thing occurs (a list update/entry added/removed) that could affect any number of other forms that might happen to be open at the same time.

I set up an EventBus using custom events in a class for each event that I want to track:

Code:

'Create a class module and call it EventBus

Public Event EmployeeAdded(EmployeID As Long)
Public Event CustomerAdded(CustomerID As Long)

Public Sub RaiseEmployeeAdded(EmployeeID As Long)
  RaiseEvent EmployeeAdded(EmployeeID As Long)
End Sub

Public Sub RaiseCustomerAdded(CustomerID As Long)
  RaiseEvent CustomerAdded(CustomerID)
End Sub

Then create an instance of this EventBus class when your application is started. Hold it in a property on a public module (we have an App module that contains this and a bunch of configuration and startup code):

Code:

'Create a public property to expose an lifelong instance of an EventBus
'Set this upon startup so it's always available
'I use two-part module/proc names, and this module's name is App,
'so I'll access this instance from anywhere in my app via App.EventBus

Private mEventBus As EventBus

Public Property Get EventBus() As EventBus
  Set EventBus = mEventBus
End Property

Public Function Startup()
  Set mEventBus = New EventBus
End Function

In any form you want to subscribe to the events, grab a copy of the public EventBus instance and sync it using WithEvents:

Code:

'In any form you want to subscribe, use WithEvents
'and the form load/unload to grab a copy of the event bus instance.
'From there, you can tap into the events and be notified each time
'the event gets raised

Private WithEvents mEventBus As EventBus

Private Sub Form_Load()
  Set mEventBus = App.EventBus
End Sub

Private Sub Form_Unload()
  Set mEventBus = Nothing
End Sub

Private Sub mEventBus_EmployeeAdded(EmployeeID As Long)
  Me.EmployeePicker.Requery()
End Sub

And finally, to close the loop, call the App module's EventBus.RaiseEmployeeAdded whenever you add a new employee from your NotInList event or wherever else you might be adding them:

Code:

'From anywhere in the application you can
'call the App.EventBus.RaiseYourEvent and anyone
'that's listening will be notified.

Private Sub AddEmployee()
   ... code to add the actual employee
   App.EventBus.RaiseEmployeeAdded(NewEmployeeID)
End Sub

This creates a nicely decoupled pub/sub pattern that any object can use to emit and subscribe to events. Works quite nicely.

Cheers,

Refresh contents of List Box as new items added to Form (1 Viewer)

David Ball

Registered User.

pbaldy

Wino Moderator

moke123

AWF VIP

jleach

Registered User.

Similar threads

Users who are viewing this thread