Save Record prompt when cycling through records on form

Got a form with navigation buttons.  This form is not used to add records, only to cycle through and find records and update when needed.  What I want to find out is how to prompt the user to save the current record they're working on before the record changes when they click on the navigation buttons.

I've tried On Current and it doesn't work right.  I could re-create the navigation buttons look and run my own event procedures with that to do it, but it'd take too much time.  I was hoping there was a form event or somebody's written an external function to do this.

Thanks!
LVL 1
SeanGraflundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wesleystewartCommented:
It would seem that the Before Update event for the form would be the place to put your code.  You can test the Dirty property of the form.  The following came from Access97 help:

Sub UndoEdits()
      If Me.Dirty Then
            Me!btnUndo.Enabled = True      ' Enable button.
      Else
            Me!btnUndo.Enabled = False      ' Disable button.
      End If
End Sub

Sub btnUndo_Click()
      Dim ctlC As Control
            ' For each control.
            For Each ctlC in Me.Controls
                  If ctlC.ControlType = acTextBox Then
                        ' Restore Old Value.
                        ctlC.Value = ctlC.OldValue
                  End If
            Next ctlC
End Sub

You'll want to add your own confirmation code (some sort of msgbox ?) in there.  Give me a little more time and I'll see if I can't cook up something better.

Wes
0
wesleystewartCommented:
Yuk.  Forget that previous code.  Sorry it was so crappy.  I'm quite the hack today.  Give this a shot:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty Then
        If MsgBox("Do you want to save changes", vbYesNo, "Save?") = vbNo Then
        UndoIt
        Exit Sub
        End If
    End If
End Sub

Sub UndoIt()
    Dim ctlC As Control
        ' For each control.
        For Each ctlC In Me.Controls
            If ctlC.ControlType = acTextBox Then
                ' Restore Old Value.
                ctlC.value = ctlC.OldValue
            End If
        Next ctlC
End Sub

This will ask your user if they want to save changes.  If they click no, it resets all the text boxes to their previous value.  If they click yes, it saves the change and carries out their navigation or form close event.

Wes
0
DedushkaCommented:
You should use before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If MsgBox("Save record?", vbYesNo) = vbNo Then Cancel = True
End Sub

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jschrismanCommented:
I'm going with Dedushka on this one. There is no need to check if a form is dirty in the BeforeUpdate event because the BeforeUpdate event only gets fired if the form is dirty.

I might add one thing to Dedushka's response.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save record?", vbYesNo) = vbNo Then
  Cancel = True
  me.undo  'Undo all changes user made
endif
End Sub





0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DedushkaCommented:
jschrisman, thank you for support :-)
0
wesleystewartCommented:
Indeed.  Nice and simple.

Wes
0
JimMorganCommented:
Sean:  Why do you need to remind them to save the record as the very act of moving to a new record saves any changes in the current record?

If they make a mistake, they can come back and correct it.

It seems as if you are really asking them if they are through making changes with the current record?  If not, put them back in the record by 'Cancel' and let them continue making changes.

If they don't like the changes they made, then what are you going to do?

My preferences are to not bug the user everytime they make a change with statements like "Are you sure?", etc.  Unless the users are total dolts, after a while, asking them to do this can get very tiring and irritating.

Just my 2c worth.

Jim
0
SeanGraflundAuthor Commented:
I think jcrishman has the right idea .. nice and simple..

Jim,

moslty it's going to be a check for data entry errors .. i was thinking about throwing it in there for each record, but i'm going to use it instead to make sure the data entry is correct and also I want to log all changes made in a database .. old value and new value, field name, form, etc .. just to have it.

I didn't know which event was used so I could check that ..

You're right though, after playing around initially moving through the form, it is annoying :)

And I never knew that me.Undo was a property .. hrm, I always used code similar to wesleystewart's example above.

gess I should look through an object's property list more often.


Thanks!
0
JimMorganCommented:
There are two times to use Undo.  One when you want to change the entire record back - me.undo.  The other for changing a single field.  This must be used to get out of a NotInList trap with combo boxes.  The only way to clear a bad entry is to use acErrorContinue and then undo the field value.

Hopefully, we all learn something new each day.  That's one of the reasons I'm hear most days.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.