Results 1 to 9 of 9
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need to refresh list box(es) on mainform once control in subform changed

    Experts:



    I need some assistance with refreshing a control (on parent form) once a combo (on subform) has changed.

    Background:
    - Upon opening database (attached), please open form "F10_StaffMemberToOffice"
    - The top right of the main form includes two listboxes (those are the those I want to refresh/requery)
    - Based on currently existing records in "T00_JunctionTable_OBS" (aka OBS table), there are currently five (5) unfilled billets (i.e., Financial Manager, Accountant, Data Manager, etc.)
    - Also, based on the OBS table, there are currently two (2) staff members (employees) who haven't been assigned to any billets yet.

    Process:
    - In the subform, I select a **new** position from the the "Billet Name" combo. Let's say I select "Financial Manager".
    - This creates a new record in the OBS table and (temporarily) associates staff member "Vacant" to this position.
    - Given the selection of "Financial Manager" in the subform, I now want my listbox "lstBilletsAvailable" (on the mainform) be refrehsed; thus, "Financial Manager" should automatically disappear.
    - Simililarly, once I change the staff member = "Vacant" to any of the two actual available staff members (e.g., Denzel Washington or Robert Redford), I then want that listbox also be updated as well and remove the newly added employee from the "available staffmember" listbox.

    I included the following code in my subform. Unfortunately, it doesn't seem to work (i.e., the listbox do not remove any values once assignments in the subform have been made):

    Code:
    Private Sub cbo_billet_Change()
    
        Forms![F10_StaffMemberToOffice].lstBilletsAvailable.Requery
    
    End Sub
    
    
    Private Sub cbo_lastName_Change()
    
        Forms![F10_StaffMemberToOffice].lstStaffMembersAvailable.Requery
        
    End Sub
    How do I modify the events/VBA so that a change on the subform will refresh the two listboxes on the main form?

    Thank you,
    EEH

    P.S. For testing purposes, I currently have only three records in the OBS table (RecordIDpk 24, 25, 26). Upon having made selections, you may need to delete any newly created records from the OBS table once
    the two employees (Washington, Redford) have been assigned.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Probably need to first commit subform record to table. Run code to save.

    I would not use Change event - never have. I use AfterUpdate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just tested your set up.
    "In the subform, I select a **new** position from the the "Billet Name" combo. Let's say I select "Financial Manager".
    - This creates a new record in the OBS table and (temporarily) associates staff member "Vacant" to this position."


    No, it does not create a record in the OBS junction table--at least not just when you select Financial Manager.
    The Financial Manager is identified as record 27 on your form, but only 24,25 and 26 are in the table -so 27 has not been added yet, and even after I selected Robert Redforb (but haven't moved off that record) there is no 27 in OBS.
    As soon as I move to another record on the subform, then OBS is updated with the 27 records info.


    I tested with this code ---Note the Purple line and it seems to adjust the Staff Members available list box
    --but I am not familiar with the details of your business or code.

    Code:
    Private Sub cbo_lastName_AfterUpdate()
    
        'Updates date/time stamp when staff member is added
        On Error GoTo cbo_lastName_AfterUpdate_Error
        Me.txt_dateTimeModified = Now()
        Forms![F10_StaffMemberToOffice].Requery 'jed
        On Error GoTo 0
        Exit Sub
    
    cbo_lastName_AfterUpdate_Error:
    
        'Throws error message
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cbo_lastName_AfterUpdate, line " & Erl & "."
    End Sub

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange -- perfect... changes in the subform now result in the listboxes being updated. Thank you!!!

    Cheers,
    Tom

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I often use a public sub to requery listboxes across forms to avoid having to hardcode a particular form reference.
    This allows me to use the same data entry forms with a variety of calling forms and it makes sure that all lists are up to date.
    It can also be called in a wide variety of events.

    Code:
    Public Sub RequeryAllLists()
    
        Dim ctl As Control
        Dim frm As Variant
    
        For Each frm In CurrentProject.AllForms
            If frm.IsLoaded Then
                For Each ctl In Forms(frm.Name).Controls
                    If ctl.ControlType = acListBox Then
                        ctl.Requery
                    End If
                Next
            End If
        Next
    
    End Sub

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Moke123 - thank you... should the afterChange event on the subform evoke this RequeryAllList event? If so, how do I call it?

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange -- quick follow up. The refresh works fine as long as I scroll down and then select n, e.g., name from the employee drop-down in the subform. In the actual database, however, I have thousands of employees. So, I often find myself typing the first few characters of a name and once it shows up in the combo, I then select the name. For some reason, typing name and then selecting the name doesn't refresh the top list boxes on the main form. Is there any reason for it? If a known issue, how could be this avoided so that typing/select a name would result in the refresh of the listbox?

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    should the afterChange event on the subform evoke this RequeryAllList event? If so, how do I call it?
    Its a public sub so the RequeryAllLists code should go in a standard module, not the form module.

    you just need an appropriate form event to call it from your forms. I often call it in the OnClose event of a form or as part of another procedure.

    for instance you could call it in the code you were given by Orange like

    Code:
    Private Sub cbo_lastName_AfterUpdate()
    
        'Updates date/time stamp when staff member is added
        On Error GoTo cbo_lastName_AfterUpdate_Error
        Me.txt_dateTimeModified = Now()
    
        RequeryAllLists                             'call it here instead of Forms![F10_StaffMemberToOffice].Requery 'jed
    
        On Error GoTo 0
        Exit Sub
    
    cbo_lastName_AfterUpdate_Error:
    
        'Throws error message
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cbo_lastName_AfterUpdate, line " & Erl & "."
    End Sub

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    moke123 - thank you for the follow-up. Maybe I'm stilling missing something. Could you pls check out attached db, review, and repost (if necessary w/ additional code).

    Btw, right now, when I select a staff member (based on who's shown in the listbox), it refreshes the listbox. However, once I type the first few characters and then select a name, it doesn't refresh.

    Thanks in advance!
    Tom
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-10-2015, 04:19 AM
  2. Replies: 1
    Last Post: 10-22-2014, 12:44 PM
  3. Replies: 7
    Last Post: 03-21-2014, 05:52 PM
  4. Checkbox in mainform to control subform
    By revned12 in forum Forms
    Replies: 3
    Last Post: 09-09-2012, 02:32 AM
  5. Replies: 1
    Last Post: 06-15-2012, 05:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums