none
Update clause for Data Changed RRS feed

  • Question

  • Here is what I am doing.

      I have form which show multiple records from a table and it is bound to the fields of the table.

      Now when I change a field in record 1, I have the user click on an 'Update Record' button to update that record.  Then the user can click on the button for 'Update All', which will update all the fields in records in the current record set that is displayed on the form.  It gets the values from record 1 of the record set. This is causing me some challenges, since if I did not update fields 2 & 3 on the form, they should not be updated for all the other records on the form in the record set.  The only fields which should be updated are the ones in record 1 that I updated.

      I have written the Update clause to update all fields on the table when I have the user, click on the 'Update All' button.  This does work, since all fields from record 1 in the Update clause do get updated.

      What I would like to have the code do is when the user performs an 'Update All', only the fields which were changed on record 1 would be updated to the rest of the records in the record set and not all fields. 

      Is there a way for me to do this?  I have been researching this and I could do this in DB2 on the Mainframe, yet I cannot find a way so far how to do thie is MS Access 2016.  Any ideas or input?

    Thanx in advance and I look forward to hearing all ideas.

    Jerry

    Saturday, March 2, 2019 1:54 PM

Answers

  • Sorry, but skyping goes beyond my commitments here.  What I have done to make things easy for you is post a file named ballj_351.accdb to my public databases folder at:

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

    This little file includes a form using Northwind data.  In the form if you amend the value in any column or columns in one record, and then click the 'Save Current Record and Update All' button in the form's header, all records in the form will be updated with in the values in the amended columns in the current record. NB: do not save the record before pressing the button.  To save the record without updating the other records just click on the record selector in the usual way, move to another record, or close the form.

    In the form's footer is a button to undo all updates and revert to the original values.  This included purely for the demo, so that you can experiment with changing values.  It would not form a part of an operational database.

    You simply have to use the code from the form's class module and the basChangedRecord standard module in your own database, amending the code where necessary with your own table and column names.

    Ken Sheridan, Stafford, England

    • Marked as answer by ballj_351 Monday, March 4, 2019 10:48 PM
    Sunday, March 3, 2019 8:48 PM

All replies

  • You'll need to identify in the form's module which column's values have been changed.  Note that this does not simply mean updated as a column can be updated without its value actually changing.

    You might like to take a look at ChangedRecord.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.

    This little demo file includes the a basChangedRecord module which includes functions and procedures for determining if one or more columns in a row in a table have had their value changed.  If the code in the RecordHasChanged function is amended as indicated by the comments against two lines below, when the function is called in the form it will print what changes have been made to the immediate window (press Ctrl+G to open) as in the example below the code:

    ' module basChangedRecord

    ' determines if data in a record edited
    ' in a form has actually been changed

    Option Compare Database
    Option Explicit

    ' arrays for storing values from recordsets
    Dim aOldVals(), aNewVals()

    Public Sub StoreOldVals(rst As DAO.Recordset)

       ' store values of current row in array
       aOldVals = rst.GetRows()
       
    End Sub

    Public Sub StoreNewVals(rst As DAO.Recordset)
       
       ' store values of edited row in array
       aNewVals = rst.GetRows()

    End Sub

    Public Function RecordHasChanged() As Boolean

       Dim n As Integer, intlast As Integer
       Dim var As Variant
       Dim aOld(), aNew()
       
       intlast = UBound(aOldVals) - 1
       
       ' loop through array of original values
       ' and store in new array
       ReDim Preserve aOld(UBound(aOldVals))
       For Each var In aOldVals()
           aOld(n) = var
           n = n + 1
       Next var
       
       n = 0
       
       ' loop through array of edited values
       ' and store in new array
       ReDim Preserve aNew(UBound(aOld))
       For Each var In aNewVals()
           aNew(n) = var
           ' if any value has changed then return True
           If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
               Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
               Or aNew(n) <> aOld(n) Then
               Debug.Print n, aOld(n), aNew(n)  '******temporary debug line******
               RecordHasChanged = True
               ' Exit For                                   '******line remarked out******
           End If
           n = n + 1
       Next var
       
    End Function
    ' module ends

    The following is an example of what would be written to the immediate window:

     1            Janet         Jane
     2            Black         White

    This shows that the FirstName column has been changed from Janet to Jane, and the LastName column has been changed from Black to White.  By changing the values of all columns you'll be able to determine the value of n for each column.  This will then enable you to substitute for the temporary debug line code which progressively builds the SQL statement to update the table and stores it in a module level variable of String data type, which can then be executed by code behind the Update All button.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, March 2, 2019 4:41 PM Hyperlink added.
    Saturday, March 2, 2019 4:40 PM
  • The "Update Record" button is not needed. If the user leaves the row without clicking it, the data will still be saved.

    Wrt UpdateAll: I think the trick is to compose your update statement to only include the changed fields. How do you know which ones changed and which ones did not?
    * In Form_BeforeUpdate you can inspect .Value and .OldValue, and if different the field has been changed. Use that to compose your SQL statement, and wait with executing it until user clicks UpdateAll.

    * In each control's AfterUpdate event update a Boolean variable to indicate if that field was updated.


    -Tom. Microsoft Access MVP

    Saturday, March 2, 2019 4:43 PM
  •   I have reviewed both of these ideas and the code.  There are many open concerns that I have with what I am thinking in how to implement these ideas.  The form is bound to a table with many records displayed on the screen.  When they change record 1 and click on 'Update All', then I need to push down the changes to the rest of the records from the recordset.  In thinking of how to do this, I find challenges in doing this or how I could do this. 

      I really with someone would be willing to do a Skype with me to see what I am doing on the form and code.  Then I could ask some questions to be able to get a better grasp of doing this.  Would someone be open to this?  Thanx in advance.

    Sunday, March 3, 2019 12:23 PM
  • Sorry, but skyping goes beyond my commitments here.  What I have done to make things easy for you is post a file named ballj_351.accdb to my public databases folder at:

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

    This little file includes a form using Northwind data.  In the form if you amend the value in any column or columns in one record, and then click the 'Save Current Record and Update All' button in the form's header, all records in the form will be updated with in the values in the amended columns in the current record. NB: do not save the record before pressing the button.  To save the record without updating the other records just click on the record selector in the usual way, move to another record, or close the form.

    In the form's footer is a button to undo all updates and revert to the original values.  This included purely for the demo, so that you can experiment with changing values.  It would not form a part of an operational database.

    You simply have to use the code from the form's class module and the basChangedRecord standard module in your own database, amending the code where necessary with your own table and column names.

    Ken Sheridan, Stafford, England

    • Marked as answer by ballj_351 Monday, March 4, 2019 10:48 PM
    Sunday, March 3, 2019 8:48 PM
  • Ken,

      I have been reading the code you sent and testing it.  I must say, I really like this!  One of the questions I must ask as I begin coding is when you check for fields that could have changed, how does the index work.  I know I will be debugging to see what is happening, but also want to ask.

      The first 3 fields on the screen are protected.  The user cannot make any changes to them. Therefore, when I read the basChangedRecord and the section of the following:

           ' if any value has changed then return True
           If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
                Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
                Or aNew(n) <> aOld(n) Then
                ' determine which column has been updated
                 Select Case n
                    Case 1
                    strColumn = "ProductCode"
                    strSetColumns = strSetColumns & "," & strColumn & "=""" & Forms("frmProducts").ProductCode & """"
                    Case 2
                    strColumn = "ProductName"
                    strSetColumns = strSetColumns & "," & strColumn & "=""" & Forms("frmProducts").ProductName & """"

     ...

      When I am looking for which column has changed, would I need to check columns 1 - 3?  Since they cannot be modified and are protected, they are disabled, when I do the Select Case statement, is this just for the fields that are enabled?  Therefore for me, Case 1, would be the first field that is enabled, which is really the fourth field on the detail section of the form.

      Does it matter?  I am just looping through each of the columns for the screen, left to right.  I.E. in your example, Case 1 could be ProductName and Case 2 could be ProductCode if you wanted to code it that way?  I know one should test left to right, top to bottom.  I am thinking that is just a loop to check all the fields that could be modified on the screen and build the select statement as I go along.

      Also after the following statement, I should add my WHERE clause before doing the db.execute statement.

        strWhereClause = "WHERE tbl.ColumnName = Forms("frmProducts").FieldName and tbl.ColumnName = Forms("frmProducts").FieldName"

            strSQL = strSQL & strSetColumns & strWhereClause

      As your time permits, can I have your thoughts on this and my thinking for this?  Thanx in advance.

    Jerry


    Monday, March 4, 2019 8:36 AM
  •   Can anyone see why I am getting this error?  It is stopping on the following statement:

    Set rst = dbs.OpenRecordset(strSQL)

    Plz see attached and thanx in advance.

    Monday, March 4, 2019 6:57 PM
  • When building an SQL statement in code you should not reference parameters, but concatenate their values into the statement, e.g.

    strSQL = "SELECT * FROM tblMfcMain " & _
                   "WHERE MakeCar = """ & Forms!frmMfcMthUpd!cboMakeDtl & """ " & _
                   "AND etc

    I've assumed that the MakeCar column and the value of cboMakeDtl are both of text data type.  If the column and the value of the control are of numeric data type, which would be the case with a 'lookup field' for instance the literal quotes characters should be omitted:

    strSQL = "SELECT * FROM tblMfcMain " & _
                   "WHERE MakeCar = " & Forms!frmMfcMthUpd!cboMakeDtl & " " &_
                   "AND etc

    For future reference, when posting code here, please do so as text copied from the procedure, not as an image.  We can then copy and edit it.

    Ken Sheridan, Stafford, England

    Monday, March 4, 2019 7:44 PM
  • Ken,

     Moving forward now.  I believe the Case # is the column from the table for each field with an offset of 0, wihch is the ID prime key field.  Therefore Case 1 is the first field from the table, excluding the ID field and is the ProductCode.  Plz let me know if I am reading this correctly!  Also, since the first field that can be changed is the 4th value, can I just start my Case statement with Case 4.  I would not be concerned with 0 - 3, since the user cannot change those values anyways!  I think I have gotten it, but want to check with you.

           If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
                Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
                Or aNew(n) <> aOld(n) Then
                ' determine which column has been updated
                 Select Case n
                    Case 1
                    strColumn = "ProductCode"
                    strSetColumns = strSetColumns & "," & strColumn & "=""" & Forms("frmProducts").ProductCode & """"
                    Case 2
                    strColumn = "ProductName"
                    strSetColumns = strSetColumns & "," & strColumn & "=""" & Forms("frmProducts").ProductName & """"
                    Case 3
                    strColumn = "UnitPrice"
                    strSetColumns = strSetColumns & "," & strColumn & "=" & Forms("frmProducts").UnitPrice
                End Select
                RecordHasChanged = True
           End If
           n = n + 1
       Next var

      Moving

    Monday, March 4, 2019 8:11 PM
  • The array's subscript is zero-based, so the value of n for the fourth column should be 3.  You can easily confirm this by setting a breakpoint on the line:

          Select Case n

    Then amend the first of the updatable columns' value and click the button.  When the code breaks on the line examine the value of n by hovering over it with the mouse pointer, or by entering:

        ? n

    in the immediate window (press Ctrl+G to open).  To clear the breakpoint press Ctrl+Shift+F9.

    Ken Sheridan, Stafford, England

    Monday, March 4, 2019 10:01 PM
  • Ken,

       I have been testing and coding all day.  You are correct and at this point, it is working so great.  I thank you so very much for your assistance in this.  I just have to pretty up and clean the code, yet what you sent to me works.  I am just going to modify it more to what we are doing, but in the initial unit testing, it is working as you sent me.  Thank you very very much!!!!!!!!!!!!!!

    Jerry

    Monday, March 4, 2019 10:05 PM
  • Everyone,

      This works great and I send much thanx out to Ken for all of his assistance in helping me to understand and put this in place.  I have a happy customer now and the appreciation goes to Ken.

    Monday, March 4, 2019 10:49 PM