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

[imrosie]

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:

Private Sub custname_NotInList(NewData As String, Response As Integer)
Private Sub custname_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String    'for capturing the parsed components of FullName
'the FullName entered is not the table list; prompt user to ok adding it to list
 intAnswer = MsgBox("" & Chr(34) & NewData & _
Chr(34) & " isn´t on the list." & vbCrLf & _
 "Would you like to add it?" _
 , vbQuestion + vbYesNo, "Express")
 
' Background process for Parsing FullName entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then     'FullName entered----> First Last
     strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
     strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then  'FullName entered----> Last, First
     strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
     strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
     MsgBox "You've entered the name without a comma between the first and last name."
     Exit Sub
     End If
  'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
  "VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True
    If intAnswer = vbYes Then
  MsgBox "The name has been added to the list." _
   , vbInformation, "Express"
 'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
   Response = acDataErrAdded
Else
  MsgBox "Please select a name on the list." _
 , vbInformation, "Express"
 Response = acDataErrContinue
 End If
 
End Sub
 

I wrestled with this for a long time and just can't figure out how to correct it...help.
Rosie

[nico5038]

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)

[imrosie]

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

[nico5038]

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)

[imrosie]

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