Saving Data After Updating 2

[RonCT]

I have a form where users will be updating records on a monthly basis. I need to have the form prompt the user to save changes if they did anything to the record. The form is set up with next and back buttons that allow the user to just leave the record if they made a change. I tried using the AfterUpdate property on the main form and it kind of worked. The macro had the warning box pop up from a macro. I had it say save changes. The problem with the box is that it only says OK. I need to have the box say yes or no. If the user selects no, then they return to the record. If they say yes, they move on to the next record. Please help! Is there a way to do it without VB programming. If not, can you tell me how to install the code?

 

[nice95gle]

I gave this example yesterday in another thread but I think you can use it for what you are asking.

dim msgresp

msgresp = MsgBox("Would you like to save", vbYesNo)
If msgresp = vbYes Then
DoCmd.Save
exit sub
End If

 

[shreked]

Ron

If you can't understand the code goto the help file in access and look for "MSGBOX" then click on example click on the example "MsgBox Function Example" this should explain how the code works


Shrek

 

[RoyVidar]

Hi!

Think perhaps the forms before update event would be best place to do such actions, data validations...

This event fires whenever a user tries to save a record, either by closing the form, moving to another record, hitting SHIFT + ENTER... and it is possible to cancel it, so that for instance if the user selects no, it will not leave the current record (the user can edit, hit ESC...).

I'm afraid it's VBA, but I'll try to guide you thru it, if you haven't alredy solved your challenge:

Enter the forms design view, and find properties for the form. In the events tab, click in the line wher you find the "Before Update" event. Click the button with three dots at the right, and in the following dialog box, select "Code Builder".

This should put you in the code window, where your cursor will be between the first and last of the code lines below . Paste the blue lines into where your cursor is, so it looks like this (well, not the colors;-))

[tt]Private Sub Form_BeforeUpdate(Cancel as Integer)

Dim iAnswer As Integer
If Me.Dirty Then
iAnswer=MsgBox("Save changes to the record?", vbYesNo)
If iAnswer=vbNo Then
Cancel=True
End If
End If

End Sub[/tt]

Short explanation of some two of the lines:
if me.dirty then - if the record has been changed or is new
cancel=true - cancelling whatever action that triggered this event (trying to move to next record, closing form...)

Report back if you don't get it to work!

nice95gle:
Your save command would save design changes to the form, saving records could be for instance:
[tt]docmd.runcommand accmdsaverecord[/tt]

HTH Roy-Vidar

 

[nice95gle]

docmd.runcommand accmdsaverecord

Thanks, I just learned something

 

[RonCT]

HTH Roy-Vidar - It works. The only problem I see is that when a change was made to a record and I say not to Save Changes, it stays at the same record but the another message box pops up saying You can't go to the specified record. You nay be at the end of a recordset. Is there a way to get rid of that message box. Thank you very much!

 

[RoyVidar]

Hi again!

Thank you for the star. By your question, I'm assuming you are using some kind of "recordmoving"... buttons you have created yourself and not the navigation buttons of the form. Does this happen only for new records?

If that is correct, I could assist in removing that message thru changes in the code, if they where created thru/ with the button wizard, but I'd just like to check 1 if the assumption is correct 2 if not, please describe what action/button/event did "trigger" the event (record movement, save button...)

Roy-Vidar

 

[RonCT]

I created the navigation buttons using the button wizard. I've tried all of the code that everyone was nice enough to give me and this seems to work the best so far because it let's you navigate through the records back and forth.

Private Sub Form_BeforeUpdate(Cancel as Integer)

Dim iAnswer As Integer
If Me.Dirty Then
iAnswer=MsgBox("Save changes to the record?", vbYesNo)
If iAnswer=vbNo Then
Cancel=True
End If
End If

End Sub

The other code I put in asked if I wanted to save changes when moving to the next record even if no change was made. The problem with the code above is that it pops up a message box asking a user to save changes if a change has been made. This is correct. The problem is if the user says no, it stays at the same record and there isn't a way to get to the next record unless you say yes.

 

[RoyVidar]

Hi again!

Yes the option [tt]Cancel = True[/tt] does what you asked for initially. Return to the record if they don't want to save. There are some possibilities. I'll name two

First one, is simply to hit ESC after answering no, and then move to next record.

Next one:

If you try changing the line (cancel=true) to

[tt]Me.Undo[/tt]

which is the code equiualence to hitting the ESC button, the changes would become undone, and you'd be able to move to next record.

If the xtra message box is still bothering you, a short answer would be to find the record navigation code (on click event of the buttons) and do the following addition:

change the lines:

[tt] msgbox err.description[/tt]

to

[tt] if err.number<>2105 then
msgbox err.description
end if[/tt]

(2105 is the error number for the error that fires when the custom designed record moving buttons are not able to perform this)

Report back if you don't make it work, or need other options.

HTH Roy-Vidar

 

[RonCT]

It works perfectly! You are awesome! The other box doesn't pop up & I didn't need to change the 2015. Thank you very much! I appreciate it!

 

[RoyVidar]

My pleasure!

Merry Xmas!

Roy-Vidar