MS Access not in list event

[Tom Orr]

I'm new to Access programming. I followed Microsoft online instructions for the notinlist event. The code goes from an order form combobox client name to a data entry client form when the user selects a client not in the list. After entering the new client info and closing that form, the focus(?) returns to the client-name order form combobox. The new client information does not appear on the list. I have to manually clear the combobox, close and then reopen the order form. The new client information then appears.

When attempting to requery the combo box, an error message appears that the "field must be saved before requerying."

Thanks

[TheSmileyCoder]

I don't know what the Microsoft example looks like but this is some of my own code:

Private Sub cmb_Document_NotInList(NewData As String, Response As Integer)
    If vbYes = MsgBox("The document [" & Me.cmb_Document.Text & "] could not be found." & vbNewLine & "Would you like to register it now?", vbYesNo + vbQuestion) Then
        DoCmd.OpenForm "frm_DocumentRegistration", acNormal, , , acFormAdd, acDialog, Me.cmb_Document.Text & ""
        If IsNull(Me.cmb_Document) Then
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
        Else
        Me.cmb_Document = Null
        Response = acDataErrContinue
    End If
 
End Sub

Notice that the form is opened as dialog (Which means that the code following on the next line is not executed until after the dialog is closed!

This is the code from the 2 buttons on the dialog form:

Private Sub btn_Cancel_Click()
    Me.Undo
    DoCmd.Close
    Screen.ActiveControl.Undo
    Screen.ActiveControl = Null
 
End Sub

If user doesn't want to register document after all, the last 2 lines will clear the field so user doesn't get another not-in-list event.

Private Sub btn_Save_Click()
    Me.Dirty = False 'Save record
    Dim intKey As Integer
    intKey = Me.tb_KEY_Document 'Record the autonumbered key of the document
    DoCmd.Close 
    Screen.ActiveControl.Undo 'Clear so we dont get errors
    Screen.ActiveControl.Requery 'Refresh list
    Screen.ActiveControl = intKey 'Set to the newly created document
End Sub

The Screen.ActiveControl.Undo will stop the error message you are receiving.


This example is based on a combobox where a primary key is used.For example:

SELECT tbl_Documents.KEY_Document, tbl_Documents.tx_Document_D3_ID, tbl_Documents.tx_DocumentName FROM tbl_Documents;

[Tom Orr]

Thank you very much!! It was the "undo" action that did the trick. Once the combobox was cleared, the order-entry form could be requeried with the updated client information. I'm learning that Access is very powerful but also very quirky. Thanks again.

[TheSmileyCoder]

Happy to help. Welcome to Bytes