Optional field

[Shaun]

Hi,

I'm building a db that stores information about field conditions at
different locations. These attributes include landform, environmental
setting, current land use, erosion, and vegetation. Of course the end user
wants a "other" choice for all of these attributes to avoid having a list of
choices a mile long. This by itself is not a problem, however, when other is
selected, they want a text field to fill in what that "other" is.

The attributes of the location are in 2 tables: one for setting, landform,
and vegetation and the other table for the remaining two. The reason they
had to be split was the user also records percentage of each
setting/landform/vegetation that covers the area. Land use and erosion are
simply presence/absence of the various types. The PK for each of these
tables is a multifield of LocationID,Type, and Description (since each
location can only have one record of each Description).

I figured that having a blank field at the end of each of these tables for
this information that is only used occasionally is not the best way to go but
can't figure out a better way (been staring at this thing for too long). Any
suggestions would be greatly appreciated. Thanks!

 

[Allen Browne]

An additional free-form text field where the user can enter an explanation
for the "other" choice makes good sense.

Perhaps you could just call it a comment field and be done with it, i.e.
they can add a comment regardless of their choice.

Alternatively you could set up your lookup table (the choices in the combo)
with a yes/no field to indicate whether a comment is expected (as it is in
the case of Other), and give the user a warning if they choose this one but
did not fill in a comment.

 

[Shaun]

So you're saying it would be ok to just add a comment field into the 2 main
tables? I was worried that being it will only be occasionally used, it
wouldn't be the best design and that there might be a better way to do it.

I was going to control the text box control on the form based on the after
update event of the combo box with the choices in it, such that the text box
would only be visible when "Other" was chosen. Thanks for the idea of the
Yes/No in the lookup table, that might come in handy in a few other places.

 

[Allen Browne]

Yes, that's correct, Shaun. Just go ahead and use the additional field.

As a general rule, you don't want heaps of rarely-used fields, so I
understand what you are saying. But for this case (and lots of others), it
makes sense to keep the design really simple, even though the field is not
used most of the time. The fact that the field can be used as a free-form
comment with any value indicates that it is not really unnormalized, i.e.
there is not conflict/error if there is a comment beside a choose other than
"Other."

You will probably want to use the combo's Exit or LostFocus event to
determine where to go next. AfterUpdate is too early (SetFocus appears not
to work as Access afterwards moves to the next control in the tab order),
and it would not have the desired effect when tabbing through an existing
record.

 

[Shaun]

Thanks Allen!

Yes, that's correct, Shaun. Just go ahead and use the additional field.

As a general rule, you don't want heaps of rarely-used fields, so I
understand what you are saying. But for this case (and lots of others), it
makes sense to keep the design really simple, even though the field is not
used most of the time. The fact that the field can be used as a free-form
comment with any value indicates that it is not really unnormalized, i.e.
there is not conflict/error if there is a comment beside a choose other than
"Other."

You will probably want to use the combo's Exit or LostFocus event to
determine where to go next. AfterUpdate is too early (SetFocus appears not
to work as Access afterwards moves to the next control in the tab order),
and it would not have the desired effect when tabbing through an existing
record.