Remember the value before it was replaced how?

[Guest]

In short: I have a check box (yes/no) in my form that the user clicks to make
a decision, when checked; a new message is stored in a field called Status in
my database. What, I am trying to do is to store “in memory†- not in any
filed - the current comment found in the Status field before gets update so
if the user change his mind and uncheck the yes/no box the system will place
back the previous comment (like UNDO). Once the form is closed, I don’t care
about permanently save the old comment anywhere. Below is what I have done
but the system still store in the database the new comment despite the user
uncheck the yes/no box (meaning the ELSE statement) Can anyone show me how to
write this code correctly? I am sure the answer it is here somewhere but when
I search I guess I am not using the correct wording. Thank you.


Dim oldStatus As String
oldStatus = Me!Status

If Me!ynNeshap = True Then
Me!Status = "Neshap approval

Else

Me!dNeshap = Null
Me!Status = oldStatus

End If

 

[Guest]

I have also tried to use the UNDO as below, however I am getting Error 438
"Object doesn't support ..." It appear to work fine if use Me.Undo but that
will undo change in all my fields not just in Status. :-(

If Me!ynNeshap = True Then
Me!Status = "Neshap approval

Else

Me!dNeshap = Null
Me!Status.Undo

End If

 

[Guest]

Hi Silvio,

If you use the controls BeforeUpdate event you have access to the .OldValue
reference, so you could have a private variable within the form to store the
oldvalue, and it will keep it until either the user re-changes the value or
the form closes;

strOldVal = Me.Status

Or if you can check the validity of the change in the beforeUpdate event you
could just cancel the update.

hope this helps,

TonyT..

 

[Guest]

Tony, I am not as good as you think can you please show me how/where
strOldVal = Me.Status fits in my code?

 

[Guest]

Hi again,

only trouble is you don't say where your code is. I will post generic
possibilties instead

In the forms general declarations put;
Private strOldVal as String
then
Private Sub Status_BeforeUpdate()
strOldVal = Me.Status.OldValue
End Sub
then
Private ynNeshap_AfterUpdate()
If Me!ynNeshap = True Then
Me!Status = "Neshap approval"
Else
Me!dNeshap = Null 'False is probably better than Null unless you
are using Triple State Check boxes.
Me!Status = strOldVal
End If
End Sub

if that doesn't fit what you are wanting with regards to event timing, let
me know when you want the *undo* part to fire.

TonyT..

 

[Guest]

Tony my code runs ON CLICK of the checkbox called Neshap. If it helps, this
is the entire as I have it now:

Private Sub ynNeshap_Click()

On Error GoTo HandleErr

If Me!ynNeshap = True And ynVector = True Then
Me!tNeshap = CDate(Now())
Me!dNeshap = Date
Me!Status = "Neshap and Vector pending approval since " & Date

ElseIf ynNeshap = True And ynVector = False Then
Me!tNeshap = CDate(Now())
Me!dNeshap = Date
Me!Status = "Neshap pending approval since " & Date

Else
Me!ynNeshap = True
Me!tNeshap = Null
Me!dNeshap = Null
Me.Status.Undo

End If

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmForwardSub.ynNeshap_Click"
'ErrorHandler:$$N=Form_frmForwardSub.ynNeshap_Click
End Select
End Sub

 

[Guest]

Hello once more Silvio,

It would be more usual to have that code in the After Update event of your
check box, but it doesn't make much difference to the outcome of what you
have.

if you use the 1st 2 bits if code I posted (general declarations and Status
BeforeUpdate), then all you need to do is replace;
Me.Status.Undo
with;
Me.Status = strOldVal

(Still recommend using False rather than Null for values for other
checkboxes unless they are Triple State checkboxes.)

Nearly there I think

TonyT..