Hi, I know there are quite a few posts on the forum already about how to disable autosave in access. But I am an absolute noob in Access and still have no idea how to disable autosave after reading all the threads... Can someone post a detailed, step by step guide on how to do this?
You're trying to reinvent the way Access is intended to work, and this becomes a problem with experienced Access data input users. They know that Access saves records when moving to another record or when the form is closed, and expect this behavior. In my opinion, it's a better policy to allow Access to work in its native way and merely check with the user before the record is saved, allowing them to save it or dump the new record or changes. This piece of code will do just that
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub
You're trying to reinvent the way Access is intended to work, and this becomes a problem with experienced Access data input users. They know that Access saves records when moving to another record or when the form is closed, and expect this behavior. In my opinion, it's a better policy to allow Access to work in its native way and merely check with the user before the record is saved, allowing them to save it or dump the new record or changes. This piece of code will do just that
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub
You don't say what version of Access you're using, but in Versions 2003 and older
Copy the code I posted
From Form Design View Press <Ctrl> + <G>
You're now at the Code Module for the Form
Beneath current code, if any, Paste the code in
Save the File
Back out of the Code Module
If you're using Versions 2007/2010, make sure that your File is in a 'Trusted" location. You have to do this, in these versions, in order for Code to run.
You should be set. If you have any problems, post back.
You don't say what version of Access you're using, but in Versions 2003 and older
Copy the code I posted
From Form Design View Press <Ctrl> + <G>
You're now at the Code Module for the Form
Beneath current code, if any, Paste the code in
Save the File
Back out of the Code Module
If you're using Versions 2007/2010, make sure that your File is in a 'Trusted" location. You have to do this, in these versions, in order for Code to run.
You should be set. If you have any problems, post back.
I have 2002 and I went to the design view of the form, pressed ctrl+G, it looks like I entered Visual Basic and the cursor is at "Immediate" window. And where do I insert code/module? Is the "immediate" window the Code window?
No, teh Immediate Window, in the Code Module, is for 'immediately testing code.' Either just close that window, with the X in the upper, right-hand corner of the small window, or click into the white space above it and below the line Option Compare Database.
No, teh Immediate Window, in the Code Module, is for 'immediately testing code.' Either just close that window, with the X in the upper, right-hand corner of the small window, or click into the white space above it and below the line Option Compare Database.
"option compare database" as in "Module"? I pasted the code underneath "option compare database" and saved the module, however Access still doesn't prompt me with any warning after I make changes and close the program...
If you've pasted the code into a Module by going into the Objects Dialog Box, where you see, down the left-hand side
Tables
Queries
Forms
Reports
Pages
Macros
Modules
and then selecting Modules and Create, you've created a Standard Module! You can still see the Immediate Window, from here, and you'll see Option Compare Database, as well, but you're in the wrong place!
The code needs to be in the Form's Code Module.
When you have the code that you've pasted in, staring you right in the face, and you look up at the at the very top of the module window, to the left, do you see something like
Code:
Microsoft Visual Basic DB_Name [B]Module[/B]Name (Code)
or
Code:
Microsoft Visual Basic DB_Name [B]Form[/B]Name (Code)
And I guess you might as well copy and post your exact code here.
If you've pasted the code into a Module by going into the Objects Dialog Box, where you see, down the left-hand side
Tables
Queries
Forms
Reports
Pages
Macros
Modules
and then selecting Modules and Create, you've created a Standard Module! You can still see the Immediate Window, from here, and you'll see Option Compare Database, as well, but you're in the wrong place!
The code needs to be in the Form's Code Module.
When you have the code that you've pasted in, staring you right in the face, and you look up at the at the very top of the module window, to the left, do you see something like
Code:
Microsoft Visual Basic DB_Name [B]Module[/B]Name (Code)
or
Code:
Microsoft Visual Basic DB_Name [B]Form[/B]Name (Code)
And I guess you might as well copy and post your exact code here.
Thanks and really useful this.
here i have one request. how to save multiple record at a time.?
For eg:-
Table: tblMaterials
Form: FrmMaterials (Datasheet mode with data entry)
if i entry more than one record by using your VBA, its asking for save on each time,
Therefore i need to avoid such disturbance while entering more data s
Dear Mr. JHB
First of all thanks for your grand support.
I make one multiple entry forms for creating new materials in the database
When I am entering more than one records in this form by using below VBA code, Its appearing save messages on updating each rows.
Here I want to know how to save all records at one time with Click on Save button, shown as attachment
Please help me and Kindly see the attachment.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub
