How to UNDO all changes made a record with subrecords

Ugurovic

Registered User.
Local time
Yesterday, 18:15
Joined
Aug 22, 2008
Messages
36
Hi FORUM,

I have a question. What I want is to cancel all changes made to a record when the record already exist. This could be easy, but it isn't because it is linked to subforms with other records. How to cancel all changes made in this form and subform? What VBA code do I need?

Thanks in advance

Ugurovic
 
Sounds like a lot of work for built in functionality.

How do you know the record already exists?

The correct way to resolve this is to put a unique index on the fields that are "unique" in table design. This will prevent re-entry of the "record". Once the database tells your user that they can't enter the same record again, they can just hit <esc> to clear any changes they made to the form.
 
Hey thanks for your reply.
Excuse me for my bad english...

I first make the records. These records are filled in into the headform and subform, so 1record can have several subrecords. When this is done, this goes into the table. But when I open the record (with its form to modify) and I modify the records and its subrecords, how can I say with one button "ALL UNDO - CLOSE FORM", I want to undo all changes but not delete the record, I want to undo all changes to the record and subrecord.

I'll hope this is more clear.

Thanks in advance

Ugurovic
 
I'm not sure I get it. Once a record has been entered, the default behavior of Access is to write the record to the table (if you leave the record). This includes records on the main and sub-forms.

In order to stop that from happening, you need to handle almost everything in code, behind the scenes. At that point, Access is no longer an asset to you and you should probably use some other coding language.

The other option is to delete the records from the table, which you seem to have explicitly stated that you do not want to do.

Can you provide more information?
 
example:

Headform
Name
Number
Adress
Age

Subform1
School
AdressSchool

Subform2
Girlfriends
Boyfriends


Now this is 1 record I made, I press save and it's finished. At a later stage, 1 month later I go to the record, I open the form where I can modify the record (it's linked with ID, ItemsListCount), I modify a lot but after a few seconds I'm realising that I was modifying the wrong record.
How can I now cancel all changes made to this record with his subforms. Just UNDO ALL CHANGES, form is closed and than I go the my correct record. How to UNDO?

Thanks

U
 
I think you may be a little confused about Access. You store data in Tables. You display data in Forms/Reports. You input/amend data in Foms. You select data using Queries. You can use Action Queries to use SQL to amend/update data.
 
No I am not confused. With forms I put data into tables. Indeed, with forms I display records of tables. No I am sure, for so far my experience, what Access can do and cannot do.

Kind regards

Ugurovic.
 
How can I now cancel all changes made to this record with his subforms. Just UNDO ALL CHANGES, form is closed and than I go the my correct record. How to UNDO?

I think I understand now.

You can press <escape> to undo any changes that have not yet been written to the database. If they're already been written to your tables, you must delete them, though.

Does that help?
 
No, with escape I delete the field value, if I close the form, changes are saved.
I just want to exit the form without changes being made to the record... if there are fields changed, these fields may not be written to the table, that's my goal.
changes made => user is wrong => button 'close without saving' => record is still the record before.

Regards

U
 
Sorry, that's not how Access works. If the record is dirty and an event occurs that enables it to write the data to its table, it will write it to the table. You could over-ride this functionality in the "Before Update" event, but you've got a ton of work to do designing that.

I just re-read your post: the escape key does not delete data in a field, it undoes any changes since the last write.
 
Ok, I understand, I have just tested it, but it doesn't work with a bound field and a dependant field, example: Field1: Name, Field2: =Field1.Column(1), if Field1 is filled, automatically Field2 gets filled, and with escape we cannot delete this... hmm how to undo independant field with the bound field...

regards

Ugurovic
 
change my code into what, could you give an example
 
Not really. The statement "is not plain Access" would indicate that I have no idea. Who wrote the cascade code? That's the person you need to ask.

The point is, on the change (or other appropriate) event of whatever field you're complaining about, you need to make sure the other fields you're complaining about are updated. Or you need to re-write/re-think how or when the dependant fields get their data.
 
The simplest way to delete a record with all its child records is to enable "Cascade Delete" in the relationship then if there's a problem, press a button that will delete the parent record, then everything will be gone as well.

If you want a full ACID, then you will have to do it with a unbound form and managing the recordsets using transactions. That's a lot of work.

EDIT: Another alternative! You could implement an Audit Trail (search for it on forum) so you have a log of any changes made, then just need to add some code to mark the event where you want to represent a transaction, then if you wan to undo the edits, use the audit trail log to walk the edits and putting it back in place. This enables you to continue using bound form, but you still have significant work to do with implementing the trail.
 
Last edited:
What you have is an old problem.
In a less database oriented environment than Access saves are not implicit - and must be executed explicitly. Hence there is the opportunity (before that save) to be sure you're on the right record.
(Bear in mind that operator mistakes are always going to happen - and you can spend thousands putting in functionality to "help" then - when good training or just the fear of god put into them from management can be more cost effective ;-)

As Banana says - you can wrap all this in transations. (Have a look at his thread here Bound Form Transactions ). It's possible to bandage up into a useable form - but is likely a source of problems for the unwary.

Equally you can use an unbound form (technically not possible in non-single view - but instead a form bound to no real data source, i.e. a constructed recordset) or a form bound to a local holding table. And only commit the data from there to the live table when ready.

One advantage to this would be fewer reads and writes back to the database (compared to, say, implementing a roll back with your own audit trail).

As a simple example of non live updates - I'll attach the simplistic Unbound form demo to this post.
(Yep - my website is down again! Can't believe it's been down twice now since I started posting here. Co-incidence or is someone trying to tell me something? lol).

Cheers.
 

Attachments

As Banana says - you can wrap all this in transations. (Have a look at his thread here Bound Form Transactions ). It's possible to bandage up into a useable form - but is likely a source of problems for the unwary.

Leigh, thanks for the pointer to that thread. :) Just want to clarify that this only works, AFAICT, with MySQL as the backend. I doubt my approach will work for Access backend (unless I'm missing something obvious).

Also, just so you know, Leigh has a sample of transaction-capable form so you can get an idea of how this is implemented within Access environment. It's also on his website. (Leigh, I'm so sorry to hear that you are having a spot of bad luck with website!)
 
Oh yeah I didn't want to imply that the eventual solution for Banana in that thread is a solution that most can use.
Indeed - I'd suggest that it'll only be applicable to MySQL as a linked table back end (unless Oracle and others offered similar ODBC configuration options - for AFAICT SQL Server isn't as accomodating).

But the general discussion through the thread might give some insight into the issues involved (rather than type it all out here again). :-)

I'll attach the Form Transaction example here too - just in case.
(<mumbles something about stupid website>)

Cheers.
 

Attachments

Hello

Can someone attach a file where a form and subform is connected BUT where the subform is unbound. In het "Unbound.zip" it is just 1 form and not a subform.

Thank You

Kind regards

Ugurovic
 
Well, a unbound form (and this applies to a subform) basically means that its recordsource property is blank. That's all there is to being unbound, really.
 

Users who are viewing this thread

Back
Top Bottom