When saving, avoid prompting on cmdSave, show for everything else.

[Adnan]

I got the following code from MSDN. Works great, but I have a cmdSave button,
now everytime I hit the buttun, it promts me to save. The question is, how
can I avoid this promt message when I hit the save button? (avoid prompting
it when clicked to save, show for everything else).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then
' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End Sub



Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

 

[John Spencer]

Add a class module variable to your routine.

Option Compare Database
Option Explicit

Dim tfManualSave as Boolean

'all the other code follows
In the cmdSave button's event add

tfManualSave = True

In the form's BeforeUpdate event add

If tfManualSave = True Then
tfManualSave = False
Else
'your present code here
End If


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Adnan]

John

I do appreciate your neat response but I think I am missing something on
steps you listed below. Here’s what I did:


Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String
Dim iResponse As Integer

If tfManualSave = True Then
tfManualSave = False
Else
'your present code here
' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then
' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End If

End Sub



-------------------

Private Sub lblSave_Click()
On Error GoTo Err_lblSave_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

tfManualSave = True

Exit_lblSave_Click:
Exit Sub

Err_lblSave_Click:
MsgBox Err.Description
Resume Exit_lblSave_Click

End Sub



----------------
and I created a class module and this is what I have listed in it:

Option Compare Database
Option Explicit

Dim tfManualSave As Boolean




I know I missed something because this did not make any diference, yet.

Once again, thank youuu!
Adnan

 

[BruceM]

The form's code module is the place to add the variable declaration. You
would not create a new class module. A class module is the code module (the
collection of code) for a form or report. There are other uses for class
modules, but I have to admit I don't quite understand that concept.
However, since you are not declaring a Public variable (which would be
needed if you were declaring a variable outside of the current form's code
module) I expect the idea was that the variable declaration belongs in the
form's class module.

This line of code:
tfManualSave = True
needs to come before whatever happens in the DoCmd.DoMenuItem line of code.
By the way, if you are saving the record all you need is:
Me.Dirty = False

Some people prefer:
If Me.Dirty Then
Me.Dirty = False
End If

or

If.Me.Dirty Then Me.Dirty = False

Any of those are easier to understand when reading the code, too.

Anyhow, the idea is that you set the variable tfManualSave = True to True
*before* saving the record. Saving the record triggers the form's Before
Update event. If it is True the Before Update code resets it to False and
skips straight to the end. If you set it to True *after* saving the record
it will still be true when you go to another record. It will remain True
until the next time you update a record by any means (by navigating to a new
record, clicking the Save button, etc.). However, the fact that it is True
means the Before Update code will be skipped for that next record, no matter
how you save it (navigating to another record, clicking the Save button,
etc.).

 

[Adnan]

Bruce,

The explanation makes sence. But for some reason I still keep getting the
message promting me to save when i click lblSave.

Here's what I have:



Private Sub lblSave_Click()

Dim tfManualSave As Boolean

tfManualSave = True

If Me.Dirty Then
Me.Dirty = False
End If

End Sub




Public Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String
Dim iResponse As Integer
Dim tfManualSave As Boolean

If tfManualSave = True Then
tfManualSave = False
Else
'your present code here
' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then
' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End If

End Sub



Thanks for you help sir,
Adnan

 

[BruceM]

At the very top of the code window for the form you should see:
Option Compare Database
Option Explicit

Right after that, declare your variable:
Dim tfManualSave As Boolean

The trouble is that you declared the variable within the Click event, but
when you do that the variable exists only within that event, so it is not
available to the Before Update event. By declaring the variable at the top
(which is often referred to as the Declarations section of the module) it is
available to all procedures within that module.

 

[Adnan]

Bruce,

It wroks now. Thank you so much .

At the very top of the code window for the form you should see:
Option Compare Database
Option Explicit

Right after that, declare your variable:
Dim tfManualSave As Boolean

The trouble is that you declared the variable within the Click event, but
when you do that the variable exists only within that event, so it is not
available to the Before Update event. By declaring the variable at the top
(which is often referred to as the Declarations section of the module) it is
available to all procedures within that module.

 

[Adnan]

I thank you for your help, John!

Add a class module variable to your routine.

Option Compare Database
Option Explicit

Dim tfManualSave as Boolean

'all the other code follows
In the cmdSave button's event add

tfManualSave = True

In the form's BeforeUpdate event add

If tfManualSave = True Then
tfManualSave = False
Else
'your present code here
End If


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[BruceM]

Glad to hear it. The idea should come in handy in the future, too.