Before Update event prevents data change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to allow the user to enter a value in a control, then use VBA event
code to adjust the value in the field (e.g. changing the case), but I don't
want to try to adjust the value if the user hasn't modified the control's
data, because this means the field will be considered dirty even if the value
is unchanged and it requires two clicks to move to another record.

I have tried using the BeforeUpdate and LostFocus events but neither does
what I need.

I am trying to use the following code (text field)
If Me.ActiveControl.Text = Me.ActiveControl.OldValue Then
Exit Sub
End If
Me.ActiveControl.Text = ... 'modified value (actually several lines here)

The problem is that if I include this in the Lost Focus event, the OldValue
has already been updated so it never runs the code to change the value even
if the user has dirtied the control.

Alternatively if I use it in the Before Update event, the OldValue does
correctly reflect the previous field value, but then it won't let me change
the value entered by the user - if the user has typed a different value I get
the following error:
"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing MyApp from saving the data in the field." with
error number 2115.

This seems like Catch22!

Is there another way to achieve what I want? Or am I using the wrong event?
 
rustle said:
I want to allow the user to enter a value in a control, then use VBA
event code to adjust the value in the field (e.g. changing the case),
but I don't want to try to adjust the value if the user hasn't
modified the control's data, because this means the field will be
considered dirty even if the value is unchanged and it requires two
clicks to move to another record.

I have tried using the BeforeUpdate and LostFocus events but neither
does what I need.

I am trying to use the following code (text field)
If Me.ActiveControl.Text = Me.ActiveControl.OldValue Then
Exit Sub
End If
Me.ActiveControl.Text = ... 'modified value (actually several
lines here)

The problem is that if I include this in the Lost Focus event, the
OldValue has already been updated so it never runs the code to change
the value even if the user has dirtied the control.

Alternatively if I use it in the Before Update event, the OldValue
does correctly reflect the previous field value, but then it won't
let me change the value entered by the user - if the user has typed a
different value I get the following error:
"The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing MyApp from saving the data in
the field." with error number 2115.

This seems like Catch22!

Is there another way to achieve what I want? Or am I using the wrong
event?
Click to expand...

Use the AfterUpdate event. It only fires when the user has modified the
data in the control (though, granted, it will fire if the user modifies
it to the same value that it was before). So you'd have an event
procedure like this:

Private Sub YourControlName_AfterUpdate()

Me!YourControlName = ...

End Sub

Note -- don't try to change the control's Text property for this. It's
the Value property you want, which is the default property and hence
need not be explicitly named.
 
Dirk Goldgar said:
Use the AfterUpdate event. It only fires when the user has modified the
data in the control (though, granted, it will fire if the user modifies
it to the same value that it was before). So you'd have an event
procedure like this:

Private Sub YourControlName_AfterUpdate()

Me!YourControlName = ...

End Sub

Note -- don't try to change the control's Text property for this. It's
the Value property you want, which is the default property and hence
need not be explicitly named.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Many thanks - this solved the problem.
 
Back
Top