Coding for navigatin does not work.

[injanib via AccessMonster.com]

I have a next button on my form that is supposed to prompt if I want to save
before navigating. I have the following VBA, but it does not do anything. It
does not give me any error either.
I can't figure out what is wrong with the coding.

Private Sub cmdNext_Click()
If Me.NewRecord Then
If Me.Dirty Then
If MsgBox("Do you want to save this record?", vbYesNo, "Save Record?")
= vbNo Then
Me.Undo
DoCmd.GoToRecord , , acNext
End If
End If
Else
If Me.Dirty Then
If MsgBox("Do you want to save the changes?", vbYesNo, "Save Changes?
") = vbNo Then
Me.Undo
DoCmd.GoToRecord , , acNext
End If
End If

End If

End Sub

 

[Dirk Goldgar]

I have a next button on my form that is supposed to prompt if I want to
save
before navigating. I have the following VBA, but it does not do anything.
It
does not give me any error either.
I can't figure out what is wrong with the coding.

Private Sub cmdNext_Click()
If Me.NewRecord Then
If Me.Dirty Then
If MsgBox("Do you want to save this record?", vbYesNo, "Save
Record?")
= vbNo Then
Me.Undo
DoCmd.GoToRecord , , acNext
End If
End If
Else
If Me.Dirty Then
If MsgBox("Do you want to save the changes?", vbYesNo, "Save
Changes?
") = vbNo Then
Me.Undo
DoCmd.GoToRecord , , acNext
End If
End If

End If

End Sub

The way the code is written, it won't do anything at all unless the current
record has been modified by the user, and it won't go to the next record
unless the user replies "No" to the prompt.

What exactly do you want the button to do? Do you want it always to go to
the next record, and if the current record was modified, prompt the user and
either save the changes or discard them depending on the user's response?

 

[injanib via AccessMonster.com]

exactly.

When the user clicks on it, and if it is a new record, I want it to ask the
user if he wants the current record saved before going to the next record.
And if it is an existing record that has been modified, I want it to prompt
for saving changes before goning to the next record.

I have a next button on my form that is supposed to prompt if I want to
save

[quoted text clipped - 26 lines]

End Sub

The way the code is written, it won't do anything at all unless the current
record has been modified by the user, and it won't go to the next record
unless the user replies "No" to the prompt.

What exactly do you want the button to do? Do you want it always to go to
the next record, and if the current record was modified, prompt the user and
either save the changes or discard them depending on the user's response?

 

[injanib via AccessMonster.com]

Haah! I am laughing at myself. I didn't think this through before I coded it.
You made rethink. I got this now. Thanks for the reply.

exactly.

When the user clicks on it, and if it is a new record, I want it to ask the
user if he wants the current record saved before going to the next record.
And if it is an existing record that has been modified, I want it to prompt
for saving changes before goning to the next record.
[quoted text clipped - 9 lines]

the next record, and if the current record was modified, prompt the user and
either save the changes or discard them depending on the user's response?

 

[Dirk Goldgar]

exactly.

When the user clicks on it, and if it is a new record, I want it to ask
the
user if he wants the current record saved before going to the next record.
And if it is an existing record that has been modified, I want it to
prompt
for saving changes before goning to the next record.

Then you want the code to be something like this:

'------ start of revised code ------
Private Sub cmdNext_Click()

Dim intResponse As Integer

If Me.Dirty Then

If Me.NewRecord Then

intResponse = MsgBox( _
"Do you want to save this record?", _
vbQuestion+vbYesNo, _
"Save Record?")

Else

intResponse = MsgBox( _
"Do you want to save the changes?", _
vbQuestion+vbYesNo, _
"Save Changes?")

End If

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False
End If

End If

' Whether we saved this record or not, go to the next one.

DoCmd.GoToRecord , , acNext

End Sub

'------ end of revised code ------

 

[injanib via AccessMonster.com]

Thanks Dirk,

I used your codes and it works perfect except that it does not give me
prompts made to my subform. What do I need to change?

exactly.

[quoted text clipped - 4 lines]

prompt
for saving changes before goning to the next record.

Then you want the code to be something like this:

'------ start of revised code ------
Private Sub cmdNext_Click()

Dim intResponse As Integer

If Me.Dirty Then

If Me.NewRecord Then

intResponse = MsgBox( _
"Do you want to save this record?", _
vbQuestion+vbYesNo, _
"Save Record?")

Else

intResponse = MsgBox( _
"Do you want to save the changes?", _
vbQuestion+vbYesNo, _
"Save Changes?")

End If

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False
End If

End If

' Whether we saved this record or not, go to the next one.

DoCmd.GoToRecord , , acNext

End Sub

'------ end of revised code ------

 

[Dirk Goldgar]

Thanks Dirk,

I used your codes and it works perfect except that it does not give me
prompts made to my subform. What do I need to change?

I'm afraid I don't understand you. Could you explain what you mean, please?

 

[injanib via AccessMonster.com]

The form on which I am using the code has a subform too. The code gives me
the proper prompt if I make a change on the main form, but not on the subform.
Meaning that if I make a change in the subform and then press the navigating
buttons, it does not prompt whether I want to save the changes or not. It
automatically saves them and moves on.

 

[AccessVandal via AccessMonster.com]

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False
End If

End If

' Whether we saved this record or not, go to the next one.

DoCmd.GoToRecord , , acNext

End Sub


Dirk,

As I recalled, it not possible to move to the next record if the Response is
"No" as the current record is already new.

Maybe a minor change to this might work.

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False

' we saved this record, go to the new record.
DoCmd.GoToRecord , , acNewRec

End If

End If

 

[Dirk Goldgar]

As I recalled, it not possible to move to the next record if the Response
is
"No" as the current record is already new.

Maybe a minor change to this might work.

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False

' we saved this record, go to the new record.
DoCmd.GoToRecord , , acNewRec

End If

End If

You make a good point. I wasn't trying to resolve all the possible problems
with the code, just get it to work as outlined. But it wouldn't have hurt
to cover all the bases. However, I think you've missed one possibility
also. If we want to avoid any error when the button is clicked, we need to
allow for the possibility that the use is on a new, unmodified record.
Here's a possible revision:

'------ start of revised code ------
Private Sub cmdNext_Click()

Dim intResponse As Integer

If Me.Dirty Then

If Me.NewRecord Then

intResponse = MsgBox( _
"Do you want to save this record?", _
vbQuestion+vbYesNo, _
"Save Record?")

Else

intResponse = MsgBox( _
"Do you want to save the changes?", _
vbQuestion+vbYesNo, _
"Save Changes?")

End If

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False
End If

End If

' Whether we saved this record or not, go to the next one
' (unless we're on a new record already).

If Me.NewRecord Then
DoCmd.Beep
Else
DoCmd.GoToRecord , , acNext
End If

End Sub
'------ end of revised code ------

However, I see from injanib's reply in this thread that just coding the
cmdNext button may not be enough, anyway.

 

[Dirk Goldgar]

The form on which I am using the code has a subform too. The code gives me
the proper prompt if I make a change on the main form, but not on the
subform.
Meaning that if I make a change in the subform and then press the
navigating
buttons, it does not prompt whether I want to save the changes or not. It
automatically saves them and moves on.

That happens because whenever you change the focus from the subform to the
main form, the subform record is automatically saved if it has been
modified. The same thing happens (in reverse) if you change from the main
form to a subform: the main form's record is automatically saved if it is
"dirty". There is nothing you can do to stop this behavior of Access, short
of using unbound forms or subforms and handling the loading and unloading of
data yourself -- thereby abandoning the power of MS Access.

Your original question was about a navigation button, but I think you really
have a larger issue in mind -- you want to control whether or not a record
gets saved, no matter how that might happen. For example, a dirty record on
the main form would normally be saved automatically whenever the user moves
to a different record, or when the focus is shifted to the subform, or when
the form is closed. If you want to trap the save and require the user to
confirm or discard the changes under all those circumstances, then you
really need to be working in the form's BeforeUpdate event. For example,
you might have a BeforeUpdate event procedure like this:

'------ start of code ------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMessage As String

strMessage = "Do you want to save " & _
IIf(Me.NewRecord, "", "your changes to ") & "this record?"

If MsgBox(strMessage, vbQuestion+vbYesNo, "Save or Discard?") _
= vbNo _
Then
' Discard changes to this record.
Me.Undo
End If

End Sub
'------ end of code ------

If you want to force the user to confirm changes for both the main form and
a subform, you would have to have a similar event procedure for the
BeforeUpdate event of both forms. Note, though, that you will not easily be
able to arrange things so that the user enters a main record and then one or
more subform records, and only then gets a prompt to confirm parent and
child records alike. That's because the user can't get to the subform
without automatically saving any changes that have been made to the main
form's record. It is possible, through some complex mechanisms, to make
this appear to happen, but I recommend you not try to go there.

 

[injanib via AccessMonster.com]

Thank you both for your feedback. With the information you two provided, I
can come up with something.

As I recalled, it not possible to move to the next record if the Response
is

[quoted text clipped - 15 lines]

End If

You make a good point. I wasn't trying to resolve all the possible problems
with the code, just get it to work as outlined. But it wouldn't have hurt
to cover all the bases. However, I think you've missed one possibility
also. If we want to avoid any error when the button is clicked, we need to
allow for the possibility that the use is on a new, unmodified record.
Here's a possible revision:

'------ start of revised code ------
Private Sub cmdNext_Click()

Dim intResponse As Integer

If Me.Dirty Then

If Me.NewRecord Then

intResponse = MsgBox( _
"Do you want to save this record?", _
vbQuestion+vbYesNo, _
"Save Record?")

Else

intResponse = MsgBox( _
"Do you want to save the changes?", _
vbQuestion+vbYesNo, _
"Save Changes?")

End If

If intResponse = vbNo Then
' Discard changes to this record.
Me.Undo
Else
' Save the changes to this record.
Me.Dirty = False
End If

End If

' Whether we saved this record or not, go to the next one
' (unless we're on a new record already).

If Me.NewRecord Then
DoCmd.Beep
Else
DoCmd.GoToRecord , , acNext
End If

End Sub
'------ end of revised code ------

However, I see from injanib's reply in this thread that just coding the
cmdNext button may not be enough, anyway.