Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to avoid the prompt "save changes to form..." in Access 2007 when you have changed someting in Datasheet View?

Posted on 2010-09-06
Medium Priority
Last Modified: 2013-11-28
I have an Access 2007 application running a 2003 MDB.
When users are changing the column layout in datasheet view (adding/removing columns), they are asked if they would like to save changes when closing the form. This was not the case in access 2003.
How do I stop this prompt, I seem not to able to intervene before the standard prompt "Save chages to the form" is coming?
If I could intervene I wolud be happy to give the users the possibility to store their sepsific datasheet layout, but then I would like to give a more understandable question....
Question by:Joar
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33614147
Please be clear...
What "Format" is this database in?

The Access 2007 format (.accdb) or the 2003 format (.mdb)

Be very careful when running Access 2003 format databases under Access 2007.
There are many changes that are Access 2007 specific, that users may be able to make that may cause issues down the line.

1. IMHO, you want the message to always appear.
I was always prompted to save changes like this in Access 2003, so I could not reproduce your issue of Access 2003 NOT asking to save design changes to a tables.
(This seems suspicious in and of itself though, and should be investigated.)

Can I ask why you are allowing users to add/remove columns?
This should be a developer task...

Or you can use a Form with the Default View property set to "datasheet View".
(not allow the users direct access to a table)
Then you can create an interface that asks then what columns that want displayed before opening the form, to avoid this issue altogether.


LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 33614193

If you close the form from VB, use this line:

DoCmd.Close acForm "frmName", acSaveNo

Author Comment

ID: 33615372
To boag2000:
I said the db format was 2003 mdb....
This client have been running Access 2003 for years, and now upgraded to Access 2007, while I as a developer upgarded to 2007 some years ago.
I am a bit anxious of converting from mdb to accdb because of all the rewriting of code I expect?

In my app users are allowed to switch from form to datasheet view.
I am not allowing users to add/remove columns in the table, just hiding/unhiding columns in the datasheet view.
Access 2003 didn't ask for saving changes in the form if you did hide/unhide, while Access 2007 does.
Thats the problem, because it is annoying the user.
I understand your advice is to write a special interface to solve this problem.
May be its a good solution, but are you sure this will surpress the save prompt?

To GRavL:
Where shall I put this docmd.close code, if the user closes the window or closes the entire application? Do I have to substitute the standard close-icons (the X in the upper right corner) with a close button? I have tried all the events in the form and noen of them do execute before the "Save changes.." comes.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33615730
In your original post you stated:
    When users are changing the column layout in datasheet view (adding/removing columns) they are asked if they would like to save changes when closing the form.

Now you say:
    I am *not* allowing users to add/remove columns in the table, just hiding/unhiding columns in the datasheet view.

So you can see why I was confused originally.

So again, if you created a form to allow them to select the columns to display before the form opens, then this may solve the issue also.

But I think GrayL has the most straightforward solution.



Author Comment

ID: 33737869
Understand, when I said add/remove I ment inhide/hide.
I see that your solution is one way to go.
But my customers are using this datasheet method in many forsms and then I have to write special "datasheet" form for each form in the application or write some intelligent form handling all forms?

May be I will try the GrayL method
LVL 44

Expert Comment

ID: 33738685
If you had a button cmdClose on the form, in the Click event of the button

Private Sub cmdClose_Click()
 DoCmd.Close acForm "frmName", acSaveNo
End Sub

substitute your real form name for "frmName"

Author Comment

ID: 33741140
Thank you! seems to be the solution!

Author Closing Comment

ID: 33741148
LVL 44

Expert Comment

ID: 33747409
Thanks, glad to help.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question