Save record & stay on same record

[Jani]

I'm trying to save a record and than stay on that same record. Below is my
code which saves the record but moves to a new record instead of staying on
the record which was changed or added. The vbNo works OK. Help would be much
appreciated. Thanks, Jani

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
iAns = MsgBox("Do you want to add this record or accept the changes? After
adding or making a change to this record, check the %'s from this month to
previous month. For %'s which are +/- 20% a comment should be entered.",
vbYesNo)
If iAns = vbNo Then
Me.Undo
Cancel = True
If iAns = vbYes Then
DoCmd.GoToRecord , , acLast

End If
End If

End Sub

 

[Dale Fye]

Jani,

Generally, the forms BeforeUpdate event will only fire when the form is
dirty (there have been changes) and when something you have done has caused
it to move to a new record. What are you doing that is causing the
BeforeUpdate event to fire (I frequently have a Save button on my form uses
the following command to force the record to update.

If me.Dirty then Me.Dirty = False

I would change your BeforeUpdate event to look something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim iAns As Integer
iAns = MsgBox("Do you want to add this record or accept the changes?
After
adding or making a change to this record, check the %'s from this month
to
previous month. For %'s which are +/- 20% a comment should be entered.",
vbYesNo)

If iAns = vbNo Then
Cancel = True
'I would leave out the undo unless you want to undo all of your
changes
'Setting Cancel to True will cause the BeforeUpdate event to fail,
allowing
'you to return to your form, fill in the comment, and then save it
again.
'Me.Undo
Else 'you don't really need the Else or Cancel = False lines
Cancel = False
End if

End Sub

HTH
Dale

 

[Jani]

Hi Dale - 1st of all thanks for the quick response. This works great but
after it saves I want it to stay on the same record as the one that is saved
as I need to have them check percentages. How would I do that. I've tried I
don't know how many different codes this morning without any luck.

Jani

 

[Dale Fye]

Jani,

My first question is, what are you doing that is causing the BeforeUpdate
event to fire? This is usually caused by clicking on one of the navigation
buttons, is that what you are doing? If so, then I recommend you add a Save
button to the form and use that instead of the navigation buttons.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jani]

Just tabbing off the last field. I did have a save button which worked but
again couldn't get the saved record to stay displayed. Am I making myself
clear on what I'm attempting to do?

 

[Klatuu]

First, you are explicitly causing the form to change records:

If iAns = vbYes Then
DoCmd.GoToRecord , , acLast

If tabbing out of the last control on the form causes the record to be
saved, then you have the form's Cycle Property set to All Records. That
causes the form to advance to the next record when you update the last bound
control in the form's tab order. To stay on the current record, Change the
Cycle Property to Current Record. Then it will just tab from the last
control to the first. And, remove the code noted above.

 

[Jani]

I'm feeling like a real dunce... now I'm just cycling through the current
record.

 

[Klatuu]

Like trying to walk to town with one foot nailed to the floor?

Well, you have to do something to cause the record to attempt to update. It
could be clicking a navigation button to move to a different record, a save
button you your form, or closing the form. Or, if you want your form to move
to the next record when you complete the current record if all is well, or
stop and stay on the current record if there is a problem, then the form's
Before Update event is the place to do it. If you cancel the event, the
update will not occur and you will not move to another record.

Now, the question is, what do you want to happen and when?
If you can give me a description of the possible actions, I can help figure
this out. The problem is, when to allow the user to move to another record.
It is after they have saved the record and not moved?
Is there any way you can programmatically compare the percentages so the
user doesn't have to do it manually (they are human and will skip the check
if they are in a hurry, or may miscalculate)?

 

[Jani]

I like your analogy! They add data to a new (or change an existing) record,
need to save the record, stay on that record, then check the %'s against the
previous month for which I have a query already done - and that, fortunately
works perfect. What I have just done is used a save button which displays a
message to check the percents, saves the record and than lands on the %
button but they have to click it. I wanted to be able to have them cancel the
new record/changes but have given up on that for now.

 

[Klatuu]

It would be more user friendly, and make your task much easier if you could
programmatically check the percentages and notify the user if the percentages
are out of tolerance and require a comment. Since you already have a query
that returns that value, it shouldn't be that hard. Then you could use the
form's Before Update event to check the percentages, and if out of tolerance,
show a message box and cancel the update until the comment is entered. To be
really nice, you could then set the focus to the control where the comment
should be entered.
Part of the task would also be testing the control for the existance of a
comment.

The current problem you have is to know when to allow the user to move to
another record. As is, every time they try to move, they get the warning and
stay on the record. That becomes irratating to the user. There needs to be
a way to know the condition and let them move on.