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

Help...notinlist creating 2 of same customer name in table

imrosie
100+
P: 222
Hello, I need help. I have a 'NotInlist' routine that appears to givbe me 2 entries of the same new customer. It first adds the name to the list, then when I fill in other customer data (i.e., address, city, phone etc.)...I end up with two separate entries of the same customer (different customer id's (assigned through an autonumber)

I found this out after looking in the Customer table to see that 1st the name goes into the Customer table, but without any of the data. The second customer has all the data I typed in, but without a name...

Here's the code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub custname_NotInList(NewData As String, Response As Integer)
  2. Private Sub custname_NotInList(NewData As String, Response As Integer)
  3. Dim intAnswer As Integer
  4. Dim strSQL As String
  5. Dim strFirstName As String, strLastName As String, strFullName As String    'for capturing the parsed components of FullName
  6. 'the FullName entered is not the table list; prompt user to ok adding it to list
  7.  intAnswer = MsgBox("" & Chr(34) & NewData & _
  8. Chr(34) & " isnīt on the list." & vbCrLf & _
  9.  "Would you like to add it?" _
  10.  , vbQuestion + vbYesNo, "Express")
  11.  
  12. ' Background process for Parsing FullName entry into first and last name
  13. strFullName = Trim(CStr(NewData)) ' Change Variant to String
  14. If InStr(1, strFullName, ",") = 0 Then     'FullName entered----> First Last
  15.      strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
  16.      strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
  17. ElseIf InStr(1, strFullName, ",") > 0 Then  'FullName entered----> Last, First
  18.      strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
  19.      strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
  20. Else
  21.      MsgBox "You've entered the name without a comma between the first and last name."
  22.      Exit Sub
  23.      End If
  24.   'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
  25. strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
  26.   "VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
  27.    DoCmd.SetWarnings False
  28.    DoCmd.RunSQL strSQL
  29.    DoCmd.SetWarnings True
  30.     If intAnswer = vbYes Then
  31.   MsgBox "The name has been added to the list." _
  32.    , vbInformation, "Express"
  33.  'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
  34.    Response = acDataErrAdded
  35. Else
  36.   MsgBox "Please select a name on the list." _
  37.  , vbInformation, "Express"
  38.  Response = acDataErrContinue
  39.  End If
  40.  
  41. End Sub
  42.  

I wrestled with this for a long time and just can't figure out how to correct it...help.
Rosie
Jul 25 '07 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
Storing a Fullname and the First and LastName is known as the creation of redundant data. You'll experience trouble when afterwards someone edits the Lastname and not the FullName.....
In a normalized table we only record FirstName and LastName and when we need the FullName we use a concatenation of the two fields. That way we can control the name and have "John Doe" or "Doe, John" as we like (or need).

This will however make it impossible to use one combobox for the selection.
Personally I use a mainform with a datasheet subform, allowing the user to filter on multiple fields (Like LastName, FirstName, Town, DayOfBirth, etc) to find out or his "new" client is already known. Then a [Add] button can be used to add a new client. Optionally you could show a message when someone with the entered name on the Add-form already exists, but keep in mind that typo's are easily made.

For the filtering I give my users the following instruction:
http://www.geocities.com/nico5038/xR...nstruction.zip
and as a sample of a datasheet subform and the [Add] button check:
http://www.geocities.com/nico5038/xS...tAction-97.zip

Nic;o)
Jul 27 '07 #2

imrosie
100+
P: 222
Storing a Fullname and the First and LastName is known as the creation of redundant data. You'll experience trouble when afterwards someone edits the Lastname and not the FullName.....
In a normalized table we only record FirstName and LastName and when we need the FullName we use a concatenation of the two fields. That way we can control the name and have "John Doe" or "Doe, John" as we like (or need).

This will however make it impossible to use one combobox for the selection.
Personally I use a mainform with a datasheet subform, allowing the user to filter on multiple fields (Like LastName, FirstName, Town, DayOfBirth, etc) to find out or his "new" client is already known. Then a [Add] button can be used to add a new client. Optionally you could show a message when someone with the entered name on the Add-form already exists, but keep in mind that typo's are easily made.

For the filtering I give my users the following instruction:
http://www.geocities.com/nico5038/xR...nstruction.zip
and as a sample of a datasheet subform and the [Add] button check:
http://www.geocities.com/nico5038/xS...tAction-97.zip

Nic;o)
Nic;o

Thanks for your direction, I'm going to get busy with this one now to see if I can ever get out of the situation I'm in. I'll post back the result for the sake of others. thanks

Rosie
Jul 27 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Keep me posted and don't hesitate to ask clarification.
The approach I proposed is called the "Object/Action" principle and resembles the way most Windows applications and also Access does work. Check out the database window for tables, forms and reports and see the same "action" buttons, although positioned in another place :-)

Nic;o)
Jul 27 '07 #4

imrosie
100+
P: 222
Keep me posted and don't hesitate to ask clarification.
The approach I proposed is called the "Object/Action" principle and resembles the way most Windows applications and also Access does work. Check out the database window for tables, forms and reports and see the same "action" buttons, although positioned in another place :-)

Nic;o)
Hi Nic;o..

I'm a newbie andthat was a bit over my head.....I just need a simple procedure to add a new name to the list when it's not in the list,,,,,,,,,,,without it creating two, not one, entry in the Customer table. thanks if you have something for a newer person to all this Vb.
Rosie
Jul 28 '07 #5

Post your reply

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