Combo Box

[Fgrill220]

Happy Holidays All!

I have a form called Projects with a combo box that has a list of names from another table Project Managers, how can I add a new name to the table without leaving the current form?

Thanks

 

[Corticus]

The best way to do that is to use the not_in_list event.

If you right click on the combo, and go to the code builder, you can select (from the top right list of events) the not_in_list event. This will fire whenever the user tries to type something into the combo that's not in the list.

At this point, you can use a little piece of dynamic SQL to add the value to the table to which this combo looks.

The basic idea is this:

Private Sub cmbContact_NotInList(NewData As String, Response As Integer) 

    Select Case MsgBox("Add new contact?", vbYesNo) 
        Case vbYes 
            insSQL = "INSERT INTO tblContact( contact ) " & _ 
                     "VALUES ( """ & NewData & """ );" 
            DoCmd.SetWarnings False 
            DoCmd.RunSQL (insSQL) 
            DoCmd.SetWarnings True 
            Response = acDataErrAdded 
        Case vbNo 
            Me.Undo 
            Response = acDataErrContinue 
    End Select
End Sub

Your combo will have a different name. Also, in the SQL statement:
INSERT INTO tblContact( contact )
'contact' is the name of the field in your table to which you will be adding the new data you've just tried to type into the combo.

If you can, take the spaces out of your names, that's goint to cause troubles down the road.

 

[Fgrill220]

I get an compile error - variable not defined and highlights insSQL= here is the code:

Private Sub PManager_ID_NotInList(NewData As String, Response As Integer)

Select Case MsgBox("Add new contact?", vbYesNo)
Case vbYes
insSQL = "INSERT INTO Project Managers( PM Name ) " & _
"VALUES ( """ & NewData & """ );"
DoCmd.SetWarnings False
DoCmd.RunSQL (insSQL)
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
Me.Undo
Response = acDataErrContinue
End Select

End Sub

 

[Corticus]

You have Option Explicit set, whcih means any variables used must be declared.

Just change:

insSQL = "INSERT INTO Project Managers( PM Name ) " & _ 
"VALUES ( """ & NewData & """ );"

to:

insSQL$ = "INSERT INTO Project Managers( PM Name ) " & _ 
"VALUES ( """ & NewData & """ );"

The little $ lets Access know that insSQL is a variable, and of the string variety.

You could also add:
Dim insSQL as string
or
Dim insSQL$