Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting name into unbound combo box

    I need to copy first name and last name and paste it into my unbound combo box after adding a new client.

    Currently, my "new name" form has: First Name and Last Name fields and I want to combine them so that it would look like this: Anderson, Brent

    I can see how I could reverse Helen Feddema's code to split the name and paste them into two fields as follows:

    'Parse entry into first and last names
    strLastName = Mid(NewData, 1, InStr(NewData, Chr(44)) - 1)
    Debug.Print "First name:" & strFirstName
    strFirstName = Mid(NewData, InStr(NewData, Chr(32)) + 1)
    Debug.Print "Last name:" & strLastName

    Normally, I'd ask Helen directly, but I thought maybe, someone else could learn from this too.

    Thanks in advance.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pasting name into unbound combo box

    Hi Brent,
    Unless I'm missing the point, all you need is something like:
    NewData = [LastName] & ", " & [FirstName]
    to get the data you want. How you add that to your combobox depends on its rowsource.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting name into unbound combo box

    I'm not sure where do you put that line of code? Should it be placed inside the command button that closes the form and requeries the main form?

    Also, the rowsource is set up in a such way that it combines last name and first name into one column by using Name:[LastName]&", "&[FirstName].

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pasting name into unbound combo box

    If the combobox is getting its values from a query based on the table that you've just input the new data to, then requerying the main form ought to show the newly-added data in the combobox.
    Is that the case?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting name into unbound combo box

    Actually, the requery method works fine. What I really wanted was to close the "add new name" form and have the new name automatically show up in the combo box in order to look up its records.

    Right now, how my forms work would be that the data entry person types in the name, if not in the list then "Add New Name" dialog box opens. the name of person gets automatically added to First and Last name fields. Data Entry person only needs to enter address, phone number and Email address and then click "close" button. The "Add New Name" form closes, and the Application form reopens. The Data Entry person now has to type in the name that she just had added to the New Name form.

    I was looking for a way of automatically filling in the Name field in the Application form so that the Data Entry person doesn't have to type the name again.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pasting name into unbound combo box

    Hi Brent,
    I think I get you now! Something like:
    With Forms!formname
    .requery
    .comboboxname = [LastName] & ", " & [FirstName]
    end with
    in the click event of the Add New Name form's close button should do it I think (if you don't requery the Application form first, I think you'd retrigger the NotInList event)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •