Field updates from lookup

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

I have a form bound to a query (labour query) which is
bound to a table (labour). A form field (resource) does a
lookup into a table (resource lookup) using a select
statement as the record source. Once a value is selected
from the dropdown box, I want to update a few field values
(rate, company, fax)in the bound query/table from the
values in the lookup table. However, those fields do not
appear on the form (I don't want them there). Is this
possible or do I have to place those fields on the form
and format them as invisible? Please let me know. Thanks!
-Howard
 
If they exist in the form's recordset, just not on the form, it is fairly
simple. One method is to do as you asked, place hidden controls on the form.
The question though is why are you adding this information to the Labour
table instead of just linking the Labour table and Resource table together
to link the data? All you should need in the Labour table is the value of
the Primary Key field from the Resource table.
 
I didn't link the tables together because I have
simplified this example. Actually, there are five resource
fields on this form, therefore there are five of each
company, rate, and fax fields. I think I would have to
include five occurrances of the resource lookup table in
the query and link each resource to aunique table. I was
just hoping for a less messy solution. Let me know. Thanks!
-Howard
 
I didn't link the tables together because I have
simplified this example. Actually, there are five resource
fields on this form, therefore there are five of each
company, rate, and fax fields. I think I would have to
include five occurrances of the resource lookup table in
the query and link each resource to aunique table. I was
just hoping for a less messy solution. Let me know. Thanks!
-Howard
Click to expand...

If you have five sets of fields in your table - you should really
consider normalizing. Embedding a one to many (one to five, in this
case) relationship in every record IS A VERY BAD IDEA. If you have
multiple resources for each entity in your main table, a better design
would be to have two tables - your current table linked to a Resources
table with a *single* company, rate, and fax field (and five records,
or four, or seven, or however many are needed).
 
You're absolutely correct. Shame on me. If I was writing
this app from scratch, I may have thought of that. Thanks
for the awakening.
-Howard
 
Also John,
In the back of my head, I may have been worried about the
complications of creating reports if I normalize the
tables. Please see my message under "reports & printing".
Thanks!
-Howard
 
Back
Top