Refresh a form after VBA change

[Jeff H]

Hi Hans. Regarding this code you offered me recently:

Code: Select all

Private Sub SelectProgram_NotInList(NewData As String, Response As Integer)
    If MsgBox("""" & NewData & """ does not occur in the list of programs." & vbCrLf & _
            "Do you want to add it?", vbYesNo + vbQuestion) = vbYes Then
        CurrentDb.Execute "INSERT INTO Programs (ProgramName) VALUES(""" & NewData & """)", dbFailOnError
        Response = acDataErrAdded
        
    Else
        MsgBox "Please select a program from the list!", vbExclamation
        Response = acDataErrContinue
    End If
End Sub

It's an excellent feature I want to keep, but I think it needs a line to refresh the form after adding the record to the Programs table. I looked up acDataErrAdded, but I couldn't really see how to update the form from their examples.

The normal action is that a Program is selected in the Select Program unbound combo box which populates the Program Name text box and activates that record in the form and subform. When a Not-In-List title is typed into the combo box, and the above code runs, the new title is added to the Program table but nothing changes in the form so the Viewings subform is not available.

Thanks,
- Jeff

[HansV]

It would actually be better not to use the On Not In List event of the combo box. Use the combo box ONLY to jump to an existing program.
If you want to add a new program, use the "New Program" button and enter the name in the Program Name text box in the Detail section of the form.

[Jeff H]

Ok. Too bad. I liked the two options, but I can see there are technical issues.

Thanks again for the overall set up you gave me which works great and includes a lot of hints for follow up in my efforts to gain some insight into Access.

- Jeff