Re-initializing controls on a form

[Peter Hallett]

When entering data to a form, it is occasionally necessary to
programmatically reset a group of bound controls to their default values.
Sometimes the VBA runs as intended, and assigns the appropriate null or zero
values, but on others, and for reasons I do not understand, it falls foul of
the run-time error, “You can’t assign a value to this object.â€

I have also tried:–
Me.Control = Me.Control.DefaultValue
but that works no better.

Neither does a requery offer a solution. This would unnecessarily, and
inappropriately, rerun much of the form’s VBA.

I have encountered this problem in other situations, where data is accepted
when entered manually but rejected when entered programmatically. If I
understood the nature of the error I might be able to get around it but, at
the moment, I am stumped.

 

[Dirk Goldgar]

When entering data to a form, it is occasionally necessary to
programmatically reset a group of bound controls to their default values.
Sometimes the VBA runs as intended, and assigns the appropriate null or
zero
values, but on others, and for reasons I do not understand, it falls foul
of
the run-time error, “You can’t assign a value to this object.â€

I have also tried:–
Me.Control = Me.Control.DefaultValue
but that works no better.

Neither does a requery offer a solution. This would unnecessarily, and
inappropriately, rerun much of the form’s VBA.

I have encountered this problem in other situations, where data is
accepted
when entered manually but rejected when entered programmatically. If I
understood the nature of the error I might be able to get around it but,
at
the moment, I am stumped.

I'm not sure exactly what you've tried, but I don't think the code snippet
above would necessarily work in all cases. You might try this:

With Me.ControlName
If Len(.DefaultValue) = 0 Then
.Value = Null
Else
.Value = Eval(.DefaultValue)
End If
End With

I can think of cases where that would fail, in particular if the control is
bound to an autonumber field or to a field that is defined as Required in
the table design.

In this situation, is it enough to just Undo the control:

Me.ControlName.Undo

?

 

[Peter Hallett]

Dirk,

Many thanks for your assistance. Unfortunately, however, your solutions
worked no better than mine. They did, though, force me into a detailed
re-examination of my code – where the solution was eventually found.

The reset routine was being called from Sub Form_Open, instead of Sub
Form_Load. As a consequence there were no valid controls to reset when the
reset routine was invoked.

A puzzle nevertheless remains. Despite the mistake, the code worked fine
for much of the time. On other occasions it stopped with a run-time error.
Perhaps some sort of cached values were being used under certain
circumstances. Whatever the reason, it made debugging very difficult.
Errors of the ‘now you see it; now you don’t’ variety are notoriously hard to
pin down.

 

[Dirk Goldgar]

Dirk,

Many thanks for your assistance. Unfortunately, however, your solutions
worked no better than mine. They did, though, force me into a detailed
re-examination of my code – where the solution was eventually found.

The reset routine was being called from Sub Form_Open, instead of Sub
Form_Load. As a consequence there were no valid controls to reset when
the
reset routine was invoked.

Ah. I hadn't realized you were running the code in the form's Open event.
That's usually a problematic event for any attempt to manipulate the value
of a bound control, since it fires before the form's recordset is loaded.

A puzzle nevertheless remains. Despite the mistake, the code worked fine
for much of the time. On other occasions it stopped with a run-time
error.
Perhaps some sort of cached values were being used under certain
circumstances. Whatever the reason, it made debugging very difficult.

I'd guess it's a timing problem -- sometimes the form's recordset has been
loaded by the time you try to manipulate bound-control values, sometimes it
hasn't. Also, there are some things you might do in the form's Open event
that would force its recordset to be loaded. I can't say, not knowing what
the rest of your code is like. Also, if you are testing by flipping the
form between form view and design view, I suppose that might make a
difference. That's just speculation, though.

Errors of the ‘now you see it; now you don’t’ variety are notoriously hard
to
pin down.

Absoultely, as they often depend on factors that you aren't aware of, and
sometimes have no control over.