Imported tables with autonumber errors

Hello all,

My Customer table with Autonumbers used as the customer account number. There are two main forms (Order frm, Customer frm); the Customer's account number displays in a bound control on each. All relationships are tied to this account number, it's an Order entry system. The trouble comes in after adding a new customer.

there's a combo control on Customer frm to search a name and if not found, the NotInList function is triggered. The acct number' control is the second control on the Customer frm. Once a new person is successfully added to the table an autonumber 'acct number' is assigned. However, what I've discovered is that somehow, the person is getting 2, 3 autonumbers...and I don't know how this is happening. I found this out when I had difficulty on the Order frm trying to bring up the newly added customer. I couldn't locate the new customer through the combo box on the Order form. So I went into the actual table and found that my newly added (Joe Smoe, acct# 2442) had 2 and sometimes 3 autonumbers (2442, 2443),...I had to delete one of the numbers, in order to bring up the new name. As soon as I have to add another pperson, the same thing all over again. On the Order form, if I make the 'acct number' control unbound, the assigned nnumber from the customer form doesn't come up when you bring up a customers record, only when you make it bound, BUT then, you're assigning again, the customer another number. Help, help

What a mess. I need to maintain the integrity between the imported tables so I don't lose the existing customer info (re: past order history). Does anyone have ideas how to alleviate this? Unfortunately the Customer table (and some of my other tables) were imported with autonumbers already used as Primary Keys).
There are over 2300 Customer existing records so I can't start all over. I tried on a test DB, doing an append query to get the old info into a new db, but lost all the relationships because of new autonumbers.....Help,
anyone? thanks in advance
Here's the 'NotInList' event (if that's the issue??)

Expand|Select|Wrap|Line Numbers

1. Private Sub customername_NotInList(NewData As String, Response Dim strSQL As String
2. Dim FirstName As String
3. Dim LastName As String
4. Dim i As Integer
5. Dim SpacePosition As Integer
6. Dim Msg As String
7. SpacePosition = InStr(NewData, " ")
8.  
9.     'Exit this sub if the combo box is cleared
10.     If NewData = "" Then Exit Sub
11.  
12.     Msg = "'The name," & NewData & "' is not found." & vbCr & vbCr
13.     Msg = Msg & "Do you want to add new customer?"
14.  
15. FirstName = Trim(Left(NewData, SpacePosition - 1))
16.    LastName = Trim(Mid(NewData, SpacePosition + 1))
17.  
18.     i = MsgBox(Msg, vbQuestion + vbYesNo, "New Customer Name...")
19.     If i = vbYes Then
20.         strSQL = "Insert Into Customers ([FirstName], [LastName]) " & _
21.     "values ('" & FirstName & "','" & LastName & "');"
22.         CurrentDb.Execute strSQL, dbFailOnError
23.         Response = acDataErrAdded
24.     Else
25.         Response = acDataErrContinue
26.     End If
27.  
28. End SubAs Integer)
29.  
30.  

Rosie

• RowSource - if it is table post table metadata, if it is a query post SQL, if the query based on another query post SQL of latter query and so on
• BoundColumn
• ColumnWidths
• ColumnCount

Hi, Rosie.

Plz, post additional information concerning combobox "customername"

• RowSource - if it is table post table metadata, if it is a query post SQL, if the query based on another query post SQL of latter query and so on
• BoundColumn
• ColumnWidths
• ColumnCount

Hi, Rosie.

The problem with NotInList event is that your combobox RowSource is not updated after a new customer has been added to the customers table.
This may cause multiple entering of the same customer until the form is not reopened.

To tell the truth I would do it via ADO.Recordset. In the following example I replaced update via SQL with update via Recordset, added combo requery, and make some not critical changes to the rest of the code.

Expand|Select|Wrap|Line Numbers

1. Private Sub customername_NotInList(NewData As String, Response As Integer)
2.  
3.     Dim strSQL As String
4.     Dim FirstName As String
5.     Dim LastName As String
6.     Dim Msg As String
7.     Dim RS As New ADODB.Recordset
8.  
9.     'Exit this sub if the combo box is cleared
10.     If NewData = "" Then Exit Sub
11.  
12.     Msg = "'The name," & NewData & "' is not found." & vbCr & vbCr
13.     Msg = Msg & "Do you want to add new customer?"
14.  
15.     FirstName = Trim(Split(NewData, " ")(0))
16.     LastName = Trim(Split(NewData, " ")(0))
17.  
18.     Response = acDataErrContinue
19.     If (MsgBox(Msg, vbQuestion + vbYesNo, "New Customer Name...")) = vbNo Then Exit Sub
20.  
21.         Response = acDataErrAdded
22.  
23.     With RS
24.          .Open "Customers", CurrentProject.Connection, _
25.             adOpenDynamic, adLockOptimistic
26.         .AddNew
27.         ![FirstName] = FirstName
28.         ![LastName] = LastName
29.         .Update
30.         Me.customername = ![CustomerID]
31.         .Close
32.     End With
33.  
34.     Me.customername.Requery
35.  
36.     Set RS = Nothing
37.  
38. End Sub
39.  

• You are right - subscripts in "Split" should be 0 and 1. Actually these are subscripts of substrings array "Split" function returns.
• Below is commented part of the code dealing with ADO
  
  Expand|Select|Wrap|Line Numbers

  1.     'declares ADO.Recordset object that will be
  2.     'instantiated automatically
  3.     Dim RS As New ADODB.Recordset
  4.  
  5.     '..............................
  6.  
  7.     'calling RS properties/functions
  8.     With RS
  9.         'opens "Customers" table into Recordset RS, using
  10.         'active database ADO connection
  11.         .Open "Customers", CurrentProject.Connection, _
  12.             adOpenDynamic, adLockOptimistic
  13.         'creates new record to the recordset
  14.         .AddNew
  15.         'set values for the fields [FirstName] and [LastName]
  16.         ![FirstName] = FirstName
  17.         ![LastName] = LastName
  18.         'write the record to "Customers table"
  19.         .Update
  20.         'retrieves Autonumber [CustomerID]
  21.         Me.customername = ![CustomerID]
  22.         'closes recordset
  23.         .Close
  24.     End With
  25.  
  26.     Me.customername.Requery
  27.  
  28.     'destroy Recordset object being needed no longer
  29.     Set RS = Nothing
  30.  
  The aim of the code is to retrieve [CustomerID] of a new customer. While it could be done using "INSERT INTO....." and then retrieving [CustomerID] with DMax function, its much more safe to do it via Recordset.

Hi, Rosie.

• You are right - subscripts in "Split" should be 0 and 1. Actually these are subscripts of substrings array "Split" function returns.
• Below is commented part of the code dealing with ADO
  
  Expand|Select|Wrap|Line Numbers

  1.     'declares ADO.Recordset object that will be
  2.     'instantiated automatically
  3.     Dim RS As New ADODB.Recordset
  4.  
  5.     '..............................
  6.  
  7.     'calling RS properties/functions
  8.     With RS
  9.         'opens "Customers" table into Recordset RS, using
  10.         'active database ADO connection
  11.         .Open "Customers", CurrentProject.Connection, _
  12.             adOpenDynamic, adLockOptimistic
  13.         'creates new record to the recordset
  14.         .AddNew
  15.         'set values for the fields [FirstName] and [LastName]
  16.         ![FirstName] = FirstName
  17.         ![LastName] = LastName
  18.         'write the record to "Customers table"
  19.         .Update
  20.         'retrieves Autonumber [CustomerID]
  21.         Me.customername = ![CustomerID]
  22.         'closes recordset
  23.         .Close
  24.     End With
  25.  
  26.     Me.customername.Requery
  27.  
  28.     'destroy Recordset object being needed no longer
  29.     Set RS = Nothing
  30.  
  The aim of the code is to retrieve [CustomerID] of a new customer. While it could be done using "INSERT INTO....." and then retrieving [CustomerID] with DMax function, its much more safe to do it via Recordset.

Regards,

Fish

P.S. You didn't say whether is it working or not.

I'm sorry,,,,,YES it's working great. thanks so much for your help and insight.
take care.
Rosie