I'm stumped....(newbie):)

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:

Code: ( text )
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



Code: ( text )
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

Is there anyone that can help....thanks Shasha

Private Sub customername_AfterUpdate()
' Dim rst As Object
' Set rst = Me.RecordsetClone
' rst.FindFirst "CustomerID=" & Me![customername]
With Me.RecordsetClone
.FindFirst "CustomerID=" & Me!customername
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
'Not found!
End If
End With
' Forms![Add or Delete Customer]![customername].Requery
End SubThe RecordsetClone is *always* available when a recordset is open and if you use the RecordsetClone as I have, there is no reason to use additional variables which need to be cleared up (Set rst = Nothing) which you did not do anyway. You can simply eliminate all of the code in GREEN.
Another question I have is why do you have the line I put in RED? Is this a different form from where the code is executing? Has the recordset changed so a requery is necessary? Are you simply refreshing the RowSource of the ComboBox you are executing from because of changes made in the NotInList event? If the last is true, the requery of the RowSource of the ComboBox can be taken care of almost automatically in the NotInList event.
That does not answer your main question but I thought I'd give you a few things to think about while I examine the NotInList code. BTW, using code tags as I have preserves the formatting and makes it easier to read code.

While I'm at it, I should stress that it is trivial to put two fields together when you need them that way. It can be a real pain to take one field apart properly when required. I strongly recommend you use at least two fields for a persons full name. Maybe up to 4 for "John A. Smith Jr.".

OK, I see you *are* using a FirstName and LastName field...Good! I should have read further into the next SubRoutine. Is the [custID] field an AutoNumber field? If so then you should *not* be including that field in your Insert code.

BTW, I noticed you are asking the Action query to "dbFailOnError" but you have no error handling code in the SubRoutine. Could be a problem area for you later on.

Rural Guy,

I was able to fix the entire matter. How? By continuing to use my 'NotInList', but I changed my 'After Update' event to this:

Me.FilterOn = True
Me.Filter = "CustomerID = " & [comboboxname]
Me.Requery

That was it! The double entries went away!....

Thanks again for your efforts...(shasha)

Glad to hear you got it sorted.