New category Problem

[Mr_Adams]

I have a combobox on a form. it gets it data from Table "Departments"
I want the user to be able to enter a new department in the ComboBox and have it open the Department Maintenance Form so that they can add a department to the list.
Right now the code I have wants to open the Department Maintenance Form even if they choose a pre-existing Department.

I would aslo like the portion of the code in RED to display what was actually entered in the ComboBox

Can someone gelp me please

Here is the code I have

Private Sub DEPARTMENT_BeforeUpdate(Response As Integer)
' Add a new category by typing a name in lstCompany combo box.

Dim intNewDepartment As Integer, strTitle As String, intMsgDialog As Integer
Dim strMsg As String

' Display message box asking if user wants to add a new category.
strTitle = "New Department"
strMsg = "Data Entered in ComboBox is not in the Department list. "
strMsg = strMsg & "Would you like to add it?"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewDepartment = MsgBox(strMsg, intMsgDialog, strTitle)
If intNewDepartment = vbNo Then
Response = acDataErrDisplay
Else
'Open the Companies form
DoCmd.OpenForm "Department Maintenance", acNormal, , , acFormAdd, acDialog
End If

End Sub

 

[Mr_Adams]

OK My bone head move
I should have use NotInList instead of BeforeUpdate
And in properties "Limit to List" change to YES
and inproperties "On Not in List" use this event procedure

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

        ' Add a new category by typing a name in DEPARTMENT combo box.
    Dim intNewDepartment As Integer, strTitle As String, intMsgDialog As Integer
    Dim strMsg As String, rst As Recordset, Db As Database, newRst As Recordset
    Dim DepartmentID As Long
        ' Display message box asking if user wants to add a new category.
    strTitle = "New Department"
    strMsg = "'" & NewData & "' is not in the Department list.  "
    strMsg = strMsg & "Would you like to add it?"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewDepartment = MsgBox(strMsg, intMsgDialog, strTitle)
        If intNewDepartment = vbNo Then
            Response = acDataErrDisplay
                Else
                    'Open the Department Maintenance form
                DoCmd.OpenForm "Department Maintenance", acNormal, , , acFormAdd, acDialog
                End If
End Sub