Solved

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
9
953 Views
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....
0
Comment
Question by:Joar
  • 4
  • 3
  • 2
9 Comments
 
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.

;-)

JeffCoachman
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 33614193

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

DoCmd.Close acForm "frmName", acSaveNo
0
 

Author Comment

by:Joar
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.
0
 
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.

;-)

JeffCoachman
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Joar
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
0
 
LVL 44

Expert Comment

by:GRayL
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"
0
 

Author Comment

by:Joar
ID: 33741140
Thank you! seems to be the solution!
0
 

Author Closing Comment

by:Joar
ID: 33741148
Solved
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33747409
Thanks, glad to help.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now