Where to set field defaults?

[F.H. van Zelm]

Hi all pro's,

Just a simple (stupid) beginners question: where to set all kinds
of field defaults (apart for the data type, of course)? In the table
definition or in each derived form?
Think of:
- formats (e.g. align number left for some silly reason)
- mask (if ever applicable; perhaps on zip codes)
- validation rules (e.g. maximum allowed value is 100)
- foreign key values shown as text in a drop down, using the
Lookup Wizard

Personally, I prefer to set these defaults in the table because it is
difficult to remember and apply all default settings by field in each
form. But perhaps this is an amaturistic approach.

Any (phylosofical) consideration is welcome.

Frans van Zelm

 

[F.H. van Zelm]

Hi Joseph,

Thanks for reply and advice!

To be presize: where would you set the combo box to lookup foreing
key values? In the table, using the Loopup Wizard, or by each separate
form where the field is shown?

Frans

 

[John Vinson]

Hi Joseph,

Thanks for reply and advice!

To be presize: where would you set the combo box to lookup foreing
key values? In the table, using the Loopup Wizard, or by each separate
form where the field is shown?

Neither.

In the Relationships window.

John W. Vinson[MVP]

 

[F.H. van Zelm]

Hi John,

Thanks for reply and assistance.

Perhaps, I wasn't clear in asking. I know that relationship are set
in the Relationship window. That's the fundament.
But I want combo boxes in all forms where foreign keys are visible.
E.g. type and description of products in stead of autonumber key
values in a text box.

Where should I create the combo box view:
- once in the table (manually by SQL or by the Lookup Wizard)
- in each separate form (manually or by the Combo Box Wizard)

I myself prefer the table because then all dependent forms inherit
the table default. I dislike doing the same thing over and over again
by form.
But I hear people say that that is a bad approach because will
pollute the data in the table.

I'd like to read your advice, Frans van Zelm

 

[John Vinson]

I myself prefer the table because then all dependent forms inherit
the table default. I dislike doing the same thing over and over again
by form.
But I hear people say that that is a bad approach because will
pollute the data in the table.

IF you're fully aware of the many problems with tables containing
Lookup Fields, and IF you want to spare the fifteen or twenty seconds
needed to create a combo box on the form, I don't think it's
"polluting" to use combo boxes in tables. (I really never thought I'd
say that...).

The problems arise when you SEE the looked-up values in the table
datasheet view and mistakenly assume that you can *use* that data (for
sorting, queries, etc.); of course it's not there, what's in the table
is the ID.

I personally don't use them because I'm used to, and comfortable with,
creating combos from scratch (either using the wizard or just with the
control properties); but if you do a great deal of form development,
find it handy, and recognize the limitations, you should be safe. I'd
still recommend carefully checking to be sure you don't have redundant
relationships or indexes.

John W. Vinson[MVP]

 

[F.H. van Zelm]

Hi ,

Thank you for your full answer. I think, I understand what you mean:
- a filter on field Category - value Metals won't work because it really
contains the id for Metals.
- the Lookup Wizard creates a double relationschip (which I delete).

I'll take care and am happy with the conslusion that my approach
doesn't pollute table data.

Frans

 

[Larry Linson]

I'll take care and am happy with the conslusion
that my approach doesn't pollute table data.

I'm quite astonished at John's view on this, and have to disagree in strong
terms:

Without equivocation, without qualification, and without reservation, I
recommend: NEVER use a Lookup Field in a Table. Sooner or later, and likely
sooner than you might imagine, it will turn up in some situation and cause
you a problem that will take longer to diagnose and to solve (probably by
removing the Lookup Field) than all the Combo Boxes you'd have set up if
you'd not used it.

And, just in case I was not clear in my recommendation, let me repeat: NEVER
use a Lookup Field in a Table.

Larry Linson
Microsoft Access MVP

 

[Tony Toews]

I'm quite astonished at John's view on this, and have to disagree in strong
terms:

Without equivocation, without qualification, and without reservation, I
recommend: NEVER use a Lookup Field in a Table. Sooner or later, and likely
sooner than you might imagine, it will turn up in some situation and cause
you a problem that will take longer to diagnose and to solve (probably by
removing the Lookup Field) than all the Combo Boxes you'd have set up if
you'd not used it.

And, just in case I was not clear in my recommendation, let me repeat: NEVER
use a Lookup Field in a Table.

Whereas I respectfully disagree. A client who is a developer when he
isn't too busy doing other things has these setup. I work on his
systems when he gets too busy.

As John states we know about the problems of Lookup fields. They are
slightly irritating at times especially when you want to look at the
raw values.

But nevertheless I've been working with several systems for several
years which contain them and they aren't causing us any problems.
Just irrirations at times.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

- mask (if ever applicable; perhaps on zip codes)

This assumes that you never, ever have folks or organizations in other
countries.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Just a simple (stupid) beginners question: where to set all kinds
of field defaults (apart for the data type, of course)? In the table
definition or in each derived form?
Think of:
- formats (e.g. align number left for some silly reason)

These can be reasonable in the field although I seldom use them.

- mask (if ever applicable; perhaps on zip codes)

Never use as previously posted. Another one to think about is phone
numbers might not be in North American format.

- validation rules (e.g. maximum allowed value is 100)

Can be reasonable although I prefer to use a value on a global table
to do these kinds of things. So the user can easily change the
maximum.

- foreign key values shown as text in a drop down, using the
Lookup Wizard

Totally separate discussion. Many MVPs feel they are evil. I feel
that with appropriate knowledge of their limitations they are useful.

Personally, I prefer to set these defaults in the table because it is
difficult to remember and apply all default settings by field in each
form. But perhaps this is an amaturistic approach.

Not amateurish no. However I find that clients who have asked for
this kind of detail in the table validation later change their mind.
They frequently don't have the necessary data at the time but must
wait for some of it. So I may have queries which the users run to
show where what should be a later date is in fact earlier than another
date. Or I'll warn users or display the number of days between two
dates.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[F.H. van Zelm]

Hi Tony,

Thanks for reply and advice! See *** for my reaction (it's getting complex).
But for 99%,I think, I get your points and I agree.

Frans

These can be reasonable in the field although I seldom use them.

*** Indeed, a silly example. But it was just for the sake of asking ;-).

Never use as previously posted. Another one to think about is phone
numbers might not be in North American format.

*** Masks ... they will take your / users breath, most of the time.

Can be reasonable although I prefer to use a value on a global table
to do these kinds of things. So the user can easily change the
maximum.

*** Sales price won't be less than zero, won't it? So set this validation!

Totally separate discussion. Many MVPs feel they are evil. I feel
that with appropriate knowledge of their limitations they are useful.

*** 'And now for something completely else' says Monthy Pyton. Yes,
*** if you keep in mind the limitations, nothing bad (pollution or so)
*** happens.

Not amateurish no. However I find that clients who have asked for
this kind of detail in the table validation later change their mind.
They frequently don't have the necessary data at the time but must
wait for some of it. So I may have queries which the users run to
show where what should be a later date is in fact earlier than another
date. Or I'll warn users or display the number of days between two
dates.

*** Indeed. The client is annoyed by my questioning on what can or
*** cannot be the case. They want 'somewhere in between, some of
*** the time (in spring or day time)'. But if they want it, it can be done.

 

[F.H. van Zelm]

Hi Larry,

I start feeling 'guilty' for opening this discussion. Don't get exited,
please.

What's going on: in my 'circles' some so called 'big guys' shout: "Do ..."
or
"Never ever do ... or I'll get you." On the other hand, they can't write a
proper
sentence in Dutch (I live in the totally in sane country Holland). That
makes
me think.

To tell the truth: I only developed a few Access databases. They were not
much, complex or great. But I never had big problems with Lookup-fields,
other then described in threads above. Indeed: not to be used for filtering
and sorting on values as visible. But in those cases, I supply the real
values
from the related table(s) and field(s).
Also very true: one can do without table-lookups and create these lookups in
each separate query and form.

I really like to understand (that's an unknown mindset in my 'circles') why
and where things go wrong with lookup-fields in a table. Please give a
data design and/or SQL and/or VBA example where things go wrong on
behalf of a table-lookup.

Frans van Zelm