Problem with Default Values

  • Thread starter Thread starter Peter R. Fletcher
  • Start date Start date
P

Peter R. Fletcher

In a database used for tracking adult education classes, I have a form
for entering Class information, normally operating in "Continuous
Forms" mode, with a listbox which allows the Course to be specified
(Course information being kept in a separate Table with a 1:Many
relationship with the Class Table). I am trying to ensure that the
Course is never Null (to avoid problems if the user puts in some
information, does not specify the Course, ands tries to close the
Form), and I have run into the following odd behaviour: If a default
value is specified for the ListBox control and the value of that
control is the first thing to be changed on a New record (by
displaying and clicking on the displayed information corresponding to
the new value), a new instance of the continuous form (a fresh "New
Record") is displayed below the one being used and the value of the
ListBox control for the record being added is reset to the control's
specified default value. Once the record has been Dirtied (either by
changing another control first, or simply by making the "ignored"
entry in the ListBox), everything works as it should. This is not just
a function of the Continuous Forms mode, because it also happens in
"Single Form" mode, though you don't see the new "New Record" appear,
of course.
This is a very complex form with a lot of code behind it, but as far
as I can see, no code is being executed (or at least, none of mine!)
between the new value of the ListBox being selected and the default
value being "magically" restored.
Is this what should happen in this situation?
Is there any fix for it? The Course is the first thing that the user
should logically set for a new Class, so it would not be particularly
helpful to tell them to do something else first.
I am temporarily setting the value of the control for a New Record in
the Current event, but this introduces other problems.
 
I don't have Access here to check, but from memory, when you initially dirty
a clean new record X, another clean new record Y is created instantly, but
the focus stays on X. I imagine the reason for this, is so you can click
directly from X into Y, thereby saving X, and preparing to enter data in Y,
in a single action. If it did not work like that, you would have to save X
as a seperate action, then Access would create Y, then you could click into
Y. I guess Access is just trying to optimize & simplify the process of
getting from X, into Y.

As for ensuring that the Course (for a new Class) is never null, the proper
way to do that is to set the Required property of the Course field in the
Class table. Then a value is required for that field, regardless of whether
you do or do not have a default value on the listbox.

HTH,
TC
 
I expect the new "New Record" to appear, for the reasons you give,
what I didn't expect was that the value I had just set in the ListBox
in the old, now Dirty, "New Record" would be overwritten by the
default value set for that Control - effectively "cleaning it up"
again!

On your second point, since this particular field points relationally
to a parent record, it can't be Null when the record is saved and
doesn't have to be separately set as Required. I make a distinction
between trying to ensure that a field is set to a sensible value
before a record is saved and trapping the error which results when it
hasn't been - at that point, it is sometimes too late to unwind events
and fix things, hence my approach.
 
I expect the new "New Record" to appear, for the reasons you give,
what I didn't expect was that the value I had just set in the ListBox
in the old, now Dirty, "New Record" would be overwritten by the
default value set for that Control - effectively "cleaning it up"
again!
Click to expand...

Yes, that does seem strange. Sorry I don't have Access on this PC, or I
would try to replicate.

On your second point, since this particular field points relationally
to a parent record, it can't be Null when the record is saved and
doesn't have to be separately set as Required.
Click to expand...

Not sure I follow you there. If each Class is for exactly one Course, the
Class table would have a CourseID field, and that field should be Required
in the Class table, no?
I make a distinction
between trying to ensure that a field is set to a sensible value
before a record is saved and trapping the error which results when it
hasn't been - at that point, it is sometimes too late to unwind events
and fix things, hence my approach.
Click to expand...

Yes, that sounds sensible. But if there is no "obvious" default for the
Course of a new Class, it might be better to show "no selection" initially,
so the user must explicitly select a Course, instead of blindly going with
the default one?

Cheers,
TC
 
Back
Top