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
951 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

12 Experts available now in Live!

Get 1:1 Help Now