Solved Prevent ms access to autosave data

[Ihk]

I am using following code to prevent access to autosave data, unless I press save button.
Form top

Private SaveRecord As Boolean

Form Current

SaveRecord = False

Form Before update

If SaveRecord = False Then
    Cancel = True
    Me.Undo
    Cancel = False
End If

cod on Save button (on click)
(along with other fields conditons)

SaveRecord = True

This works fine without any problem for one form (without subform).
Problem:
But I have problem, when I have subform
With above, If I jump to subform, data filled in Main form is deleted.
If I dont use above code, data of main form is saved before I type something in subform and then confirm to save records by pressing save button.
(I can undertand, the code before update is deleting Mainform records, but solution?)

How can I solve this issue to add data of Mainform and subform at the same time, after I press save button.
1) Prevent the main form to autosave
2) meanwhile I can add data to subform
Finally press save
Note: I dont want yes/no popup messages for confirmation.

 

[theDBguy]

You can use a temp table or an unbound form.

 

[MajP]

This can be done using transactions. See code from @arnelgp
https://www.access-programmers.co.u...-dont-save-bounded-fields.323435/post-1828834

I do not recommend this approach it is just seems utterly unnecessary. You are forcing Access to break the way it is designed to work. I just do not know In what world users create a record and then spend time to create multiple child records and then decide they do not want to save them. And they are more that happy to be forced to select save on each record? I guess you can come up with some kind of use case, but I do not get the point.

 

[The_Doc_Man]

The issue is "focus." The automatic operation for Access forms is that when a dirty form has focus and loses focus to a control on another form (or closes), that "dirty" data must be saved. That is the automatic behavior for bound forms. If your form is unbound, there is no place to save it so no save occurs. BUT if the main form is unbound, that makes the sub-form that much harder to coordinate with the main form since normally you bind the sub-form to the main form's primary key. Or something like that.

You are asking for Access to not do the thing that makes it so strong, and what you give up is all of the hand-holding and fully automatic data management. If you make everything unbound, then there is no automatic save... but if you actually WANTED to save some things under some circumstances, now you have to open a recordset for the main (and possibly for the sub) so that you can manually save the data you wanted to save. And in so doing, you lose ALL of the automated checking and validation and cross-form coordination.

If you NEVER EVER want to save the main form, then an unbound form makes sense. But if it is an "iffy" situation, your process design seems a bit questionable because it appears to have been made without appreciation for what Access actually does.

If you give us a 10,000 foot overview of the physical process (avoiding code discussions), maybe we could understand enough to advise you as to the best way to handle your data management problem.

 

[arnelgp]

you may also try to use Temp tables for you main and child records.
when it is time to save the record by clicking on the button, you Append/Update your
"real" tables from the temp table. and again "clear" the temp table, ready for next
data entry.

 

[MajP]

@arnelgp. Do you have a demo with subform records where you can cancel both the main and child records? I thought you demoed that.

 

[arnelgp]

Do you have a demo with subform records

unfortunately, i tried this and does not work (Transactions) when there is a subform.

 

[Ihk]

Thank you very much for all.
Now I could not stop myself to share my concept. May be some one give suggestions / inputs.
Main form (Dashboard) will have many other things, but along with that either right or left corner will have a button to open a form (which will slide in from respective side, top to bottom full height.) which will disappear by clicking elsewhere on dashboard/Main form.

Same concept for several other things.
Example below:
Let me talk one concept (one example)..... Lets say I have three tables
1) ProductT : (It will have only names), which will serve as combobox in another table ProductDetail
2) ProductDetailT : (Product name will be combo box here to add ID in this table)
3) ProductDetailComment : (why different table, because it will have multiple rows of comments. and any of those can be deleted by button.
this comment table should have an ID of ProductDetail table (as foriegn ID).

At the time of visualization of data, All data of single product will appear on one page with a listbox of comments on that page as well.

NOTE: In this app, I am not interested in popups, just everything embeded either subforms or listbox etc. (may be slide in and out).
How to implement / my thoughts:
because comments are in another table, therefore I need either subform or I should use tab control, so that while adding the Detail of a product, someone can add comments of that at the same time as well.
My pictures will describe..........................
May some one can give me input or suggestions. I will be thankful.
concept one:

 

[Ihk]

This can be done using transactions. See code from @arnelgp
https://www.access-programmers.co.u...-dont-save-bounded-fields.323435/post-1828834

I do not recommend this approach it is just seems utterly unnecessary. You are forcing Access to break the way it is designed to work. I just do not know In what world users create a record and then spend time to create multiple child records and then decide they do not want to save them. And they are more that happy to be forced to select save on each record? I guess you can come up with some kind of use case, but I do not get the point.

This is true and I agree. But sometimes we want to add primary ID of a table as forigen ID in another table.
Like I have described my concept.
I want to add ID from ProductDetail table to Product comment Table (as foriegn ID), this will help me to fetch all comments for that sepecfic product.

 

[MajP]

Why is product detail in a separate table? Can you have more than one detail record (multiple brands or suppliers)? If not this information is unique to that product and should be in the same table as the product name.

This is true and I agree. But sometimes we want to add primary ID of a table as forigen ID in another table.
Like I have described my concept.
I want to add ID from ProductDetail table to Product comment Table (as foriegn ID), this will help me to fetch all comments for that sepecfic product.

So what? What does that have anything to do with not saving the record? This is the opposite of doing that. That tells me you need the PK of the product saved?
If you delete the product then cascade deletes, deletes the comments?

 

[Ihk]

As explained in Post#8

So what? What does that have anything to do with not saving the record?

Comments are multple against on record from product detail.
Therefore it (comments) has different table).
When it comes as subform, this is where problem starts.

2ndly.

Why is product detail in a separate table?

Any one wont have to type the product name each time.
Prod Detail will store its varient, and sources (supplier ID), etc..

 

[MajP]

Any one wont have to type the product name each time.
Prod Detail will store its varient, and sources (supplier ID), etc.

So Prod Details is a 1 to many? So a different supplier may supply the same product in different dimensions?

 

[Ihk]

So Prod Details is a 1 to many? So a different supplier may supply the same product in different dimensions?

Yes Exactly . .
-Same product can be from many suppliers.
-Different products can be from same supplier.
-Each product can have different dimensions/varient....
-much more

But this all is not a problem....

The point which I am making here is....
Multiple line comments for same products..
To achieve this..
I made a separate table for prod comments, which will have id of product (detail) table as FK.
So it will have one to many (prod detail to prod comment).
How to add this..
While we add product detail, we want to add comments linked to that Article/product at the same time..
As shown in post#8.
Because these are two different tables (prod detail and prod comments)..
Therefore it can only be practically done via either tab controls or via subform.
This is where the problem begins and I started this post..

 

[ebs17]

What you should understand: Storage in tables is data record-by-record, i.e. always for one data record.

A bound form represents table content. The current and edited record in the form is automatically saved when the focus changes, or you must discard the changes.
Therefore you cannot keep a group of newly created or changed data records in the status "not yet saved" at the same time, not even across several forms.

Only way out:
1) You are working with an unbound form. There you can understand existing groups of provided controls as data sets. It only becomes correct data records when you transfer the contents into correct tables, here you are responsible for this.
2) You work with bound forms whose data lineage are temporary copies of the real tables. Then, when you are satisfied with your input process, you transfer the contents of the temporary tables to the real tables. You would also have to be able to do this manually if several tables are linked via keys and keys in real tables could also be changed by third processes (multi-user operation).

So your great idea cannot be implemented as planned.

 

[gemma-the-husky]

@Ihk

I haven't read the thread completely.

My recommendation is not to consider this. Experienced users don't expect this when they use Access. Access is multi-user and works on the basis that data changes are immediately available to all users as soon as they are saved, and there is no "undo" option. Access tries very hard to save record changes, and it's actually difficult to prevent this. You would have to effectively cancel every before update event, and only save records using your save button. It's much better to work with access and use the standard access facilities.

Either that, or always use unbound forms, so there is no automatic record save, which will be very much harder to develop.

Even removing the standard close button on a form is something that should be used sparingly. It you don't have a close button on a form, it automatically means you are doing something unusual. Sometimes it makes sense, but not very often.

On a serious point. Databases are not about interface design. They are about data management. What you need is a set of normalised data tables to store your data, and then the form and interface development will become harmonious and much easier. You can add all the bells and whistles then. Show us your data tables and relationships. That's what we need to review with you. The problems you are having must be a result of incorrect data analysis.

 

[Ihk]

At 1st I must have to say thank you to all, because I learnt from this post like every day learning.
I will try to implement after considering practical possible situations.
Temp table, will cause problems in Multiuser case , because it will be a multiuser app. Because what if another user makes and another user deletes temp table records.

In that case, unbound form can be a better approach, then a lot to do for that to make sure the records are correct and remain relational.
Nevertheless, unbound form is best conclusion.
Once again thank you all, and Kind regards.

 

[arnelgp]

what if another user makes and another user deletes temp table records.

the temp table resides "locally" (on FE).

 

[Ihk]

the temp table resides "locally" (on FE).

Okay, thats also nice to know... then this will not harm... so cool..

 

[NauticalGent]

Ihk, I have to commend you on your willingness to learn. We get a lot of new members who THINK they know what they want and HOW they want to do it.

When more experienced members, members who have traveled down the same road you are travelling on, try to warn them, they get quite upset and want to argue, get their feelings hurt and go away.

You have the right attitude and will go far in your quest to become a proficient Access user...

 

[Ihk]

Ihk, I have to commend you on your willingness to learn. We get a lot of new members who THINK they know what they want and HOW they want to do it.

When more experienced members, members who have traveled down the same road you are travelling on, try to warn them, they get quite upset and want to argue, get their feelings hurt and go away.

You have the right attitude and will go far in your quest to become a proficient Access user...

Thats totally right. And thank you for appreciation.
When a member like me posts here, so Experienced members comment here to help us.
Even if they argue or whatever, basically they have taken time to guide us (me) for no reward.
So it is my duty to respect all those and it should be like that.