Before Update problem

  • Thread starter Thread starter Troy Street
  • Start date Start date
T

Troy Street

Hello,

I've created a form that displays data from one table based on the value of
another table. I've placed a check box on the form, so when the user checks
it, it updates a textbox (A) with the current date, then performs a dateadd
function based on
TextBox A and an interger value in TextBox B, putting the result in Textbox
C.

My problem occurs when trying to change the value of textbox A. I get an
error to the effect of "A macro or validation rule is preventing the field
from being updated." I have no validation rule set in the underlying table
or properties. I am also getting messages that Access cannot move the focus
to the second or third control.

I am really at a loss, because in several other areas of the same database,
I've successfully used similar code to accomplish similar results. I've
compared property settings of forms and controls and cannot find what I may
be overlooking. The database has been updated through the years from Access
95, 98, and 2000. Another change has been the operating system being used to
develop the forms (now on Windows 2000 OS) Might these have anything to do
with the problem? Any Ideas are welcome! Thanks in advance.

Here's the code:

Private Sub chkComplete_Click()

Dim x As String, y As String, z As Integer

If chkComplete.Value = 0 Then
Exit Sub
ElseIf chkComplete.Value = -1 Then
x = Format(Date, "mmm-yy")
txtLastPerformed.SetFocus
txtLastPerformed.Text = x '---Error occurs here

txtMaintenanceInterval.SetFocus '---Access can't setfocus here
z = Val(txtMaintenanceInterval.Text)

txtNextDue.SetFocus
y = Format(DateAdd("m", z, x), "mmm-yy")
txtNextDue.Text = y
Exit Sub
End If

End Sub
 
One, in VBA, one usually uses the Value property, not the Text property,
when wanting to change the value of a control via code from another
control's event. Value does not require the control to have the focus.
Therefore, delete all the SetFocus steps in your code and change Text to
Value in the corresponding steps.

Because you're setting focus on the textboxes and then changing their Text
Values, and then trying to set focus to another control, the textbox's
BeforeUpdate event's code is running, and the textbox is trying to change
the value while also trying to validate it -- a circular process -- hence
the error message.

Changing the code as I've suggested should fix this problem too.
 
That seems to have done the trick.... thanks!


Ken Snell said:
One, in VBA, one usually uses the Value property, not the Text property,
when wanting to change the value of a control via code from another
control's event. Value does not require the control to have the focus.
Therefore, delete all the SetFocus steps in your code and change Text to
Value in the corresponding steps.

Because you're setting focus on the textboxes and then changing their Text
Values, and then trying to set focus to another control, the textbox's
BeforeUpdate event's code is running, and the textbox is trying to change
the value while also trying to validate it -- a circular process -- hence
the error message.

Changing the code as I've suggested should fix this problem too.
--

Ken Snell
<MS ACCESS MVP>


used
Click to expand...
 
Back
Top