By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

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

imrosie
100+
P: 222
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.recordsetclone' 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub customername_AfterUpdate()
  2. Dim rst As Object
  3.  
  4. Set rst = Me.RecordsetClone
  5.  
  6. rst.FindFirst "CustomerID=" & Me![customername]
  7. If Not rst.NoMatch Then
  8.  
  9.   Me.Bookmark = rst.Bookmark
  10. Else
  11.   'Not found!
  12. End If
  13. Forms![Add or Delete Customer]![customername].Requery
  14. End Sub
  15.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub customername_NotInList(NewData As String, Response As Integer)
  2. Dim strSQL
  3. Dim FirstName As String
  4. Dim LastName As String
  5. Dim SpacePosition As Integer
  6. Dim lngNextID As Long
  7.  
  8.     ' Find the highest Cust ID in the Customers table and add 1
  9.     lngNextID = DMax("[CustID]", "Customers") + 1
  10.     custID = lngNextID
  11.  
  12.    SpacePosition = InStr(NewData, " ")
  13.     If SpacePosition = 0 Then
  14.         MsgBox "Your entry requires a space to separate First and Last name." & _
  15.                vbNewLine & vbNewLine & _
  16.                "Please enter a First and Last Name or choose an entry from the list.", _
  17.                vbInformation, "Invalid Data !"
  18.         Response = acDataErrContinue
  19.         Exit Sub
  20.     End If
  21.  
  22.     ' Trim the data into first and last name using the space position.
  23.     FirstName = Trim(Left(NewData, SpacePosition - 1))
  24.     LastName = Trim(Mid(NewData, SpacePosition + 1))
  25.  
  26.     If FirstName = "" Then
  27.         MsgBox "You have not entered details for the first name" & vbNewLine & vbNewLine & _
  28.                "Please fix entry.", vbInformation, "Invalid Data !"
  29.         Response = acDataErrContinue
  30.         Exit Sub
  31.     End If
  32.  
  33.     If LastName = "" Then
  34.         MsgBox "You have not entered details for the last name" & vbNewLine & vbNewLine & _
  35.                "Please fix entry.", vbInformation, "Invalid Data !"
  36.         Response = acDataErrContinue
  37.         Exit Sub
  38.     End If
  39.  
  40.     MsgBox "An account for this person does not exist....." & vbNewLine & vbNewLine & _
  41.            "Now creating new Customer Account.", vbInformation, "Unknown Customer Details....."
  42.     strSQL = "Insert Into Customers ([custID], [FirstName], [LastName]) " & _
  43.     "values ('" & custID & "','" & FirstName & "','" & LastName & "');"
  44.     'MsgBox strsql
  45.     CurrentDb.Execute strSQL, dbFailOnError
  46.     Response = acDataErrAdded
  47.  
  48. End Sub
  49.  
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
Jul 30 '07 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,442
Have you managed to get anywhere with this yet Rosie?
Aug 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.