Ms Access Unnexessary Save Record Or Drop Changes Dialog Box (1 Viewer)

taifoor · Jun 27, 2017

Dear All,

I have a form with a datasheet subform. When user adds data on form, the subform data is programmed to change with the help of some queries. BUT then when user manaually do some chnages in subform, ms access give dialog of either save record or drop changes. It seems like ms access take query changes as changes made by some other user, but actually the changes are by same user due to queries.

So, my question is how can I avoid that dialog box and make Ms Access to always save record and never give user option of dialog box to drop changes.

Ranman256 · Jun 27, 2017

The subform should be a table, or a simple query LINKED to the master form on a key field.
Is this the case?
What is the query?

taifoor · Jun 27, 2017

Ranman256 said:
The subform should be a table, or a simple query LINKED to the master form on a key field.
Is this the case?
What is the query?

Yes. The subform is a query based on another table and is linked with Master Form using Parent/Child Link.

The master form query is Invoice #, Invoice date, Customer Name etc and subform query is Qty, Rate etc etc. Got it?

Now I use to change data of subform based on Customer entered on master form. When user adds/change customer on master form, some queries run and change the data of subform showing only Sales Order Lines of selected customer. GOT IT?

gemma-the-husky · Jun 27, 2017

BUT then when user manually do some changes in subform, ms access give dialog of either save record or drop changes.

You DO NOT get this message in normal circumstances. You have got this because you have the same record open in 2 different places, and Access is warning you about this. Therefore either, save these changes, which will cause problems with the other record, or drop these changes.

It might be that you have a multi-user system, and more than one person has edited the same record, but as often as not, the second person is yourself - and it's a programming issue/system design issue.

You can easily see this - you have a form open, and change something. Then you open another form to see some other details for the same record, and change that also.

taifoor · Jun 27, 2017

gemma-the-husky said:
You DO NOT get this message in normal circumstances. You have got this because you have the same record open in 2 different places, and Access is warning you about this. Therefore either, save these changes, which will cause problems with the other record, or drop these changes.

It might be that you have a multi-user system, and more than one person has edited the same record, but as often as not, the second person is yourself - and it's a programming issue/system design issue.

You can easily see this - you have a form open, and change something. Then you open another form to see some other details for the same record, and change that also.

I havent opended same record on two seperate form. As i said that its beacause of queries that are run automatically by changing customer.

My concern is to make default save record and no dialog be shown. I think it can be acheived by setting query record lock setting etc or by suppressing error msg like by docmd.setwarnings = false (that not works here).

taifoor · Jun 28, 2017

Anyone here who can help me out.

isladogs · Jun 28, 2017

As previously stated this is not normal behaviour.
The issue occurs because of something in the way your database is designed

For example:
a) the underlying table is open elsewhere e.g. in a form / query or recordset
b) the form locking options are too restrictive - if set to All Records or Edited Record change to No Locks
c) there is an issue with table design
d) data corruption has occurred

Several years ago, I experienced this issue on numerous apparently random occasions after upsizing the BE datafile to SQL server.
After a LOT of fruitless efforts to fix I realised this occurred when I tried to save data in tables with one or more boolean fields where no default value had been set.
One I realised this, the solution was easy - add a default value in ALL boolean fields in ALL tables
In other words, I fixed a design error

BTW you won't get that particular issue if using an Access BE as Access 'assumes' a boolean field value is false if its not completed.
SQL server is more picky - boolean fields are treated as null when left blank without a default value. This means boolean fields can actually have a triple state in SQL server which is fine in itself but Access gets confused.

Now unless you just hit VERY lucky, that reason will not be the cause of your problems.

If the cause is d) above, try creating your form again (if its only that one form triggering the message) in case its corrupted.
If that doesn't fix it, backup the db then go through the usual route of compiling, compacting, decompiling, compiling and compacting again.

Then, if its still an issue create a new blank db and copy all objects to that. Doing so will correct any errors in the hidden system table MSysObjects

OR you could try CAREFULLY repairing that table - see the link for more info
How to remove deleted database objects from the MSysObjects table
Make sure you backup the frontend first

Ultimately, its down to you to dig down to identify the issue.
It may take time & only you can do it with your data.
However, if you put it off, the problem will not go away ...

taifoor · Jun 28, 2017

ridders said:
As previously stated this is not normal behaviour.
The issue occurs because of something in the way your database is designed

For example:
a) the underlying table is open elsewhere e.g. in a form / query or recordset
b) the form locking options are too restrictive - if set to All Records or Edited Record change to No Locks
c) there is an issue with table design
d) data corruption has occurred

Several years ago, I experienced this issue on numerous apparently random occasions after upsizing the BE datafile to SQL server.
After a LOT of fruitless efforts to fix I realised this occurred when I tried to save data in tables with one or more boolean fields where no default value had been set.
One I realised this, the solution was easy - add a default value in ALL boolean fields in ALL tables
In other words, I fixed a design error

BTW you won't get that particular issue if using an Access BE as Access 'assumes' a boolean field value is false if its not completed.
SQL server is more picky - boolean fields are treated as null when left blank without a default value. This means boolean fields can actually have a triple state in SQL server which is fine in itself but Access gets confused.

Now unless you just hit VERY lucky, that reason will not be the cause of your problems.

If the cause is d) above, try creating your form again (if its only that one form triggering the message) in case its corrupted.
If that doesn't fix it, backup the db then go through the usual route of compiling, compacting, decompiling, compiling and compacting again.

Then, if its still an issue create a new blank db and copy all objects to that. Doing so will correct any errors in the hidden system table MSysObjects

OR you could try CAREFULLY repairing that table - see the link for more info
How to remove deleted database objects from the MSysObjects table
Make sure you backup the frontend first

Ultimately, its down to you to dig down to identify the issue.
It may take time & only you can do it with your data.
However, if you put it off, the problem will not go away ...

Dear Ridders,

I have already mentioned two times that I have identified what causing the dialog box to appear. Again I am saying that its because of some queries of Delete and append. If i make that queries to not run, then all things are normal. So, i am sure that these queries are changing records of subform that MS access cant recognise that its by the same user. Moreover, the datasource of form and subform are LOCAL tables (not Linked tables). means no other user can access these tables.

So, its better to give solution to only mentioned problem rather than assuming multiple issues and answering them all (Like u give solution of data corrupt)

BUT one solution by u seems relevant for me i.e. setting lock settings of form to no lock. I will apply that and will share with u if this solves my problem. Thanks for ur help.

taifoor · Jun 28, 2017

Dear Ridders,

Unfortunatley, the record lock setting of Main form as well as of subform is "No Locks".
Also I have tried many ways to solve this issue but no luck. One thing that i used is sendkey "s" that make that dialog vanish when it appears. But that seems to me not a good solution.

isladogs · Jun 28, 2017

So, its better to give solution to only mentioned problem rather than assuming multiple issues and answering them all (Like u give solution of data corrupt)

OK so you said when it occurred BUT not why.
Being as you hadn't solved it, I gave you various options to consider.
If you knew the answer, you wouldn't be asking

taifoor · Jun 28, 2017

ridders said:
OK so you said when it occurred BUT not why.
Being as you hadn't solved it, I gave you various options to consider.
If you knew the answer, you wouldn't be asking

Dear Ridders,

I mentioned even in my main post that its becuase of queries being run. So it is WHY the dialog box is appearing. If no queries are run, no dialog box.

I appreciate your options are definitely they helpful to others. In my case, i am still pondering of a solution.

gemma-the-husky · Jun 28, 2017

Let's say you have a form open with a dirty record.

put record selectors on the form, and you should see a pencil, not a triangle.

now you run some update queries. If the query tries to update any field on the record in your form, you get the error you describe.

I have no doubt that is the cause.

So, before you run the updates, do

if me.dirty then me.dirty = false

as long as the record can save, you won't get the issue.

taifoor · Jun 30, 2017

gemma-the-husky said:
Let's say you have a form open with a dirty record.

put record selectors on the form, and you should see a pencil, not a triangle.

now you run some update queries. If the query tries to update any field on the record in your form, you get the error you describe.

I have no doubt that is the cause.

So, before you run the updates, do

if me.dirty then me.dirty = false

as long as the record can save, you won't get the issue.

Dear gemma-the-husky,

I applied your tech and while going thru that, i came to know that queries are not only creating issue. I would like to tell u in detail about my form structure.

I have Main form with 3 subforms. The 3 subforms record source is same table but their records are filtered by queries. Means no subform shows similar records. I thing this is creating issue. I used a command in VBA i.e. accmdRefreshPage to save records in whole form. While running this command, the said dialog appears i think.

Ms Access Unnexessary Save Record Or Drop Changes Dialog Box (1 Viewer)

taifoor

Registered User.

Ranman256

Well-known member

taifoor

Registered User.

gemma-the-husky

Super Moderator

taifoor

Registered User.

taifoor

Registered User.

isladogs

MVP / VIP

taifoor

Registered User.

taifoor

Registered User.

isladogs

MVP / VIP

taifoor

Registered User.

gemma-the-husky

Super Moderator

taifoor

Registered User.

Similar threads

Users who are viewing this thread