List Box bound to table does not fully reflect updates to table after requery

Question

I have a form that provides a graphical interface for filtering records on a datasheet. I am adding a set of controls that allow a user to save filter configurations so that they can be recalled at a later point. The saved filters are stored in a table; each record has a Nickname (primary key) and a field for the actual SQL statement. The main form will have a list box that displays all of the saved filters by Nickname (see below). The RowSourceof this list box is bound to the Saved Filters table

I want to allow users to change the Nickname and/or SQL statement of a saved filter. To achieve this, the Edit button opens a subform with two text boxes that are populated with the fields from the selected record. When the subform is closed, it asks the user if they want to save the changes to the record (for this reason, the subform and its controls are not bound to the Saved Filters table).

As far as I can tell, the functions that populate the sub for and update the record run smoothly. However, the list box of saved filter Nicknames has a problem. When the Nickname of a record is edited, the change propagates to both the table and the list box. If the user then immediately tries to edit the saved filter again, they run into trouble because the .Value property of the list box still returns the old name. If the user instead selects some other Nickname, then goes back to the first selection and tries to edit it, the sub form opens properly.

I've tried to requery the list box before it looks up the control and that did not solve the problem. I tried to de-select the item in the list box and requery, and that didn't work either. How can I make sure that the list box control updates appropriately when a user changes a saved filter?

Here's my code:

(Parent form)

'Function that is bound to the OnClick event of the Edit button
Private Function sfEdit()

    Dim sfLabel As String
    sfLabel = Me.List_Saved.Value

    If epuModule.OpenEditor(sfLabel) = False Then
        MsgBox "Unable to locate record with name " & Chr(34) & sfLabel & Chr(34), vbCritical
    End If

    Debug.Print Me.List_Saved.Value
    Me.List_Saved.Requery

End Function

([Form_SQL Editor] aka Editor Pop-Up)

Private Sub Form_Close()
    If PromptSave = True Then
        Select Case MsgBox("Do you want to save your changes?", vbYesNo)
            Case vbYes
                sfModule.Edit RecordLabel, Me.tbTerms.Value, NewLabel
            Case vbNo

        End Select
        PromptSave = False
    End If
End Sub

Can you make this question more minimal, e.g. try to replicate it in a clean database? It seems improbable that all that code is relevant. See: How to create a minimal, complete and verifiable example — Erik A

SunKnight0 SunKnight0 · Accepted Answer · 2017-10-16T17:38:30

Move Me.List_Saved.Requery (appropriately replacing "Me" with a global identifier) to your Form_Close after sfModule.Edit RecordLabel, Me.tbTerms.Value, NewLabel. Unless your edit form is modal, it runs before any editing is actually done/saved.

List Box bound to table does not fully reflect updates to table after requery

1 Answers