Before Update with Combo Boxes

[businessman]

Before Update with Combo Boxes - Pro help needed

i have a form that i want to stamp with a date when it was last updated. if a change to the form is made it will be stamped with today's date. i used the BeforeUpdate event so that you could choose to save the edits or not so if you didn't mean to edit a record then the date would not change. the code is:

Dim myVar As Byte
myVar = MsgBox("Changes to this issue have been made.  Would you like to save the changes?", vbYesNo + vbQuestion, "Save Changes?")
If myVar = vbYes Then
    [LastUpdate].Value = Date
Else
    Me.Undo
End If

my problem is that when i change the combo box values it will run this event and ask to save. i think the reason this is happening is because the comboboxes save when you change them. how do i accomplish the same task as above but allow the combo boxes to act like txt boxes?

also is there a way that when i ask if the user wants to save or not, to have a cancel button that will take no action, leaving the record open the way it was (new typing and everything) before the user tried to close the form without saving that info?

 

[Lister]

Perhaps, and without seeing your form I am assuming a lot, you sould run your code (or some code) on the Close_Form_Button. (Assuming that you have one).

Check to see if changes have been made useing the the On Dirty Event and take it from there.

Sub cmdClose_Click()
Dim myVar As Variant
If Me.Dirty Then
myVar = MsgBox("Changes to this issue have been made.  Would you like to save the changes?", vbYesNo + vbQuestion, "Save Changes?")
    If myVar = vbYes Then
        [LastUpdate].Value = Date
        DoCmd.RunCommand acCmdSaveRecord(etc)
    Else
        Me.Undo
    
    End If
End If
DoCmd.Close
Exit Sub
End Sub

Hope this helps

 

[businessman]

what does the on dirty event mean?

 

[Lister]

Try it, on the on close or better your form close button (cmdClose)
And this:

If Me.Dirty
msgbox "I am dirty, some changes have been made"
Else
msgbox "I am clean, no changes have been made"
End if

If the user makes a change in a "EXISTING" record then the record is Dirty, so the On Dirty Event catches this. And yo can check to see if your user wished to save the changes or undo any changes.

If its a new record the event is Me.NewRecord so it will always be dirty from the moment they start to add data.

Dose that help?

 

[Banana]

A note:

If you want to back out, it's better to do so at Unload event, which can be cancelled instead of Close event which can't be cancelled.

 

[businessman]

Is the Unload event kinda like the BeforeUpdate in the fact that when you close a form it will go to this before going to the close function? How does the Unload event work? if in the unload and i did a vbYesNoCancel, yes: add date stamp, no: Me.Undo, and Cancel: cancel = true, wouldn't the combo boxes still update? how does the undo work with this unload?

 

[Banana]

Unload event comes before Close event and simply means that it's about to be moved from the memory, but not from the screen. As you can see, you can't/shouldn't cancel a event if it's already removed from the memory.

On No, simply enter

Cancel = True

You will note that in the event line, there's a Cancel as Integer-

Unload_Form FormName (Cancel As Integer)

HTH.

 

[businessman]

so then if i have a yes, no, cancel msgbox and on yes i did my update, on cancel i did the cancel =true, how would i allow the user to say not to save any changes at all by clicking no? do you understand what i'm trying to do or am i being too ambiguous again? Below is the code that i'm trying to do.

Private Sub Form_Unload(Cancel As Integer)
    
If Forms![IssueDetails]![Dirty].Value = "True" Then
    myVar = MsgBox("Changes to this issue have been made.  Would you like to save the changes?", vbYesNoCancel + vbQuestion, "Save Changes?")
    If myVar = vbYes Then
        Forms![IssueDetails]![LastUpdate] = Date
    ElseIf myVar = vbNo Then
        'Close Form without saving any changes to the entire form that contains txt and combo boxes
    ElseIf myVar = vbCancel Then
       Cancel = True
    End If
End If
End Sub

 

[Banana]

You have it all right. If user chooses No, you may only want to add this:

Docmd.RunCommand acUndo

to ensure that the data is unchanged. The code will continue and eventually close the form.

HTH.

 

[businessman]

will that be the only line of code under the no? when i add that in under the no, it gives me a runtime error saying " The RunCommand action was cancelled"

 

[Banana]

Yes that's right. It should be the only line there.

Is the form a pop up?

Were you stepping through the code?

 

[businessman]

The form is opened when i click a button to edit details about a specific record. When i'm done editing the fields for a record, i close the form like normal and it goes back to my first form. it does it when i step through and run like normal.

 

[Banana]

Hmm, didn't really answer my question.

Is the form's property set to popup? If so, then runcommand won't work. It's a documented bug. A workaround is to set it to no, and set modal to yes.

Also, if you are stepping through the code and you hit any runcommand, you get an error saying it's not available. When you do the same thing without stepping through the code at all, it works just fine. Why it does that, I have absolutely no idea.

 

[businessman]

both Popup and Modal are set to no. i tried to set modal to yes and i still get a "RunCommand Action Cancelled" error.

 

[Banana]

No. You don't have to set it to modal.

So even with the breakpoints removed, you're still getting an error?

 

[businessman]

that is correct

 

[Rich]

If Forms![IssueDetails]![Dirty].Value = "True"

Is incorrect, it's just
If Me.Dirty = True Then, there are no quotes around True it's a boolean value and the statement can be shortened further to
If Me.Dirty Then

RunCommand etc can also be replaced with just
Me.Undo

 

[businessman]

when i was doing Me.Dirty = True it would never enter the condition so i created a non visible field that would say "True" when OnDirty event was met. when i did the Me.Undo it would not undo.

 

[Rich]

when i was doing Me.Dirty = True it would never enter the condition so i created a non visible field that would say "True" when OnDirty event was met. when i did the Me.Undo it would not undo.

You might find the code runs better from a command button on your form rather than one the form events

 

[businessman]

So the only way to possibly fix this is to hope that a button would like the code more than the form events? i would prefer not to use a button to do this but if i have to i might consider it.