Form Used to Update a Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with several fields on it which calculate or lookup data based
on the input from other areas of the form. The problem that I have is that
the calculated or looked up data doesn't seem to update in the underlying
table. And in some cases, when I advance to the next record, it retains the
value from the previous record. How do I get calculated or look up data on a
form to update the database for each individual recor? Thanks.
 
How do I get calculated or look up data on a
form to update the database for each individual recor?
Click to expand...

Generally, you don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson[MVP]
 
Hey John,
Thanks for the advice. Your response has raised a few more questions that I
would like to ask. When I created my database, I had several fields in a
table which required data from other tables. I selected the "lookup tab" in
the database design mode and set the row source properties to the appropriate
tables. When I created my form for user entry, the combo boxes for these
fields already had the choices available in the list because I chose combo
box as the type in the database design. I have recently read a lot of posts
and seen more than once where it is not suggested to use lookups in a
database. Is this what you and the others are referring to as a lookup. I
am teaching myself Access as I go and this has confused me so any assistance
you can give would be greatly appreciated.
 
I have recently read a lot of posts
and seen more than once where it is not suggested to use lookups in a
database. Is this what you and the others are referring to as a lookup. I
am teaching myself Access as I go and this has confused me so any assistance
you can give would be greatly appreciated.
Click to expand...

Just to clarify:

I use lookups all the time IN DATABASES. They're an essential tool.

I never use lookups IN TABLES.

I only use them on Forms; tables are for data storage, and should be
reserved for that purpose. Combo Boxes in tables make it easier to use
table datasheets for data editing and data entry - but that's the
wrong place to DO data editing and data entry; you should do those
activities on Forms (with appropriate and frequent use of combo
boxes!) Table datasheets should be used for debugging and design; and
for these purposes, it's much better if you can see what's actually
*in* the table. A Lookup conceals the actual table contents behind the
looked-up combo box.

I will grudgingly admit that putting a Lookup Field in a table does
make it a couple of mouseclicks easier to put a Combo Box
(appropriately!) on a Form. That is the only benefit that I can see to
the feature.

John W. Vinson[MVP]
 
Back
Top