Test to see if subform is 'dirty'

Question

_{Sunday, August 25, 2013 5:14 PM}

I have a combobox that is used to select a category of records that are displayed in a subform.  When the combobox is changed (updated) I need to determine if any changes have been made to any records in the subform.  My code is:

Private Sub EventsEntered_BeforeUpdate(Cancel As Integer)
    If Me.RecordScoresSubform.Form.Dirty Then
        MsgBox "Changes Made"
    Else
        MsgBox "No Changes"
    End If
End Sub

the screen shot below shows a change of entering the score '56'.  No matter what I change the subform never is considered 'dirty' by the EventsEntered_BeforeUpdate sub.  The subform is based on a table.

I always get the 'No Changes' message.  What am I missing

All replies (8)

_{Sunday, August 25, 2013 9:33 PM ✅Answered | 1 vote}

The method I suggested will work. What it won't do (and neither will a global variable) is indicate which records were updated.
Another idea would be to add a Yes/No field to your temp table and in the after update event of the subform, set it to true. The before you change the recordsource for the subform, you could check for any records updated in the temp table and only update those in the main table.

I would also question your overall methodology (unless you are working across a WAN). Thousands of records is not a lot. If your main table is properly indexed, there is no reason why you cant bind your subform to a limited number of records either by using a query with criteria applied or the recordsource itself.
E.G. Select * From <yourtable> where <yourfield> = " & forms!<yourmainform>!EventsEntered (plus whatever other criteria you need). Then in the afterupdate event of the combobox, just requery the subform. No DAO coding involved.

_{Sunday, August 25, 2013 10:05 PM ✅Answered}

Hi Steve,

I am trying to see if ANY field in ANY record in the subform has changed since the last refresh.  The subform is based on a small temp local (frontend) table.  If there any changes I need to save the changed data up to the PC holding the backend of the database.  Your explanation of why testing for 'dirty' does not work makes sense since the data is 'saved' to the temp table when I move from record to record in the subform.  I just need to see if any changes have been made and if so update the backend DB.

You could use the AfterUpdate event of a control of the subform to do the update of the backend DB.

Or if the temp table is based on a query, use that query to populate the subform directly.

My database was first developed in the mid 90's and has grown way beyond initial expectations over the years.  Sometime around 2003 I created this temp table of a small subset (1 to 30records) of a table of many thousand records on each frontend and performed analysis on the subset.  Updated records were then returned to (or deleted from) the master table over the network.  Worked great at the time.  Now that I am more familiar with DAO should I be using it and query recordsets to create dynamic subsets? 

I would say yes.

I know that global variables loose their value when an unintercepted error occurs but I started using them years ago when they were touted as THE answer to creating a bunch of txtboxes or other controls on a form whose sole propose in life was to facilitate making data on one form available to another form.  Based on your suggestion of the checkbox it sounds like maybe the current philosophy is to not use global variables.

It is not the unintercepted error as such that causes the global variables to loose their values, but the resetting of the code by the user. Unfortunately there is no other way.

There was a time that I did not want to use global variables, but I became tolerant. If you know the conditions under which you can safely use globals, they can be very powerful AND reliable.

But it is not necessary to use globals everywhere. For the communication between forms I pass the calling form's name to the called form, so that in the called form all communication with the calling form is possible.

Imb.

_{Sunday, August 25, 2013 5:38 PM}

I think you need to explain in more detail exactly how data in the subform's recordset is being changed via the combo box.  Changing data in a subform's underlying table via code in a parent form's module does not per se make the subform's Dirty property True.

Ken Sheridan, Stafford, England

_{Sunday, August 25, 2013 6:43 PM}

It appears that your combobox is on the main form. When focus changes from the suborm to the main form, if a record is 'dirty' in the subform, it is automatically saved. Therefore, the subform will never be 'dirty' when checking in any event of the main form. As Ken suggested, you need to better explain what you re trying to do.

_{Sunday, August 25, 2013 7:38 PM}

I am typing in the scores but that is immaterial based on your explanation of why the subform is never dirty.   Maybe I will have to define a global variable that is set when any field in the subform is changed and test this variable as needed.

Another may be to test for dirty in the 'lost focus' event of the subform.

Any other suggestions?

_{Sunday, August 25, 2013 7:48 PM}

You haven't explained what you are trying to accomplish. Once a record is saved, the form is no longer dirty. If you are trying to determine if any record in the subform has been changed, dirty, is not the property to check. Without a better understanding of what you are trying to accomplish, my suggestion would be a hidded checkbox in the parent form (chkSubformEdited). Then in the after update evernt of the subform add the line Me.Parent!chkSubformEdited = True.
Every time you change the source for the subform, reset the checkbox to false.
It is best to avoid global variables whenever possible. There is much written on this. If interested, bingoogle it.

_{Sunday, August 25, 2013 9:16 PM}

I am trying to see if ANY field in ANY record in the subform has changed since the last refresh.  The subform is based on a small temp local (frontend) table.  If there any changes I need to save the changed data up to the PC holding the backend of the database.  Your explanation of why testing for 'dirty' does not work makes sense since the data is 'saved' to the temp table when I move from record to record in the subform.  I just need to see if any changes have been made and if so update the backend DB.

My database was first developed in the mid 90's and has grown way beyond initial expectations over the years.  Sometime around 2003 I created this temp table of a small subset (1 to 30records) of a table of many thousand records on each frontend and performed analysis on the subset.  Updated records were then returned to (or deleted from) the master table over the network.  Worked great at the time.  Now that I am more familiar with DAO should I be using it and query recordsets to create dynamic subsets? 

I know that global variables loose their value when an unintercepted error occurs but I started using them years ago when they were touted as THE answer to creating a bunch of txtboxes or other controls on a form whose sole propose in life was to facilitate making data on one form available to another form.  Based on your suggestion of the checkbox it sounds like maybe the current philosophy is to not use global variables.

_{Sunday, August 25, 2013 10:11 PM}

I would agree with Alphonse that the use of a temporary table appears to be unnecessary and merely serves to introduce an additional level of complexity.  If you do persist with this approach, however, then be aware that updating a row in a table does not necessarily imply any change to the values in the row; a row can be updated to its current values.  Actual changes can be detected relatively easily, though, and you'll find an example as ChangedRecordDemo.zip in my public databases folder at:

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

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

If you do continue with your current approach I'd suggest that you use the module from my demo file to detect a change to a row in the subform, and in the event of an actual change or the insertion of a new row, execute an UPDATE or INSERT INTO statement to update the relevant row, or insert a new  into the back end table immediately rather than attempting to detect a change to any one of multiple rows in the subform's recordset.  The need for a global variable is thus avoided.

Ken Sheridan, Stafford, England