Form for adding when not on list

[mlubbs]

I have a table – Department
ID
DEPARTMENT
DEPARTMENT_EXPLINATION

I have a table – Employee_Listing
This table includes:
Employee_ID (Primary Key)
LAST_NAME
FIRST_NAME
INITIALS

I have a table – DISCREPANCY_LIST
This table includes:
DATE (Auto completes to current date)
EMPLOYEE_ID (Primary Key) (Brought over from Employee_Listing via form when initials are entered on form)
LAST_NAME (Brought over from Employee_Listing via form when initials are entered on form)
FIRST_NAME (Brought over from Employee_Listing via form when initials are entered on form)
INITIALS
DEPARTMENT
NUMBER_OF_UNITS
NUMBER_OF_DESCREPANCIES
NUMBER_OF_OK_UNITS

I have a form - DISCREPANCY_LIST
This form enters information into the DISCREPANCY_LIST table
The initials combo box is set: Limit to List – Yes and Allow Value List Edits – No
If the user enters initials that are not stored in the Employee_Listing table they receive an error.
Instead of an error I would like for them to receive a message asking if they would like to add these initials to the Employee_Listing table.
If the user clicks Yes, then it will bring up the Employee_Listing form
If the user clicks No, then they are told to enter valid initials
I am having a difficult time reading through the tons of information and finding a solution.
Any help you have to offer would be greatly appreciated.

Michael

 

[apr pillai]

Run the following code from the Combobox's NotinList Event Procedure to add a new entry, entered by the user in the combobox, after taking her permission for adding it to the Employee_List table:

Private Sub cboInitials_NotInList(NewData As String, Response As Integer)
Dim init As String, db As Database, rst As Recordset
Dim msg as string

Response = acDataErrContinue

init = NewData
msg = "Initials: " & init & " Not found!" & vbCr & vbCr & "Do you want to add it in file...?"
If MsgBox(msg, vbDefaultButton2 + vbYesNo + vbQuestion, "cboInitials_NotinList()") = vbYes Then
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Employee_Listing", dbOpenDynaset)
    rst.AddNew
    rst![INITIALS] = init
    rst.Update
    rst.Close
    
    Me!cboInitials = Null
    Me.cboInitials.Requery
    Me![cboInitials] = init

    
    Set rst = Nothing
    Set db = Nothing
Else
   Response = acDataErrDisplay
End If

End Sub

Make changes in the code wherever modification (like the combobox name) required.