Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    12

    Unanswered: Concatenate two fields automatically

    I would like to generate a field where the first four digits of a name and the last four of a phone number concatenate together. In my Lookup for this field name I have entered:
    Display Control : COMBOBOX
    Row Source Type: Table/Query
    Row Source: SELECT (Left([lname],4)) & (Right([phone],4)) AS Expr3 FROM ATHLETE;
    (the last bit of course comes from the query made to concatenate these together.
    When I enter new records in my datasheet view, (lname and phone), I do have a dropdown box to choose from which includes the new concatenated last name and phone.
    Is there a method to have this field update on its own - rather than selecting form either a list box or combo box?
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    comsider refreshing the relavent controldunno if this will work as I avoid datasheet view like the plague
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You can use the AfterUpdate event of the controls for the last name and phone number. It will only populate the combination control if both the LName and Phone controls have data. If the data in one of these controls is deleted, the combo control will be reset to Null.
    Code:
    Private Sub LName_AfterUpdate()
     If Nz(Me.LName, "") <> "" Then
      If Nz(Me.Phone, "") <> "" Then
       Me.ComboControl = Left(Me.LName, 4) & Right(Me.Phone, 4)
      End If
     Else
      Me.ComboControl = Null
     End If
    End Sub
    
    Private Sub Phone_AfterUpdate()
    If Nz(Me.Phone, "") <> "" Then
      If Nz(Me.LName, "") <> "" Then
       Me.ComboControl = Left(Me.LName, 4) & Right(Me.Phone, 4)
      End If
     Else
      Me.ComboControl = Null
     End If
    End Sub
    Now, if ComboControl is a bound control, this is all you need to do.

    But if ComboControl is an unbound control, as it really should be (calculated fields, such as this, really shouldn't be stored in a table, but simply recalculated, as needed) you'll also need this similar code in the Form_Current event.
    Code:
    Private Sub Form_Current()
    If (Nz(Me.LName, "") <> "") And (Nz(Me.Phone, "") <> "") Then
      Me.ComboControl = Left(Me.LName, 4) & Right(Me.Phone, 4)
     Else
      Me.ComboControl = Null
     End If
    End Sub
    Linq ;0)>
    Last edited by Missinglinq; 04-09-11 at 23:19.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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