ComboBox to pop-up form to add data

[Jantzev]

Hello gurus! i've been playing around with this for a while and after much searching I'm officially stumped. Okay, a little background: I'm very new to VBA but enjoy the experience of learning the method behind the coding and the language. I'm working on a db that will track students for a summer camp. One of my fields in my student records (CamperInfo) is "School". Currently, the field is set up as a combobox in the CamperInfo Subform in the FamilyInfo form. I would like the combobox to be able to add schools if the school is not listed by a pop-up when the "add school" (or whatever) is selected. I've played around with this bit of coding: (please no flames, I'm new! )

Private Sub School_AfterUpdate()
On Error GoTo Err_School_AfterUpdate
    Dim strForm As String
    If Me.School = "Schools" = True Then
    strForm = "Schools"
    DoCmd.OpenForm Me.School
Err_School_AfterUpdate:
    MsgBox Err.Description
End If
End Sub

As you might have guessed, it doesn't work. no errors, it just doesn't do anything. I added the "Schools" data to my table based on one post I studied but that didn't work like I had hoped. Any advice or help you can give me would be much appreciated.

thank you all!

J

 

[David R]

I think you'll find the Not In List property useful for this.

 

[Mihail]

Agree with David.
Take a look at this thread. Someone have had the same problem not long time ago.

 

[Jantzev]

Hey, I wanted to thank all of you for your help. I finally got it to work for the most part (it adds to the list but does not give an option to add additional information that would be on the form) which is perfectly okay. I'm happy with the results !

Here is the code that finally fixed it:

Private Sub School_NotInList(NewData As String, Response As Integer)
    Dim strTmp As String
 
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new school?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
 
           strTmp = "INSERT INTO Schools ( School ) " & _
            "SELECT """ & NewData & """ AS School;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
 
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

It adds a pop-up box to verify that I want to add the information and then once I click "Okay" it adds the data to the correct table.

I tried to transpose other code that I saw on the sites given but nothing else seemed to work, most likely due to my limited programming knowledge. If you have any suggestions based on my code above I continue to appreciate your help!

Sincerely,

J