Solved Detecting change in record (1 Viewer)

[nashaz]

Hi all

I have a main single form (MainF) which displays various types of records through use of continuous subforms (SubF1 and SubF2) for an employee chosen on MainF.
To prevent accidental changes in records, I have put in an EditBtn which lets the user to change records. I have multiple controls on MainF, SubF1 and SubF2, if I change something and move onto the next control, the change gets saved in the underlying table. I'd like to be able to give the user option to press CancelBtn which will get rid of all the changed (and saved) records and restore original. Using Me.Dirty isn't exactly suitable here.

What I have tried is to use the following commands to make it work.

Behind EditBtn_Click() sub I have:

Currentdb.Execute "INSERT INTO tmpEmployeeT SELECT * FROM EmployeeT WHERE EmployeeID = " & Me.EmployeeID

Similarly, behind CancelBtn_Click() sub I have:

Currentdb.Execute "INSERT INTO EmployeeT SELECT * FROM tmpEmployeeT WHERE EmployeeID = " & Me.EmployeeID
CurrentDb.Execute "DELETE * FROM tmpEmployeeT WHERE EmployeeID = " & Me.EmployeeID

What isn't working is INSERT INTO EmployeeT SELECT * FROM tmpEmployee. When I click CancelBtn, the db retains the new record rather than reverting back to previous, and deletes the record in tmpEmployeeT.

Appreciate all the help.

Note: I will be back to work on Tue next week, so I apologise in advance for not replying earlier than that

 

[The_Doc_Man]

When you do any navigational thing, you get an auto-save of dirty records. The only way to protect against the automatic update is in the Form_BeforeUpdate event, which can be canceled. There is such a thing as DoCmd.Undo, which forces all form controls back to their .OldValue, but I can tell you that a strategy that writes a record and then tries to remove it is inferior to a method that doesn't do any writing in the first place.

 

[Pat Hartman]

if I change something and move onto the next control, the change gets saved in the underlying table.

Not true. Access saves the current record if it is dirty when something you do on the form causes the form to close or to move to a different record.

Using an unbound form is way too difficult for a novice coder and absolutely unnecessary. Access is a RAD (Rapid Application Development) tool and its biggest benefit is the use of bound forms. If you are not going to use bound forms, you should find yourself a "better" platform because you are accepting all the cons of using Access but ignoring the pros. Even worse, is using a bound form and trying to update the bound record using an action query which is what you seem to be trying to d.

You are making things harder for yourself than you need to. Most users are not as clumsy as you seem to think they are. Having a button to put the form into edit mode should be more than sufficient to make the users aware that they are changing live data. You could even change the color of the background on the form for emphasis.

The actual way to ensure good data is to validate it. Use the Form's BeforeUpdate event. This is the last event that runs before the record gets saved - no matter what prompted the save. It doesn't matter if you forced the save or if Access decided on its own imitative that the record needed saving. The Form's BeforeUpdate event lets you cancel the save if the record is incomplete or any of the fields don't pass your validation edits.

You will be far happier with Access if you learn how its event model works so you can use it to your advantage rather than fighting with it. EVERY form or control event is there for a specific purpose. The documentation is lacking because it doesn't explain this very well.

I created three videos that talk about bad data. In the process I created a database that shows how form and control level events work and why some are better than others for validating data. The link also includes the database in addition to the three videos.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[nashaz]

When you do any navigational thing, you get an auto-save of dirty records. The only way to protect against the automatic update is in the Form_BeforeUpdate event, which can be canceled. There is such a thing as DoCmd.Undo, which forces all form controls back to their .OldValue, but I can tell you that a strategy that writes a record and then tries to remove it is inferior to a method that doesn't do any writing in the first place.

Hi Doc

The issue with using Form_BeforeUpdate event is that it triggers as soon as you move from MainF to any of the subforms, or vice versa, saving the data in the process.

I completely agree about "a strategy that writes a record and then tries to remove it is inferior to a method that doesn't do any writing in the first place". And even though that is what technically is going on here, this was not my intention. Before I attempted this tmpEmployeeT approach, I was trying to figure out if there was any way the form_beforeupdate event would hold off even when I move from MainF to any subform, or vice versa. Hencewhy this tmp table approach.

Given this scenario, I am not sure if docmd.undo is going to work either.

 

[nashaz]

Not true. Access saves the current record if it is dirty when something you do on the form causes the form to close or to move to a different record

Yes that is right. What I meant is that the form_beforeupdate event triggers even if you move from MainF to any subforms, and vice versa. I was hoping that using the tmp table method, I could have the desired functionality.

I understood this pretty early in my learning that unbound forms need higher expertise to work with, and that's why I am not using them at all.

 

[ebs17]

which will get rid of all the changed (and saved) records and restore original

With an event procedure in Form_BeforeUpdate you only have exactly one (the current) record under control.

For other records and records in other tables (sub-forms) you would have to turn a larger wheel.
A timestamp field could be used to record when a record was changed for each table. In a multi-user environment, you would also have to at least document which user was active there.
Restoring an old data state would require removing or saving overwritten content at the time of editing.

 

[CJ_London]

There is a hybrid way of using a bound form that does not update automatically. You need to create an ADO disconnected recordset and then bind the form to that.

code can be as simple as

Function ADODisConnRset(sql As String, Optional cn As Object = Nothing) As Object
'defaults to current application, for all other locations, create a connection and include in parameters

Dim ars As Object

    Set ars = CreateObject("adodb.Recordset")
   
    If cn Is Nothing Then Set cn = CurrentProject.AccessConnection
   
    With ars
       
        Set .activeconnection = cn
        .Source = sql
        .CursorLocation = 3 'adUseClient
        .LockType = 3 'adLockOptimistic
        .CursorType = 3 'adOpenStatic
        .Open

        Set .activeconnection = Nothing

    End With
   
    Set ADODisConnRst = ars
   
End Function

and typically would be called in the form load event with something like

set me.recordset=ADODisConnRset("SELECT * FROM myTable")

you can the use your save button to have code to update/append as required

Problems with this approach:

• the access standard form functionality for find, filter and sort won't work as they are configured to work with DAO recordsets, not ADO - but you can write your own as a substitute.
• if in a multi user environment, when updating (and possibly inserting) records, additional checks need to be made to see if the original record has been modified by a different user between the times the recordset was disconnected then reconnected.

 

[The_Doc_Man]

Here is your problem in a nutshell. If you have parent/child tables (which your descriptions suggests is the case) then the parent MUST exist before you can do anything with the child... including creating a child record. Stepping from the parent form (and implied parent table) to the child form (and implied child table) runs into the very strict "no orphans" rule. If there is a parent/child declared relationship then you cannot get around this barrier. If there is no relationship then you are effectively playing with uncooperative spreadsheets.

Once you step into the child record, a parent's Undo undoes nothing because the moment you switch contexts, the update occurs. I.e. too late for you to do any good. Preventing the update would prevent the creation of the child record. Creating even one child record is enough to force the update. SO...

If your problem is to simply undo any new data entry, you can issue a DELETE of the child records first and then DELETE the parent record. A command button could do that for you. Note that to avoid a "no current record" error following the parent's deletion, you would need to navigate to some other record and issue your DELETEs based on having saved the PK of the primary in a variable. That navigation would prevent the error from the child records, too.

HOWEVER, if that was an UPDATE query, not an INSERT INTO, then the only way to undo that is with a full-blown restoration-capable audit trail, which is a much more difficult prospect.

 

[Pat Hartman]

The issue with using Form_BeforeUpdate event is that it triggers as soon as you move from MainF to any of the subforms, or vice versa, saving the data in the process.

That is absolutely correct. That is what Access is supposed to do. WHY? Because you MUST save the parent record before you can add a child record. Otherwise, how would you have a foreign key to add to the child record to link the two.

You seem to have convinced yourself that your users are incompetent and there is no other way but the one you "see" to solve the problem. That is incorrect. Access is built to solve problems and you have not described a problem that any of us has never seen nor solved. Solve the problem by using the form level events correctly.

 

[nashaz]

There is a hybrid way of using a bound form that does not update automatically. You need to create an ADO disconnected recordset and then bind the form to that.

code can be as simple as

Function ADODisConnRset(sql As String, Optional cn As Object = Nothing) As Object
'defaults to current application, for all other locations, create a connection and include in parameters

Dim ars As Object

    Set ars = CreateObject("adodb.Recordset")
  
    If cn Is Nothing Then Set cn = CurrentProject.AccessConnection
  
    With ars
      
        Set .activeconnection = cn
        .Source = sql
        .CursorLocation = 3 'adUseClient
        .LockType = 3 'adLockOptimistic
        .CursorType = 3 'adOpenStatic
        .Open

        Set .activeconnection = Nothing

    End With
  
    Set ADODisConnRst = ars
  
End Function

and typically would be called in the form load event with something like

set me.recordset=ADODisConnRset("SELECT * FROM myTable")

you can the use your save button to have code to update/append as required

Problems with this approach:

• the access standard form functionality for find, filter and sort won't work as they are configured to work with DAO recordsets, not ADO - but you can write your own as a substitute.
• if in a multi user environment, when updating (and possibly inserting) records, additional checks need to be made to see if the original record has been modified by a different user between the times the recordset was disconnected then reconnected.

Hi CJ

Thanks for the detailed response. The database will be used by various users and we will definitely be using filters. Seems like what I am trying to do is too much hassle for much less return. Think I might keep it the way Access by default works. Appreciate your response!

 

[nashaz]

@The_Doc_Man @Pat Hartman I dont know why I did not think of the orphan records issue. It would have been very simple to understand otherwise. Thank you for your inputs, as always

 

[Pat Hartman]

@nashaz Please mark the thread as closed. Make sure you put your validation code in the form's BeforeUpdate event for both the mainform and subform so each type of record gets properly validated. Depending on the version of Access you have, Access will prevent you from attempting a subform record if the parent record is empty. If yours does not, then use the subform's BeforeInsert event to check the mainform. If the pk is null, then cancel the event, display a message, undo the typing, put focus back in the main form and exit the BeforeInsert event.