NotInList causing duplicate entries

[Guest]

I have two forms - frmPurchases and frmProducts.
On frmPurchases I have a combo for ProductID - Limit to List set to Yes
I have written the following code (copied from Microsoft KB Article 161007)
to open frmProducts, display the entry I've started making in ProductName so
I can complete the rest of the form:

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

Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmProducts", acNormal, , , acFormAdd, acDialog,
NewData
End If
Result = DLookup("[ProductName]", "tblProducts", "[ProductName]='" &
NewData & "'")
If IsNull(Result) Then
Response = acDataErrContinue
MsgBox "Please try again!"
Else
Response = acDataErrAdded
End If
End Sub

I then click on a command button to return to frmPurchases.
My problem is when I return to frmPurchases, I find two entries for the new
product - one is the partial one as I originally entered it and the other is
the complete one with AddressID etc. filled in.

When I look in tblProducts, it has both these entries as new records.

Where am I going wrong?

 

[Wayne Morgan]

I don't see a problem with what you have posted. The pop-up form should only
be entering data into the same record source as the combo box's Row Source.
If you are entering more than that, you may be causing a problem. I'm not
quite sure by your description of the "duplicate entry" as to where it is.
Are there two entries for the item in the drop down of the combo box?

Reference your line "CR = Chr$(13)", VBA has a built in, predefined constant
that you may want to use instead. It is vbCrLf, for Carriage Return/Line
Feed. You could eliminate the CR= line and just use vbCrLf where you are now
concatenating in CR.