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

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

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.

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

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

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"

Thank you! seems to be the solution!

Solved

Thanks, glad to help.