Item added during "not in list", combo box display, form does not jump to record

[Kryst51]

Item added during "not in list", combo box display, form does not jump to record

I have a combo box, this combo box uses the "Not In List" event to add a record to a table if that record is not already entered. The event works great, but after it is over, the combo box displays the new number but doesn't make the form jump to that record, it goes to the first record in the table. I am just beginning to make use of this event, so I don't fully understand how it works, so any help is appreciated. The form is bound to the table.

Here is my combo box After update event code:

Private Sub cmbInvoiceLookup_AfterUpdate()
On Error GoTo cmbInvoiceLookup_ErrHandler:
 
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[pkInvoiceID] = " & Str(Nz(Me![cmbInvoiceLookup], 0))
    Me.InvoiceNumber.Visible = True
    Me.SalesOrderNumber.Visible = True
    Me.cmbSalesman.Visible = True
    Me.cmbCustomerID.Visible = True
    Me.cmbCustomerName.Visible = True
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
Exit Sub
 
cmbInvoiceLookup_ErrHandler:
    MsgBox Err.Number & ", " & Err.Description
    Exit Sub
 
End Sub

Here is the Not in List event code:

Private Sub cmbInvoiceLookup_NotInList(NewData As String, Response As Integer)
On Error GoTo cmbInvoiceLookup_NotInList_Err
 
    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 = "Invoice: '" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Invoice is not in list...")
    If i = vbYes Then
        strSQL = "INSERT INTO tblInvoices([InvoiceNumber]) " & _
                 "values (" & NewData & ");"
        'Debug.Print strSQL
 
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        Response = acDataErrAdded
        DoCmd.SetWarnings True
 
    Else
        Response = acDataErrContinue
    End If
 
cmbInvoiceLookup_NotInList_Exit:
    Exit Sub
 
cmbInvoiceLookup_NotInList_Err:
MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error"
Resume cmbInvoiceLookup_NotInList_Exit
End Sub

 

[vbaInet]

Re: Item added during "not in list", combo box display, form does not jump to record

After you add to the list try requerying the combo box, set the value to the newly added record, then call the After_Update event.

 

[Kryst51]

Re: Item added during "not in list", combo box display, form does not jump to record

I think it already does that though (Edit: go back to the after update event that is), because some text boxes that are coded to become visible only after it moves to the record become visible, which leads me to believe that it does run, it just doesn't move to the record in the combo box.

I've tried requerying the combo box, but anywhere I put that code AFTER I run the sql to add the number to the table just causes my message box to pop up again and I can keep adding the same number to the table, which is definitely what I don't want.

I tried calling the after update event after the run sql code, but it does the same thing as just requerying the combo box does.

 

[vbaInet]

Re: Item added during "not in list", combo box display, form does not jump to record

You will need to acDataErrContinue as the very first thing before you run the INSERT.

 

[Kryst51]

Re: Item added during "not in list", combo box display, form does not jump to record

You will need to acDataErrContinue as the very first thing before you run the INSERT.

btw, Thank you for you help.

I put that in, but it still didn't work, what's interesting, (which I just realized) is after the code runs, if I click on the combo box list, and pick a record that was already there the combo box goes to that record, BUT, if I then try to go to the newly entered record it will go to the FIRST record in the table, not the record I clicked on. Then if I close the form and reopen it will find that record if clicked. Its almost like it needs to be reset.

 

[vbaInet]

Re: Item added during "not in list", combo box display, form does not jump to record

See if this sample Not In List db helps.

 

[Kryst51]

Re: Item added during "not in list", combo box display, form does not jump to record

Hey! Thanks for the example, but I am not sure that it applies to my case. The combo box was originally designed using the wizard to look up a record on my current form... I don't want to pop up a new form to enter the information. I want to add the new number to my combo box, and then move the form the combo box is on to that new record.

But one of your original suggestions did! It's amazing what sleeping on something will do for perspective. I put a requery in the after update event BEFORE it does the recordset clone, and that worked Beautifully. THANK YOU SO MUCH, for all of your time and suggestions.

 

[vbaInet]

Re: Item added during "not in list", combo box display, form does not jump to record

Great! Glad you got that sorted.