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
- 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
Expand|Select|Wrap|Line Numbers
- 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
Does anyone know how to fix this? thanks so much....
Rosie