Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combo Finder does not find record after Not In List procedure

Combo Finder does not find record after Not In List procedure

Combo Finder does not find record after Not In List procedure

My Combo10 correctly finds records it its form.

This NotInList event correctly adds any new Customer entered in the combo.

CODE -->

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

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "New customer...")
        If i = vbYes Then
          strSQL = "Insert Into Customers ([Customer]) values ('" & NewData & "')"
                CurrentDb.Execute strSQL, dbFailOnError
                Response = acDataErrAdded
                Response = acDataErrContinue
        End If

End Sub 

When I select the new Customer from the combo's drop-down list the form doesn't update and open a new record for this Customer, but stays where it was before the NotInList fired.

This is the combo's AfterUpdate, where I've tried forcing a save and turning any filter off. They don't resolve the problem, but closing and reopening the form does.

CODE -->

Private Sub Combo10_AfterUpdate()
On Error GoTo Combo10_AfterUpdate_Err

    If Me.Dirty Then Me.Dirty = False
    Me.FilterOn = False
    DoCmd.SearchForRecord , "", acFirst, "[CustomerID] = " & Str(Nz(Screen.ActiveControl, 0))
    Me.Combo10 = Null
    Exit Sub

    MsgBox Error$
    Resume Combo10_AfterUpdate_Exit

End Sub 

RE: Combo Finder does not find record after Not In List procedure

Are you requerying the recordset driving the combo box and form after you add the new customer to the table? If your combo box "Limit to List" field is set to false (which it usually is by default) the it will let you put whatever you want in there even if it isn't necessarily in the dataset for the combo box yet. I.e. your combo box might let you put the new customer name in there even though it doesn't recognize that it's within it's own dataset, but that would cause the rest of your updating to fail.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close