Solved Prevent ms access to autosave data

[The_Doc_Man]

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.

IF you are going to have that multi-tiered approach AND you want it to remain "relational" then be sure to remember to add records in the correct order, because that "relational" status INCLUDES that you never ever create a child record BEFORE you create a parent record. Access DOES NOT ALLOW creation of a child record without a parent. This is why your unbound approach is going to continually cause you issues.

I'm not saying you can't do this. I'm saying that your vision of waiting to commit everything only after parent and child data have been entered completely will add to your overhead. Further, IF you make a mistake (or your users make one), at some point you will have to enter data in that layered approach of parent first, then child record etc. will mean you have to back out something. Take that process into account.

If you are going to expose this to users for data entry, I'll offer this advice: The best source of "testing to destruction" is the average user of any product, who will totally stress your system without even trying very hard to do so. It was a somewhat "dark humor" saying that we had around one of my software jobs many years ago that the only way to have a foolproof program was to never sell to fools. Therefore, on a serious note, please try to anticipate as many failure recovery modes as possible, because - basically you will need them.

 

[Pat Hartman]

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.

Experienced members have told you that you are using a RAD tool (Rapid application Development). The entire point of a RAD tool is to build applications quickly with NO code. Access does that extremely well by using bound forms. You only need to add custom code for things like data validation.

You, who have no experience, think you have a better method. Well, that's fine but you are using the wrong tool. You need to use a tool that lets you work without any help at all. Then you can do whatever you want. You will ultimately be able to coerce Access to your will but it will be a lot harder than using a different tool. Why use a tool that you don't like? Why use a tool that you have to fight with?

In a relational database you MUST save the parent record BEFORE you can save any child records. Unless of course you elect to forgo RI which no one in their right mind would recommend. That is why bound forms work the way they do. Access KNOWS that the parent record MUST be saved before you can add child records and so, being a good tool, makes sure that happens even when you don't know enough to do it yourself. As you are discovering in your logic, you MUST have a FK for the child records otherwise there is no way to corral them. But where to you get the FK if you haven't saved the parent record??????

Maybe you just don't understand how Access works. Here's a sample m-m relationship that shows how to work with either a child table or a popup form. In the popup, YOU have to populate the FK, whereas in a subform, as long as the master/child links are set, Access populates the FK for you like a good tool

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Ihk]

Point was 100% clear to me, when @arnelgp gave me an idea that temp table will reside locally. This sentence answered my all questions. Therefore, I will use access normal ways (Master/child relationship forms).
Update to main table and delete (from temp table) will be in my Save button. This approach is much better and will not affect data integrity. Regards,

 

[ebs17]

This approach is much better and will not affect data integrity

It depends on the specific implementation.
If you also create new data sets and use auto values as keys, and in multi-user operation a third party also creates new data sets in a timely manner, then simply appending will certainly create conflicts and violations of data integrity.

 

[Ihk]

It depends on the specific implementation.
If you also create new data sets and use auto values as keys, and in multi-user operation a third party also creates new data sets in a timely manner, then simply appending will certainly create conflicts and violations of data integrity.

Considering your point 2, mentioned before (temp table), I did this.

You are right, therefore Instead of update or append, I have tried Insert statement in demo db.
So, on save button, I used both statements (Insert and delete) - basically inserting data from temp to main table, and at the same clearing all from temp. (making it ready for new entry).
In this case if any other user does the same operation at any time, data integrity will not be violated.
Subform has FK linked to PK of main form

 

[gemma-the-husky]

Point was 100% clear to me, when @arnelgp gave me an idea that temp table will reside locally. This sentence answered my all questions. Therefore, I will use access normal ways (Master/child relationship forms).
Update to main table and delete (from temp table) will be in my Save button. This approach is much better and will not affect data integrity. Regards,

Yes, but it's not that. Using a temp table is not a standard treatment. It's a special treatment @arnelgp mentioned that might be worth considering or a distinct issue. You don't start out with this sort of idea. You get to that sort of idea when you have a deeper understanding.

@Pat Hartman had it right a few posts ago. You HAVE to create the parent before you create the child. The child is a child of a parent and cannot exist as an orphan. As I mentioned before, you need to go back to the beginning, and think about your data. Construct the correct table relationship, and the solution will virtually present itself.

And it will be master tables and child tables, where a master item has 0,1, or more related child items. There is such a thing as an orphan master record. That's just a master record with no related items. You can't have an orphan child record though.

In your case that would be like a comment that is not matched to any product. It can't happen. You can have a product with no comments, but you can't have a comment with no product. Or if you can, then the design needs reworking to represent the data differently where that possibility exists.

 

[Pat Hartman]

Point was 100% clear to me, when @arnelgp gave me an idea that temp table will reside locally. This sentence answered my all questions. Therefore, I will use access normal ways (Master/child relationship forms).
Update to main table and delete (from temp table) will be in my Save button. This approach is much better and will not affect data integrity. Regards,

Except that you don't know what you are getting into. How are you planning on doing updates or is the set of data not updateable once you move it to the production tables? Do you understand how to change the FE for the child records when you move them to the production table because you will have to?

It isn't that temp tables are not used, it is that they cause bloat in the FE if that is where they are or in the BE. Do you understand the different design you would need depending on where they reside? If the tables are in the FE and the user is mid-update and leaves it until tomorrow and you release a new FE version tomorrow, that will wipe out the users work in process which is why we never leave data in the FE for any reason.

There are so many gotcha's with what you want to do that I can't even begin to list them all. When you don't even know how Access actually works, this is way out of your league. If you knew how Access worked, you would know that you could use the "Access way" without a problem by just adding an Incomplete Flag to the parent record and simply ignoring, that set of data in any process EXCEPT the maintenance form. That way every process ignores incomplete records except two. The maintenance form that I mentioned and a new process you need to write to look for orphaned sets of data so you can prompt the user to complete them or delete them.

We have no idea why you think it is so important to add the child records first. It makes absolutely no sense whatsoever in a relational database.

 

[Ihk]

Yes, but it's not that. Using a temp table is not a standard treatment. It's a special treatment @arnelgp mentioned that might be worth considering or a distinct issue. You don't start out with this sort of idea. You get to that sort of idea when you have a deeper understanding.

@Pat Hartman had it right a few posts ago. You HAVE to create the parent before you create the child. The child is a child of a parent and cannot exist as an orphan. As I mentioned before, you need to go back to the beginning, and think about your data. Construct the correct table relationship, and the solution will virtually present itself.

And it will be master tables and child tables, where a master item has 0,1, or more related child items. There is such a thing as an orphan master record. That's just a master record with no related items. You can't have an orphan child record though.

In your case that would be like a comment that is not matched to any product. It can't happen. You can have a product with no comments, but you can't have a comment with no product. Or if you can, then the design needs reworking to represent the data differently where that possibility exists.

@gemma-the-husky
There is no record in my db as orphan, offcource Masters can be orphan otherwise child is always linked and have FK.

In your case that would be like a comment that is not matched to any product. It can't happen. You can have a product with no comments, but you can't have a comment with no product.

Yes offcourse it is like that in my case. This is how I designed, and made a different table to have multiple comments of a single product. This is only possible with FK.
These are very very basic things, master child, PK + FK , Always 1st is PK then FK.
I never said such such type of misconception in above, may be it is matter of misunderstanding. Regards,

 

[Ihk]

Except that you don't know what you are getting into. How are you planning on doing updates or is the set of data not updateable once you move it to the production tables? Do you understand how to change the FE for the child records when you move them to the production table because you will have to?

It isn't that temp tables are not used, it is that they cause bloat in the FE if that is where they are or in the BE. Do you understand the different design you would need depending on where they reside? If the tables are in the FE and the user is mid-update and leaves it until tomorrow and you release a new FE version tomorrow, that will wipe out the users work in process which is why we never leave data in the FE for any reason.

There are so many gotcha's with what you want to do that I can't even begin to list them all. When you don't even know how Access actually works, this is way out of your league. If you knew how Access worked, you would know that you could use the "Access way" without a problem by just adding an Incomplete Flag to the parent record and simply ignoring, that set of data in any process EXCEPT the maintenance form. That way every process ignores incomplete records except two. The maintenance form that I mentioned and a new process you need to write to look for orphaned sets of data so you can prompt the user to complete them or delete them.

We have no idea why you think it is so important to add the child records first. It makes absolutely no sense whatsoever in a relational database.

Yes your point is right, that if temp is in front end and user postpones half way.
But the things will not work like that, it can never happen in my case. It may vary from person to person, but in my case it will never happen. Though I have not explained above all this, how will I implement this (about temp in front end).
Let me explain but briefly
1) I used Insert function -- from temp table (FE) to main table (BE) but not update or append fucntion
2) On form close or application close, temp table data will be deleted .. It will no longer stay. So user can not postpone it.
3) It will only work via save button, If user will press save button then he can expect only that that I have my records otherwise should not expect any records
4) If save button is not pressed, there is no way for data to reside in temp table.

Further misclarification @Pat Hartman @gemma-the-husky
temp tables (both tables, either for producsts or comments) are acting as reall table for that form (Master form as well as Child form), both forms are linked by PK and FK. So as per standard MS Access procdure data will be saved in temp tables.
I hope this is clear....
Secondly, Main tables which are supposed to have data of temp tables, will have the same number of fields , same name, same datatype, same relation. ... So by save button data will be inserted in main tables in their respective fields.

I never repeat text from table to table, I only use Foriegn keys, with cascade........

 

[gemma-the-husky]

@gemma-the-husky
There is no record in my db as orphan, offcource Masters can be orphan otherwise child is always linked and have FK.

Yes offcourse it is like that in my case. This is how I designed, and made a different table to have multiple comments of a single product. This is only possible with FK.
These are very very basic things, master child, PK + FK , Always 1st is PK then FK.
I never said such such type of misconception in above, may be it is matter of misunderstanding. Regards,

But if you have a form showing products (single or continuous), and a subform showing comments - the comments shown will automatically be comments for the current master product. Access will link a single form automatically, but not a continuous form, but it's easy to do it with a continuous form. (Is that the issue?). You can then edit/add/delete comments for the active product. I still don't see why this needs to be any more complicated, and why you need to try to circumvent auto saving. I assumed because you were trying to avoid autosaving, you must have a "strange" database design.

Do you want the same comments to apply to multiple products. Then you need a products table, a comments table and a product-comments junction table, so the comment is no longer the child of the product table. This is no longer a 1-many master/child, but a many to many

so you get

products table 1 --------- m junction table m --------- 1 comments table

and you have a PK/Unique index in the junction table including both the productID, and the comment ID.
Then you can have a standard comment such as "ISO 9000 certified" that can apply to many products. (You could do this with a yes/no flag on the product, but it's just an example). You still don't need temp tables, it's just a different table structure. Now you can enter products and comments separately and manage them using the junction table. If you enter a product and need a new comment, just add it to the comments table, and then insert it into the junction table using the current product ID, and new Comments ID, if you need it to apply to the active product.

 

[arnelgp]

this is a sample of Transaction with Master/Child table (actually faking).
so it does not need additional Temp table.

 

[gemma-the-husky]

In passing, I often use temp tables for something like a general ledger journal voucher.

Users enter postings into the temp table, although it's still bound and fully relational. If the journal voucher sums to Zero they can accept the entry, and then the entire balanced double entry journal in the temp table us processed into the GL transaction table. If they scrap the entry, I just clear the temp table, and they can start over.

I often do the same with order entry, so users process an order header and order lines as a single process. However, if I had a system where adding order lines might lead to a stock out situation that could affect other users, then I might not (be able to safely) use this temp table approach.

 

[ebs17]

I think he comes up with a project with some details and details of details (3 tables, each linked 1:n).

Then everything should be transferred to the real tables in one piece or discarded completely, depending on the assessment of the whole.

Of course, this is a bit out of the norm, but rules also have exceptions, and sometimes the latter are justified.

 

[Pat Hartman]

it can never happen in my case.

Until it does

 

[Ihk]

this is a sample of Transaction with Master/Child table (actually faking).
so it does not need additional Temp table.

This is nice to edit / update both master and child records, and to add multiple child records. But this does not allow to add new records.
In my case it is about new records.
For example:
Master record:
PID
Product
etc

Child Record:
Which is its own unique as well as PID as FK in each row
Comments
This is comment 1
this is comment 2
this is comment 3
And then at the end save all and they are linked (Master child)

 

[Ihk]

But if you have a form showing products (single or continuous), and a subform showing comments - the comments shown will automatically be comments for the current master product. Access will link a single form automatically, but not a continuous form, but it's easy to do it with a continuous form. (Is that the issue?). You can then edit/add/delete comments for the active product. I still don't see why this needs to be any more complicated, and why you need to try to circumvent auto saving. I assumed because you were trying to avoid autosaving, you must have a "strange" database design.

Do you want the same comments to apply to multiple products. Then you need a products table, a comments table and a product-comments junction table, so the comment is no longer the child of the product table. This is no longer a 1-many master/child, but a many to many

so you get

products table 1 --------- m junction table m --------- 1 comments table

and you have a PK/Unique index in the junction table including both the productID, and the comment ID.
Then you can have a standard comment such as "ISO 9000 certified" that can apply to many products. (You could do this with a yes/no flag on the product, but it's just an example). You still don't need temp tables, it's just a different table structure. Now you can enter products and comments separately and manage them using the junction table. If you enter a product and need a new comment, just add it to the comments table, and then insert it into the junction table using the current product ID, and new Comments ID, if you need it to apply to the active product.

Nice idea of junction table.
In my case comments (multiple) will be specefic (linked) to a product. ie different for each product.

 

[arnelgp]

But this does not allow to add new records.

i just tested it and it does Add new record, both to Parent and Child.
After entering the parent and children, press you can Save it or Cancel it.

 

[Ihk]

i just tested it and it does Add new record, both to Parent and Child.
After entering the parent and children, press you can Save it or Cancel it.

The file which is attached above, Form1 is not acting as data entry form (as new record) - Parent form has record at ID 3 and will stay at ID 3. Only subform accepts new entries.

 

[arnelgp]

it is not acting as Data Entry?
did you press the New Record on the Navigation Button (bottom of form) to create New Record?
View attachment 104855
see this video:

 

[Ihk]

it is not acting as Data Entry?
did you press the New Record on the Navigation Button (bottom of form) to create New Record?
View attachment 104855
see this video:

No I did not went to navigation pan. Thanks a lot for your support and help.