MS Access Combobox not taking a value after requery shows value

Question

I have Combobox that lets users either edit the related values for this field - cboBEA using a button. I decided to use a NotInList routine whenever they want to ADD a new value. The edit part works without a hitch, but the NotInList part needs to accept a value after providing related fields in a popup form called frmBEA_JDIR

At first the requery of cboBEA wasn't working, so I did a more deliberate resetting of the rowsource by first setting it to "" then the actual SQL of the rowsource.

Here is the code in the "Save" button of the popup form frmBEA_JDR

Private Sub cmdSave_Click()

    Select Case Me.OpenArgs
    Case "Edit"
        DoCmd.Save
        DoCmd.Close acForm, "frmBEA_JDIR"

        With Form_sfm_AddSPDistro
        .cboBEA.Requery
        .cboBESA.Requery
        .cmbPROGRAM.RowSource = ""
        .cmbPROGRAM.RowSource = "SELECT * FROM qLU_BEA_JDIR;"
        .cmbPROGRAM = .cboBEA
        End With


    Case "AddNew"


        Dim strSQL As String

        strSQL = "SELECT LU_BEA_JDIR.ID, LU_BEA_JDIR.BEA, LU_BEA_JDIR.BESA, LU_BEA_JDIR.ORGANIZATION " _
                   & "FROM LU_BEA_JDIR;"


        With Form_sfm_AddSPDistro
        'cboBEA.Requery doesn't work, so...
        .cboBEA.RowSource = ""
        .cboBEA.RowSource = strSQL

        .cboBESA.Requery
        .cboBEA.Value = Me.txtBEA
        .cmbPROGRAM.RowSource = ""
        .cmbPROGRAM.RowSource = "SELECT * FROM qLU_BEA_JDIR;"
        .cmbPROGRAM = .cboBEA
        End With

        DoCmd.Close acForm, "frmBEA_JDIR"

    End Select

End Sub

Here is the NotInList event of the calling form:

Private Sub cboBEA_NotInList(NewData As String, Response As Integer)
    Dim MsgBoxAnswer As Variant

    Response = acDataErrContinue
    Me!cboBEA.Undo  'Used this to prevent the requery error caused by frmBEA_JDIR

    MsgBoxAnswer = MsgBox(NewData & " is not in the list.  Do you want to add it?", vbQuestion + vbYesNo, "Add " & NewData & "?")

    If MsgBoxAnswer = vbNo Then
        Me.cboBEA = Null
        DoCmd.GoToControl "cboBEA"
    Else

        DoCmd.OpenForm "frmBEA_JDIR", acNormal, , , acFormAdd, , "AddNew"
        Form_frmBEA_JDIR.txtBEA = NewData


    End If

End Sub

So depending on what calls this form - the NotInList or the Edit, I put it in the openargs parameter that calls frmBEA_JDIR. This is how I handle the update in the SAVE button. Again, the edit part works perfectly, but the AddNew from the NotInList event just won't populate cboBEA even after it is requeried and I can see the new value in it.

This article shows how to use the NotInList event to open a form to enter new record and then populate combobox with new value blueclaw-db.com/access_notinlist_advanced_example.htm — June7

wazz wazz · Accepted Answer · 2018-03-16T19:20:00

In brief: Instead of figuring out what to do in the popup with openargs, let acFormAdd do it for you; acFormAdd will open the form to a new record.

Send the new data in openargs.

Open frmBEA_JDIR in dialog mode. It stops the current code until the opened form is closed.

' open frmBEA_JDIR in dialog mode to add the new data.
DoCmd.OpenForm "frmBEA_JDIR", acNormal, , , acFormAdd, acDialog, "NewData"

'data is added, now requery the dropdown
cboBEA.Requery

Fool around with this without the 'case edit' section until you see it working. Then add the edit part using acFormEdit. You can check the datamode of the popped-up form to see if it's add or edit.

MS Access Combobox not taking a value after requery shows value

1 Answers