none
Access 2016 NotInList adds record to table but have to exit form and go back in to use the new record RRS feed

  • Question

  • I can add the new record with the NotInList event but all data from the record that was loaded is still populating the fields. Have to exit the form and reload to use the new record.

    Private Sub CboPlan_NotInList(NewData As String, Response As Integer)
    On Error GoTo MicroSucks
    Dim ans As String, strSQL As String
    Dim db As Database

    Set db = CurrentDb()

    Let ans = MsgBox("Plan " & NewData & " does not exist for company " & strCoNam & vbCrLf & vbCrLf & "Would you like to add it?", vbYesNo)

    If ans = vbYes Then
        Let strSQL = "INSERT INTO tblKEIP ([CompID],[PlanNa]) SELECT " & lngCompID & ",'" & NewData & "';"
        db.Execute strSQL, dbSeeChanges
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

    ExitP:
        DoCmd.SetWarnings True
        Set db = Nothing
        Exit Sub

    MicroSucks:
        MsgBox "Error " & Err.Number & ": " & Error$
        Resume ExitP
        Resume


    End Sub

    Friday, May 12, 2017 10:00 PM

All replies