Probably a stupid question about form behaviour (1 Viewer)

[Damob9K]

As the title says, this is probably a silly question for all of you experts:

One of the things that I really dislike about the way access works is the way that it updates in real-time fields in a table that are on a form.

As an example I have : table - query - form , now in said form there will be data that I don't want to be changeable so I have locked these fields in the form (that works fine) but there are other text boxes that can be changed, and some of them have combo drop down boxes that have an effect on other text boxes.

Now this is all fine and dandy, but with this setup as soon as a user changes one of the drop down box values, or changes any other changeable text box, access saves this to the table via the query...

But I don't want this to happen !! I want to have a Save button that then commits the changes to the table, thus also allowing the use of a Cancel and Exit button to allow all changes to be ignored.

I have tried using DoCmd.Close acForm, "FormName", acSaveNo
But this seems to be totally pointless as the changes have already been saved.

Am I missing something really fundamental with working with forms and tables, is there some command that stops the form from saving immediately to the table, or do I have to go about this in a totally different way ?

I have other forms that use unbound text boxes that get their data from the original table and then when I click on the Update button it basically does old value = new value , but this seems like a clunky way of getting round the issue.

Any tips / ideas ? or do I just have to live with "That's the way Access works"

Many thanks

Damob

 

[JHB]

...
One of the things that I really dislike about the way access works is the way that it updates in real-time fields in a table that are on a form.

Yeah - people are difficult to satisfy, (I like data update in real time).

...
I have tried using DoCmd.Close acForm, "FormName", acSaveNo
But this seems to be totally pointless as the changes have already been saved.

The above is for the the form itself, not for the data in it.

...
Am I missing something really fundamental with working with forms and tables, is there some command that stops the form from saving immediately to the table, or do I have to go about this in a totally different way ?

I have other forms that use unbound text boxes that get their data from the original table and then when I click on the Update button it basically does old value = new value , but this seems like a clunky way of getting round the issue.
Any tips / ideas ? or do I just have to live with "That's the way Access works"

It is the way it works, make the form unbound.

 

[Damob9K]

OK - Thanks JHB - I was hoping that wasn't going to be the answer, but had a funny feeling it was the way it is

Yes I agree in some forms I want the data to be updating live, in others I don't.
Just wish I could have the option tho !!

live data updating isn't very numpty proof, that's why I don't like it so much

Thanks for your answer, at least I now know that there isn't an easy way around my ism !

Cheers

Damob

 

[essaytee]

live data updating isn't very numpty proof, that's why I don't like it so much
Damob

At some point the data is committed or not and as the developer you write the numpty proof (love that term) routines.

If you don't want to go the unbound route what about 'on the fly' creation of temporary tables and base data entry forms on those. It still requires numpty proof routines to do their bit but no live recording or rollback until the Save or Cancel button clicked.

 

[Galaxiom]

Working with unbound forms is not a simple as some might think. Bound forms notify of conflicting updates were multiple users are working on the same record at the same time. If the update is done with a query then the last to save is the winner and the other data quietly lost.

Note that the updates do not happen as soon as data is changed in the control. They happen when the record is saved. That happens when moving to another record or closing the form. This update is easily cancelled.

The problem comes with subforms where multiple records are being entered in the same batch. Each one is saved when the focus is moved from the record.

Temp tables are a clumsy option.

By far the best way to manage updating records as a batch is via a transacted bound form. It is discussed in this thread.

There is a link to Microsoft where the technique is demonstrated.

 

[Damob9K]

OK Thanks chaps for your comments,

And as ever you have made me think of things that I may not have thought about before going down this road (Multiple users working on the same record - Although in this case that wouldn't be the case)

The particular issue with this form that i'm trying to improve, is that it has a number of combo boxes and 2 check boxes that when certain options and or conditions are reached, it changes a fair bit of the data contained within that record.
This is fine, apart from if the user changes his/her mind (in this particular situation that could be the case - as there are multiple 'decisions' that the user needs to make before even looking at changing anything in the database) it would be difficult to know what all of the original settings were.

I have an idea though ... (just not figured it out programmatic-ally)

What if when the form is loaded - (this is based on either a search or by selecting the record from the sub-form) - it reads all the field data values and sticks them somewhere temporary, and then if the user hits the 'Cancel' button, it writes them back into the form ?

This seems to me to be a better way of dealing with the issue without having to worry about using unbound forms and the such.
And I might be just reinventing the wheel here and such a feature already exists - if so can someone point me to it !!

If not how would YOU go about it ? - I'm thinking either create a new temporary table - read from the form and do a SQL insert into the temp table, then read it back if the cancel button is clicked.
OR
Create hidden unbound fields on the form and just copy the data to these on form load, then again copy them back if cancel is clicked.

Your thoughts / criticisms greatly welcomed

Cheers

Damob

 

[Galaxiom]

The little known but incredibly powerful transacted bound form is the only serious contender when it comes to delaying saves to changes in records.

It uses the form's recordset as the temporary storage and retains all the benefits of a bound recordsource. The updates to any number of records can be encapsulated inside transaction structure from different levels in the form.

I played around with fabricated ADO recordsets to try and achieve this kind of functionality and I found some funky things. But I threw it all out when I was introduced to transacted bound forms. I urge you to take the time to understand them because there really is nothing else that can do the job so elegantly.

I have a little demo database at work. I will post it next week.

 

[Damob9K]

Thanks Galaxiom,

I will indeed read up on 'transacted bound forms' sounds like it is what I need to be using in this instance.

Just got to finish off the unfinished modules that I haven't touched for a while, in fact iv'e not touched this database at all for at least 6 months so it's taking me a while to re learn some of the stuff that I was only just starting to get to grips with, and I've already re written a fair bit as I have now since learnt better ways to do certain things.

It's all part of the learning curve !

Thanks again

Damob

 

[Galaxiom]

Better late than never.

 

[Damob9K]

Thanks Galaxiom,

I will study,decipher and digest , and then maybe ask some dumb questions

Cheers for this.

D.

 

[rashu248]

Yes, Galaxiom

This is really want.

Many many thanks to you....

 

[rashu248]

Dear, Galaxiom
Its working good in my Db, but have 2 doubts.

1, The same function how will work with data entry = yes.
(Because i want to open the entry form except existing records.)

2, How to change Record source selection from 'Table' to 'Query
(Because i need to change the Record source to 'query'. which i made
a query focusing with criteria.)


I think below have to make some changes. please help me


Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM 1", dbOpenDynaset)
Set Me.Recordset = rs

End Sub

 

[Simon_MT]

I know unbound forms but here is another way of looking at the problem. I separate the functions of entering or updating an Invoice and enquiring on a Invoice.

You can change an Invoice to the point where it has been updated into AR and the GL. If you do change an Approved Invoice the Status goes back to pro-forma. Three basic stage process: Unapproved, Approved and Updated.

Invlice Selection Form with Status in the combibox. If it has been updated instead of going to an Entry Screen the user can only use an (read only) enquiry screen, devoid of any dropdowns, users can immediately tell they are in a non-entry screen.. The latter screen is used for any Sales Enquiry.

Simon

 

[Pat Hartman]

One of the things that I really dislike about the way access works is the way that it updates in real-time fields in a table that are on a form.

I know this thread is old but someone just referenced it and I couldn't let this statement pass. This statement is a complete misconception of how Access forms work. Access does NOT update the recordset after you leave each field. It only updates the recordset when you specifically save the record or perform some action that causes you to move to a different record.

You have COMPLETE control over whether or not to save the record even though you do not have control over when Access wants to save a record. The key is to using the Form's BeforeUpdate event. Think of this event as the flap at the end of a funnel. Either you let the data through so Access will save it or you don't. You're choice. Put your validation code in this event and if you decide that the record has an error or is incomplete and shouldn't be saved, simply cancel the update. The changed data remains on the form and the user can correct his error. Under some conditions, you might want to back out the changes but that is not very common.

 

[ryetee]

I know this thread is old but someone just referenced it and I couldn't let this statement pass. This statement is a complete misconception of how Access forms work. Access does NOT update the recordset after you leave each field. It only updates the recordset when you specifically save the record or perform some action that causes you to move to a different record.

You have COMPLETE control over whether or not to save the record even though you do not have control over when Access wants to save a record. The key is to using the Form's BeforeUpdate event. Think of this event as the flap at the end of a funnel. Either you let the data through so Access will save it or you don't. You're choice. Put your validation code in this event and if you decide that the record has an error or is incomplete and shouldn't be saved, simply cancel the update. The changed data remains on the form and the user can correct his error. Under some conditions, you might want to back out the changes but that is not very common.

I was the starter of the thread where someone referenced this thread!
This has nothing to do with the qustion I posed but I do have a wee problem which I thought I'd solved using the beforeupdate event but it hasn't quite!

I was getting an error from Access when I moved from one record to the other. The error was "You cannot add or change a record because a related record is required in the table". It was a valid error and I hadn't catered for it in the right place. I now have it in the before update (form). Now this works to an exten. If a field isn't filled in I display a message and setfocus on the field in question. All good so far but if the user then tries to go "off record" for a second time without actually trying to amend the error I get my displayed message again but then I get the "You cannot add or change a record because a related record is required in the table" suggesting that access is trying to update the record in question which indicates I'm not in complete control!

 

[Pat Hartman]

The last thread of yours was about discarding changes. I offered several suggestions but you went with something else which I think was an unbound form but I didn't open the example. Let's not hijack this thread and split everyone's efforts. Please send me a PM with the thread you want me to look at it and I'll do it tomorrow.

 

[Galaxiom]

The last thread of yours was about discarding changes. I offered several suggestions but you went with something else which I think was an unbound form but I didn't open the example.

An odd conclusion to jump to Pat. The clue that it isn't unbound is in the name "Transacted Bound Form". I thought it odd when you started on about the woes of unbound forms right under my post.

The technique avoids the woes of using a temp table and the associated potential for overwriting conflicting updates which are routinely handled by using normal bound forms. I recommend you take a look at how they work.

 

[Galaxiom]

I was getting an error from Access when I moved from one record to the other. The error was "You cannot add or change a record because a related record is required in the table". It was a valid error and I hadn't catered for it in the right place. I now have it in the before update (form). Now this works to an exten. If a field isn't filled in I display a message and setfocus on the field in question. All good so far but if the user then tries to go "off record" for a second time without actually trying to amend the error I get my displayed message again but then I get the "You cannot add or change a record because a related record is required in the table" suggesting that access is trying to update the record in question which indicates I'm not in complete control!

The error you describe is thrown by Referential Integrity. Are you trying to use the Transacted Bound Form with a subform?

Another AWF developer posted a couple of years ago about trying to work with a subform, preventing both the main records and related records from being written until the completion of the transaction. They got the same error.

I expect that Referential Integrity may be tested on the database before records are allowed onto a form based on a related table. If this is the problem I can't see an immediate way around it, since the parent records have not been written to the main table as they would be in a normal bound form.

Removing Referential Integrity wouldn't be wise.

 

[ryetee]

The error you describe is thrown by Referential Integrity. Are you trying to use the Transacted Bound Form with a subform?

Another AWF developer posted a couple of years ago about trying to work with a subform, preventing both the main records and related records from being written until the completion of the transaction. They got the same error.

I expect that Referential Integrity may be tested on the database before records are allowed onto a form based on a related table. If this is the problem I can't see an immediate way around it, since the parent records have not been written to the main table as they would be in a normal bound form.

Removing Referential Integrity wouldn't be wise.

No I'm not using a sub form. I a using the transacted bound form though but I'm pretty sure I've experienced this before - ie I can trap an error in record 1 when the user has tried to move to record 2 but if he ignores it and tries it again it let's him.

 

[ryetee]

An odd conclusion to jump to Pat. The clue that it isn't unbound is in the name "Transacted Bound Form". I thought it odd when you started on about the woes of unbound forms right under my post.

The technique avoids the woes of using a temp table and the associated potential for overwriting conflicting updates which are routinely handled by using normal bound forms. I recommend you take a look at how they work.

I think Pat may be referring to me.
Pat - I haven't yet started another thread but I will. You mentioned something about having complete control over the update - I have an instance where I don't.