Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating fields (Access 2000 SP3)

    I would like to create my form to have the fields do the following:

    The first field named ClientFullName, would have the client's name inputted as "Smith, John L."
    After typing this field, is it possible for the Individual fields I created for "FirstName, LastName, MiddleInitial" to be populated automatically from the ClientFullName field?

    Thanks
    Nadia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating fields (Access 2000 SP3)

    Will each name always consist of all three parts, or can somebody also be named "Doe, John" or "Doe, J.R." ?

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating fields (Access 2000 SP3)

    Hi Nadia,

    It's possible to place text boxes on the form which could break down your string into FirstName, LastName and Initial, but they wouldn't be bound to the underlying field(s) in the table, which, I assume, is what you REALLY need to happen.

    Since ClientFullName is really derived from the three ingredients of FirstName, LastName and Initial, wouldn't it be better to have bound controls on the form that are bound to the fields of FirstName, LastName and Initial in the table, enter the data in the three boxes, and then use one calculated control to display the ClientFullName on the form? this would make it easier to get the components (FirstName, LastName and Initial) into the underlying table.

    It can be done the other way round, but then you have all the problems of names like "de Boer, Ronald F" which has spaces where you might not expect them.

    HTH

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating fields (Access 2000 SP3)

    Hans,

    As far as I can tell from previous data, we may not have a middle initial and just the last name and first name, so it would be in either three parts or two parts.

    Thanks
    Nadia

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating fields (Access 2000 SP3)

    Hans,

    I thought I had beaten you to the draw there!

    Man, i shouldn't have stopped to spell check [img]/forums/images/smilies/smile.gif[/img]))

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populating fields (Access 2000 SP3)

    You can put code in the After Update event of the ClientFullName control to update the others:

    Private Sub ClientFullName_AfterUpdate()
    Dim lngPos As Long
    Dim lngPos2 As Long

    ' Get position of comma
    lngPos = InStr(Me.ClientFullName, ",")

    ' Set last name
    Me.LastName = Left(Me.ClientFullName, lngPos - 1)

    ' Get position of space between first name and middle initial
    lngPos2 = InStr(lngPos + 2, Me.ClientFullName, " ")

    ' Do we have a middle initial?
    If lngPos2 > 0 Then
    ' Yes: set first name
    Me.FirstName = Mid(Me.ClientFullName, lngPos + 2, lngPos2 - lngPos - 2)
    ' And middle initial
    Me.MiddleInitial = Mid(Me.ClientFullName, lngPos2 + 1)
    Else
    ' No: set first name
    Me.FirstName = Mid(Me.ClientFullName, lngPos + 2)
    ' And leave middle initial blank
    Me.MiddleInitial = Null
    End If
    End Sub

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating fields (Access 2000 SP3)

    Hans, as usual, your solutions work perfectly.

    Thank you!
    Nadia

Posting Permissions

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