locked
correct way to calculate fullname RRS feed

  • Question

  • I try to concatenate first and last name together after inputting from the Access form and then put results into the same table Employee table using trigger or stored procedure. When next time i try to edit the same record a popup appears say 'Another user edited this record and save the changes before you attempted to save your changes. re-edit the record.' This means that every time i've to requery and reach to the same record which will be slow as table grows.

    How to do this thing correct, so that I don't see this message please?

    Thanks,

    K

    Friday, February 3, 2017 2:11 PM

Answers

  • Hi,

    Are you using forms to make the changes?

    • Marked as answer by KhurramKZ Saturday, February 4, 2017 7:12 AM
    Friday, February 3, 2017 2:21 PM
  • You need to commit changes to your current form if it is bound, then requery the form. You ARE the second user in this case. Change the same record in code that directly alters the table means your current form isn't seeing those changes unless you tell it to(requery). Make sure limit the number of records by using a WHERE clause and you shouldn't have bad performance.

    If you are using SQL Server, why don't you just create a Calculated field on that table on the server that does this?? I mean, that keeps the FirstName and LastName in another field. This way you never have to worry about it. When you change first or last name, that field is always correct. Just a thought.

    • Edited by HTHP Friday, February 3, 2017 2:31 PM
    • Marked as answer by KhurramKZ Saturday, February 4, 2017 7:12 AM
    Friday, February 3, 2017 2:28 PM

All replies

  • Hi,

    Are you using forms to make the changes?

    • Marked as answer by KhurramKZ Saturday, February 4, 2017 7:12 AM
    Friday, February 3, 2017 2:21 PM
  • How to do this thing correct, so that I don't see this message please?

    Hi KhurramKZ,

    Probably your record is not yet saved after the concatenation. In the routine that does the concatenation you can add the line

        Me.Dirty = False

    Imb. 

    Friday, February 3, 2017 2:26 PM
  • You need to commit changes to your current form if it is bound, then requery the form. You ARE the second user in this case. Change the same record in code that directly alters the table means your current form isn't seeing those changes unless you tell it to(requery). Make sure limit the number of records by using a WHERE clause and you shouldn't have bad performance.

    If you are using SQL Server, why don't you just create a Calculated field on that table on the server that does this?? I mean, that keeps the FirstName and LastName in another field. This way you never have to worry about it. When you change first or last name, that field is always correct. Just a thought.

    • Edited by HTHP Friday, February 3, 2017 2:31 PM
    • Marked as answer by KhurramKZ Saturday, February 4, 2017 7:12 AM
    Friday, February 3, 2017 2:28 PM
  • I try to concatenate first and last name together after inputting from the Access form and then put results into the same table Employee table.............

    Why?  It merely introduces redundancy into the table.  It is a trivial task to return the full name whenever needed, e.g. in a computed column in a query or a computed control in a form or report.

    With first and last name values it's unlikely either will be Null, but you can easily cater for this possibility in an expression, e.g. Trim((FirstName + " ") & LastName).  You can see how it would work in the debug window:

    FirstName = "Ken"
    LastName = "Sheridan"
    ? Trim((FirstName + " ") & LastName)
    Ken Sheridan
    FirstName = NULL
    LastName = "Sheridan"
    ? Trim((FirstName + " ") & LastName)
    Sheridan
    FirstName = "Ken"
    LastName = NULL
    ? Trim((FirstName + " ") & LastName)
    Ken
    FirstName = NULL
    LastName = NULL
    ? Trim((FirstName + " ") & LastName)
    Null

    Or you can call the following function, which is a small amendment of the CanShrinkLines function published by Microsoft:

    Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
     
        ' Pass this function a character or characters
        ' to be used as the separator followed by the values to be combined
        ' For example: strFullName =
        ' ConcatValues(" ",FirstName,MiddleName,LastName)
        
        Dim X As Integer, strLine As String
        
        For X = 0 To UBound(arrVals)
            If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
              strLine = strLine & strSeparator & arrVals(X)
            End If
        Next
          
        ' remove leading separator character(s)
        ConcatValues = Mid(strLine, Len(strSeparator) + 1)
     
    End Function

    Again in the debug window:

    FirstName = "Ken"
    LastName = "Sheridan"
    ? ConcatValues(" ",FirstName,LastName)
    Ken Sheridan

    Functions like this come into their own, however, where multiple columns need to be concatenated and any one or more might be Null, e.g.

    FirstName = "Ken"
    MiddleName = Null
    LastName = "Sheridan"
    ? ConcatValues(" ",FirstName,MiddleName,LastName)
    Ken Sheridan

    You'll find this and other concatenation functions illustrated in Concat.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    Friday, February 3, 2017 4:04 PM
  • A computed field expression on SQL Server could look something like this: (Trim((Trim(isnull([FirstName],''))+char((32)))+Trim(isnull([LastName],''))))

    Or you could create a generic function in Access that does what Ken Sheridan has shown. So you don't have to duplicate code all over the place in your project.
    Friday, February 3, 2017 4:59 PM
  • Sometimes the solution is in front of you, but you can't see. Thanks for reminding me that I could do that in BeforeUpdate event on the form. Although I'm also updating couple of other fields in BeforeUpdate, but don't know for some reasons, thing didn't come to my mind about this field. Feeling so dumb!!!

    Thanks to others for explanation by the way, it helped me understand about the logic behind conflict.

    Yea, i'm using SQL Server backend.

    BR

    Saturday, February 4, 2017 7:12 AM