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

Imported tables with autonumber errors

imrosie
100+
P: 222
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
Aug 23 '07 #1
Share this Question
Share on Google+
7 Replies


FishVal
Expert 2.5K+
P: 2,653
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
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
Aug 23 '07 #2

imrosie
100+
P: 222
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
FishVal,

Hopefully the following will help....I've been digging through the code and I think my problem with the Order form is this....it's based on a query called qryOrderSource:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Customers.FirstName, Customers.LastName, Orders.OrderDate, Orders.ShipDate, Orders.FreightCharge, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipZIPCode, Orders.ShipCountry, Customers.CompanyName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ZIPCode, Customers.Country, Customers.ContactTitle, Orders.ShipPhoneNumber, Orders.ShipFaxNumber, Orders.PurchaseOrderNumber, Customers.Notes
  2. FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
I think the'inner join' is preventing my being able to bring up a new customer on the Order form because the new customer ......simply doesn't have an order yet.
Maybe I'm wrong...don't know. I have no problem bring up an existing customer that has ordered before.

The combo box is unbound on both the Order Form and on the Customer Form.
Here's the source control:
Expand|Select|Wrap|Line Numbers
  1. SELECT Customers.CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName]; 
As for the Customer form (with customername control)....I believe now that the customername (the first control encountered) when the form refreshes (OnCurrent()). If so, all the other controls in the form are still blank. Hence your VBA is adding a record with only the customername populated. After hitting <Enter> after the last field, you're again adding a record, this time with all fields populated.

I"m trying to figure out how verify that I want to keep the NewData in the combobox, not to add a record in the NotInList() event. I need to set my Response to either Added or Continue, the same as at the end of the routine.

If I can figure this out, I think it will fix both forms. Does this help? thanks so much.
Rosie
Aug 23 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.  
Aug 23 '07 #4

imrosie
100+
P: 222
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.  
Hi FishVal,
I looked it over and copy/pasted it as a replacement to my code. However, the result is not what I think was expected. If I typed in the combo box the name "Salty Pretzel",,,,,the result in the window was Salty, Salty, I think because you had O and O on the trim statements, so it repeated the FirstName portion.....I didn't quite understand the "With RS" (recordset) portion, so I put back in the following to get the parsing to work:

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

FishVal
Expert 2.5K+
P: 2,653
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.
Aug 27 '07 #6

imrosie
100+
P: 222
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
Aug 27 '07 #7

FishVal
Expert 2.5K+
P: 2,653
I'm sorry,,,,,YES it's working great. thanks so much for your help and insight.
take care.
Rosie
You are welcome.
Fish
Aug 27 '07 #8

Post your reply

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