Solved Trigger the Not-In-List Event to add the record on the same from

[raziel3]

I know how to open "another" form to add the record if it is not-in-the-list of a combo box but how do I move to a new record on the SAME from that has the combo box.

I have a combo box that selects existing items and once selected it loads the form with all the details but if the item is not-in-the-list, I want to be able to go to a new record and take the unlisted item from the combo box and place it in one of the detail fields. This is what I have so far but, the form is not moving to the new record. It is moving to the record once it's on the list though.

Private Sub cboSBN_AfterUpdate()

Dim UPPNAME As String
Dim strINAME As String
Dim Answer As Integer

If DCount("*", "Product", "ITEMNAME = '" & Me.cboSBN & "'") <> 0 Then
    Answer = MsgBox("Product already exist. View?", vbYesNo, "Duplicate Record!!")
        If Answer = vbYes Then
            UPPNAME = Me.cboSBN
            strINAME = "INAME='" & Me.cboSBN & "'"
            Me.Recordset.FindFirst strINAME
        ElseIf DCount("*", "Product", "ITEMNAME = '" & Me.cboSBN) & "'" = 0 Then
            DoCmd.GoToRecord , , acNewRec
            Me.cboSBN = Me.ITEMNAME    <---Detail field #1
            Me.txtTestUPC = Me.UPC  <----Detail Field #2
        End If
End If

End Sub

 

[June7]

I am confused. What is datasource of combobox and datasource of form? If this form is to show details of item selected in combobox, why would there be another form?

A control used to select search criteria should be UNBOUND

Why do you do the DCount() again?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[arnelgp]

you add code to NotInList of the combo, not on AfterUpdate event.

 

[raziel3]

I am confused. What is datasource of combobox and datasource of form? If this form is to show details of item selected in combobox, why would there be another form?

A control used to select search criteria should be UNBOUND

Why do you do the DCount() again?

If you want to provide db for analysis, follow instructions at bottom of my post.

Yes the combo is unbound and I was just experimenting doing the DCount after the ElseIF.

The datasource of the form is the products table.

 

[June7]

When item is not in list, the AfterUpdate does not trigger because LimitToList is set to Yes.

Your If Then logic is wrong. Have you step-debugged? Consider:

Private Sub cboSBN_AfterUpdate()
With Me
If DCount("*", "Product", "PNAME = " & SQLQuote(.cboSBN) & "") <> 0 Then
    If MsgBox("Product already exist. View?", vbYesNo, "Duplicate Record!!") = vbYes Then
        .Recordset.FindFirst "PNAME='" & .cboSBN & "'"
    Else
        .cboSBN.Undo
    End If
ElseIf Not IsNull(.cboSBN) Then
    DoCmd.GoToRecord , , acNewRec
    .Item = .cboSBN
End If
.cboSBN = Null
End With
End Sub

 

[raziel3]

That did it. Thanks @June7