Running AfterUpdate after adding to combo box

Hello,

I have the following code and I wanted to have the NOTInLIst EVent fire the AFterUpdate event after my form opens saves the new record and closes.

How can I call this procedure?  I have put it in a couple of different places but nothing works.  What can I do?

Private Sub TWIC_Number_AfterUpdate()
Me.FName = DLookup("FName", "qryVGSearchTruckDriver1")
Me.LName = DLookup("LName", "qryVGSearchTruckDriver1")
End Sub



Private Sub TWIC_Number_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = Chr$(13)
 
    ' Exit this subroutine if the combo box was cleared.
If NewData = "" Then
    Exit Sub
 
    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Customers form in data entry
       ' mode as a dialog form, passing the new company name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "frmVGTRuckDriver1Srch", , , , acAdd, acDialog, NewData
    End If
 
    ' Look for the customer the user created in the TruckDriver1Srch form.
    Result = DLookup("[TWIC_Number]", "MGNameAddressPhone", _
             "[TWIC_Number]='" & NewData & "'")
             Me.TWIC_Number.Requery
             Call TWIC_Number_AfterUpdate
    If IsNull(Result) Then
       ' If the customer was not created, set the Response argument
       ' to suppress an error message and undo changes.
       Response = acDataErrContinue
       ' Display a customized message.
       MsgBox "Please try again!"
    Else
       ' If the customer was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
       Call TWIC_Number_AfterUpdate
    End If
End If
Call TWIC_Number_AfterUpdate

End Sub
Ernest GroggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
You are making this too complex by having it bass-ackward.

Here's how mine goes
Dim db As Database
Dim rs As Recordset
Dim lngOwnerID As Long 'this is the PK of the table with the newly created value

'ask if they want to add the new customer.
'if they answer YES, what the entered will be added and the clients form opened to add in all the additional details
If vbYes = MsgBox("'" & StrConv(NewData, vbProperCase) & "' is not entered as a current Client." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
set db = currentdb
Set rs = db.OpenRecordset("SELECT * FROM [tblClients] WHERE 1=2;", dbOpenDynaset, dbSeeChanges) 'recordset with no records
With rs
        .AddNew
        ![Client Name] = StrConv(NewData, vbProperCase)
        .Update
        .Bookmark = .LastModified
        lngOwnerID = ![Client ID]
    End With
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    DoCmd.OpenForm "frmClients", , , "[Client ID]=" & lngOwnerID 'open the form to add more detail
    
    DoCmd.SelectObject acForm, "frmClients"
    DoCmd.Maximize 'maximize the clients form
    response = acDataErrAdded 'clear the NotINList error
Else
    response = acDataErrContinue 'keep the error and the user will back out of the value they added themselves if it was a typo
End If

Open in new window


Done this way, the value gets added to the combo box through the NotInList event, while the user becomes responsible for punching the rest of the detail into the clients form.

Done your way, you are passing the NewData to another form, having the value added there, trying to acertain whether the user actually added it, clearing the NotInList error, requerying the combobox and setting the combobox to the new value.  That's working way too hard!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ernest GroggAuthor Commented:
Sorry I have not replied for a while but was working out the code with how I have the form.

Since my firm was for another table and the cboBox was pulling from the Clients table I needed to make sure I could use it correctly.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.