Data change confirmation using msgbox

[Eric6]

hi,
i'm trying to make a
confirmation message that only shows up
when the user changes a value inside a form...

I want to warn the user that changes to the table
can be dangerous but i do not want to completely stop
him from making them (because changes might actually
be necessairy)

up to now, i'm coding some VBA for the "BeforeUpdate" event
i want to check the value from the table with the value in
the control field. If they are different; give the user an
warning message.
my problem is, i dont know how to get the values from the
table and form in VBA.
Here is the code i have so far

Private Sub Description_BeforeUpdate(Cancel As Integer)
    Dim Response, TableValue, FormValue, rec
    rec = CurrentRecord

    TableValue = 'value from table
    FormValue = 'value from control
    'My control name is "RO Number"
    'My column name is "RO"
    
    If TableValue <> FormValue Then
        Response = MsgBox(&quot;You have made changes to an existing record, do you wish to save them?&quot;, vbYesNo, &quot;Confirm changes&quot;)
        If Response = vbNo Then
            DoCmd.CancelEvent
            DoCmd.DoMenuItem acFormBar, acEdit, acUndo
        End If
    End If
End Sub

thank you

 

[Junior1544]

i'm not sure what vertion of access you are using... but if you're using access 2k or 2k2, then you can use the form's on dirty event...

then just have some thing like this...

if MsgBox("You have made changes to an existing record, do you wish to save them?", vbYesNo, "Confirm changes&quot = vbNo then
DoCmd.CancelEvent
end if

i think this may work for you if you are using access 2000 or 2002...

if you are using a previous vertion i don't know how you would go about doing what it is you are tring to do... but i'm sure there is a way...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[jebry]

Hi!

Another thing you can do in the Before Update event is compare a control's value to its oldvalue:

If Me!TextBox.Value <> Me!TextBox.OldValue Then
If MsgBox(&quot;You changed the FieldA in TableB to &quot; & _
Me!TextBox.Value & &quot;. Are you sure you &quot; & _
&quot;want to do this?&quot;, vbYesNo) = vbNo Then
Cancel = -1
Call Me.TextBox.SetFocus
End If
End If

You can check as many controls as you want.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Eric6]

thank you very much guys and/or girls
its working now =0)

 

[Junior1544]

what kind of solution did you use?? to enlighten the rest of us

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Eric6]

i used the old and new values...

here is my procedure
Private Sub Description_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Description_BeforeUpdate
If Me!Description.Value <> Me!Description.OldValue Then
If MsgBox(&quot;You have made changes to an existing description. &quot; & _
&quot;Do you wish to save these changes?&quot;, vbYesNo, &quot;Confirm Changes&quot = vbNo Then
DoCmd.CancelEvent
DoCmd.DoMenuItem acFromBar, acEdit, acUndo
End If
End If

Exit_Description_BeforeUpdate:
Exit Sub

Err_Description_BeforeUpdate:
MsgBox Error$
Resume Exit_Description_BeforeUpdate
End Sub

 

[Eric6]

i still have a problem though
if the text is auto corrected
the undo command will undo the
autocorrection and not the whole
change to the field

i haven't figured how to get arround this yet
(asside from disabling autocorrect, but i wish to keep it)

i tried to use the old and new values to assign
the old value to the field but access wont let me
it says i cant change the value of a field
in the beforeupdate method...

does anyone have an idea as to how or when
i could do this confirmation message and be
able to undo the changes if the user selects no?

thank you

 

[jebry]

Hi!

Try adding this:

Call Me!YourTextBox.SetFocus
Me!YourTextBox.Text = &quot;&quot;

hth
Jeff Bridgham
bridgham@purdue.edu

 

[gwinn7]

It seems like you are making this too hard for yourself.

Why not lock all of the controls on the form and place a 'Edit' button on the form. When the user clicks the 'Edit' button, then prompt the user with your warning, then unlock the controls for the user to change.

If a user has changed data on the form, the form's BeforeUpdate event will trigger. Then, if you want to use that, you can prompt the user to save or cancel changes. Its really simple. I wouldn't bother with all that code. Its a waste of time!

Gary
gwinn7
A+, Network+

 

[Eric6]

you know,
you're right!

i'll just do an edit button
thanks for the idea