Please Help....Getting double entries using recordset (AfterUpdate)

[imrosie]

Please help with this one,,,,,I've been trying everything in my arsenal to fix this one. I'm stumped....

I"ve got a unbound combo box (customername) that has two events (on click); AfterUpdate and NotInList.

The AfterUpdate uses a 'me.recordsetcl one' in order to display other data in controls associated with customerid/customername from the Customer table.

The NotInlist, does just that, adds a new customer name to the
list along with a new autonumber (used as customerid).

The problem is that I'm getting two entries (different autonumbers). I can't figure out what I'm doing wrong. If I remove the 'AfterUpdate' event, then I can't bring up the customer's data, since then there's no connection to the customername/customerid.

If I remove the Not in List, then there's no way to add a new customer. I reconfigured NotInList many many times and still I get 2 entries. One entry has Customer 'name only', The second entry has name, address, city and the rest of the fields as entered.

I tried setting an index (on Customer table) with CustID and CustomerID, making them unique, but still I'm getting 2 entries whenever I use the 'NotInList'.... ......Help Help,,

Here are these events:

Private Sub customername_AfterUpdate()
Dim rst As Object
 
Set rst = Me.RecordsetClone
 
rst.FindFirst "CustomerID=" & Me![customername]
If Not rst.NoMatch Then
 
  Me.Bookmark = rst.Bookmark
Else
  'Not found!
End If
Forms![Add or Delete Customer]![customername].Requery
End Sub
 

Private Sub customername_NotInList(NewData As String, Response As Integer)
Dim strSQL
Dim FirstName As String
Dim LastName As String
Dim SpacePosition As Integer
Dim lngNextID As Long
 
    ' Find the highest Cust ID in the Customers table and add 1
    lngNextID = DMax("[CustID]", "Customers") + 1
    custID = lngNextID
 
   SpacePosition = InStr(NewData, " ")
    If SpacePosition = 0 Then
        MsgBox "Your entry requires a space to separate First and Last name." & _
               vbNewLine & vbNewLine & _
               "Please enter a First and Last Name or choose an entry from the list.", _
               vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If
 
    ' Trim the data into first and last name using the space position.
    FirstName = Trim(Left(NewData, SpacePosition - 1))
    LastName = Trim(Mid(NewData, SpacePosition + 1))
 
    If FirstName = "" Then
        MsgBox "You have not entered details for the first name" & vbNewLine & vbNewLine & _
               "Please fix entry.", vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If
 
    If LastName = "" Then
        MsgBox "You have not entered details for the last name" & vbNewLine & vbNewLine & _
               "Please fix entry.", vbInformation, "Invalid Data !"
        Response = acDataErrContinue
        Exit Sub
    End If
 
    MsgBox "An account for this person does not exist....." & vbNewLine & vbNewLine & _
           "Now creating new Customer Account.", vbInformation, "Unknown Customer Details....."
    strSQL = "Insert Into Customers ([custID], [FirstName], [LastName]) " & _
    "values ('" & custID & "','" & FirstName & "','" & LastName & "');"
    'MsgBox strsql
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
 
End Sub
 

CustID is NOT an autonumber. I've also done the After Update with and without the '.Requery', it doesn't make a difference.
Does anyone know how to fix this? thanks so much....
Rosie

[NeoPa]

Have you managed to get anywhere with this yet Rosie?