We help IT Professionals succeed at work.

How to use vba to split the text of a first name and last name into two separate fields to add to another form.

fabi2004
fabi2004 asked
on
118 Views
Last Modified: 2017-03-13
I have a drop-down list that allows adding to it.  When a user types in information that is not on the list, a message appears asking them if they want to add it.  If they click yes, then another form opens pre-populated with the text data they originally typed in the first form.

The problem is that a user will usually type in "FirstName LastName" and that is what carries over to the new form directly into the LastName field.

What I need is to split the text at the 'space' between the first and last name and then add the first name to the first name field in the new form and the last name to the last name field in the new form.

The user would enter "FirstName LastName", select yes to add, a new form opens and the FirstName field populates with "FirstName" and the LastName field populates with "LastName".

Right now I have:
Dim strRowSource as String
        strRowSource = Me.cboProviderID.RowSource
        Me.cboProviderID.RowSource = ""
        DoCmd.OpenForm "frmProviders", , , , acFormAdd, acDialog, NewData
        Me.cboProviderID.RowSource = strRowSource

Below is the entire code I have in the NotInList event of the combo box:

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


    Dim strMsg As String
    Dim strRowSource As String
    
    If NewData & "" = "" And Me.cboProviderID.Column(1) & "" = "" Then
        Me.cboProviderID = Null
        Response = acDataErrContinue
        Exit Sub
    End If
    
    strMsg = "'" & NewData & "' is not in the list!" & vbCrLf _
           & "Would you like to add it?"
    If MsgBox(strMsg, vbInformation + vbYesNo + vbDefaultButton1, "Add provider") = vbNo Then
        Response = acDataErrContinue
        Exit Sub
    Else
        strRowSource = Me.cboProviderID.RowSource
        Me.cboProviderID.RowSource = ""
        DoCmd.OpenForm "frmProviders", , , , acFormAdd, acDialog, NewData
    
        Me.cboProviderID.RowSource = strRowSource
        If Form_frmProviders.Tag = "Save" Then
            Me.cboProviderID = Form_frmProviders.ProviderID
        End If
        
        DoCmd.Close acForm, "frmProviders"
        
        Response = acDataErrContinue

    End If

End Sub

Open in new window

Comment
Watch Question

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you so much!
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Glad i helped you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.