NOTinList add names only on form, other info stored with blank name

[imrosie]

Hello (from Rosie the newbie),

I recently got help with a wonderful event to perform this from 'thescripts'...it recognizes that a name is not in the list an allows for (after parsing first and last name) for the addition of the new FullName into the Customer table. I've been working on some other forms in this application and hadn't noticed until now a little problem.

The event occurs in a 'search customer' form. You first search an already existing customer name, or add new one. What's happening is after the 'NotInList' event occurs and you've added the new name to the list, it's stored in the table, no problem. What happens next is, you begin adding other info to this 'new customer', i.e., address, phone, city, state, etc (you get the picture). BUT what's really occuring is a new record in the database ends up with all these field filled in, without a Customer Name. So in effect there are 2 records stored; one with only the new name you added (during the 'NotinLIst event) and the second record with all the fields filled in but without a name, when you close (or save) the form.

So my 'NotInList' is disconnected from the rest of the forms fields. How can I
connect them together? When I close the form, that's when the other fields are stored.
Help please, thanks, here is the code for event:

Private Sub fullcustname_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 in 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
Me![fullcustname].Undo
 
  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
 
 

Thanks again

[hyperpau]

Hello (from Rosie the newbie),

I recently got help with a wonderful event to perform this from 'thescripts'...it recognizes that a name is not in the list an allows for (after parsing first and last name) for the addition of the new FullName into the Customer table. I've been working on some other forms in this application and hadn't noticed until now a little problem.

The event occurs in a 'search customer' form. You first search an already existing customer name, or add new one. What's happening is after the 'NotInList' event occurs and you've added the new name to the list, it's stored in the table, no problem. What happens next is, you begin adding other info to this 'new customer', i.e., address, phone, city, state, etc (you get the picture). BUT what's really occuring is a new record in the database ends up with all these field filled in, without a Customer Name. So in effect there are 2 records stored; one with only the new name you added (during the 'NotinLIst event) and the second record with all the fields filled in but without a name, when you close (or save) the form.

So my 'NotInList' is disconnected from the rest of the forms fields. How can I
connect them together? When I close the form, that's when the other fields are stored.
Help please, thanks, here is the code for event:

Private Sub fullcustname_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 in 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
Me![fullcustname].Undo
 
  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
 
 

Thanks again

The code you got is quite confusing. I wonder how you understood it.

Here is what I suggest you to do.
I want you to make a form bound to the table where you have the customer name field on.

Let's say you name it frmAddName
in the frmAddName, just add the combo box of the CustomerName field.
Let's say the name of this field is [CustName]
Then add two command buttons. one is named cmdAdd, and one is cmdCancel

Program the two command buttons as follow:

Private Sub cmdAdd_Click( )
 
    DoCmd.RunCommand acCmdSaveRecord
    Forms![Name Of Your Form]![Name of the Combo Box].Requery
    DoCmd.Close "frmAddName"    
 
End Sub
 
Private Sub cmdCancel_Click()
 
    Me.Undo
    DoCmd.Close
 
End Sub

That should do it for this form. This is where user would add the new name.




Let's go to your form. On the NotInList event of you combo box, program
it like this:

Private Sub NameOfYourComboBox_NotInList(NewData As String, Response As Integer)

MsgBox "The Name you entered is not yet in the list" & _
vbCrLf & "Double Click to Add a new name", _
vbInformation, "Name Not Found"
Response = DataErrCont

End Sub

On the DblClick Event of your combo box, program it like this.

Me.[NameOfYourComboBox] = ""
DoCmd.OpenForm "frmAddName"



Try it. This is the scenario, when the user input a name not in the list,
he would receive the message:
' The name you entered is not in the list.
Double Click to add a new name '

Then the user clicks OK on the message box.

The user would now then double click the combobox.
This would open the frmAddName I asked you to make.

The user would type that new name in the text box and Clicks ADD.
Since the form is bound to the table where the name is saved on that table
and the combo box on the Your Form is refreshed.

The user may now select that newly added name in the combo box.

Enjoy! :)