VBA adding a duplicate record without event

[John Nelson]

I have relatively simple subform that allows the user to check an order's configuration of part numbers and their quantities. I set an INSERT INTO statement on the part number combo box to the "NotInList" event and everything seems to be working correctly except that when the focus leaves the Qty field VBA inserts a second record of the same information just added. There are no other events set up for the subform and I can't for the life of me figure out for the life of me why it's doing it. Any ideas?

Private Sub PartNumber_NotInList(NewData As String, Response As Integer)
Dim strSQL As String, msg As String, msgResponse As Integer

'Check to see if part is in tblSeatAssembly
If IsNull(DLookup("[SeatAssemblyID]", "tblSeatAssembly", "[PartNumber]='" & NewData & "'")) Then
msg = NewData & " is not in the Seat Assembly list. Would you like to add it?"
msgResponse = MsgBox(msg, vbYesNo)
    Select Case msgResponse
        Case vbYes
        strSQL = "INSERT INTO tblSeatAssembly (PartNumber) SELECT '" & NewData & "'"
        Debug.Print strSQL
        CurrentProject.Connection.Execute strSQL
        Response = acDataErrContinue
        Case vbNo
        Me.PartNumber.Undo
        Response = acDataErrContinue
        Exit Sub
    End Select
End If
    


Dim intCustCode As Integer, intSeatID As Integer, intCount As Integer

intCustCode = Forms!frmOrder!cboCustomerCodeID
intSeatID = DLookup("[SeatAssemblyID]", "tblSeatAssembly", "[PartNumber]='" & NewData & "'")
intCount = Nz(DCount(Nz(intSeatID, 0), "tblCustomerCodeDetail", "[CustomerCode]=" & intCustCode), 0)

'Check the count in tblCustomerCodeDetail of the SeatAssemblyID to avoid adding duplicates
'**Doesn't work correctly - count is always 0
If Not intCount >= 1 Then
    Debug.Print intCount & " " & intCustCode & " " & intSeatID
    strSQL = "INSERT INTO tblCustomerCodeDetail (SeatAssemblyID, CustomerCodeID) SELECT " _
        & intSeatID & ", " & intCustCode

    Debug.Print strSQL
    CurrentProject.Connection.Execute strSQL

End If

Me!PartNumber.RowSource = "SELECT qryCustomerCode.PartNumber, qryCustomerCode." & _
"SeatAssemblyID FROM qryCustomerCode ORDER BY qryCustomerCode.PartNumber;"

Me!PartNumber = intSeatID
Me!Qty.SetFocus
'when focus leaves Qty it's adding a second record of the same info to tblCustomerCodeDetail
'executing strSQL again?
    
Response = acDataErrContinue

End Sub

 

[WayneRyan]

John,

The only comment that I have is that you must have the Master-Child
relationship set. Would need to see the DB to confirm, but with no
Events, that would be the logical place to look.

Wayne

 

[John Nelson]

Hey Wayne, thanks for the reply. I actually have a child/master set up. It's a field called CustomerCodeID that ties in the customer's configuration and pulls from the query (the form is based off of) the information specific to each customer. I'm not sure that's the problem, because the form updates okay based on the child/master.