Getting "default" value from table

[kourkoutas]

I have four tables, Vehicle Entries, Jobs and Parts Used and Parts. These
tables are connected with one to many relationship in the order they are
written (i.e. one entry has many jobs, for one job many parts are used etc).
In the parts table I store the Part ID, Description and Price.
In the parts used table I store an ID, PartID and Price.
I've created a form with all the elements of the parts used table. What I
want to do, is when I select a Part ID, I want the price corresponding to the
specific part (from parts table) to be written automatically in the price
field of the parts used table (as a default value).

 

[Beetle]

It's not necessary to do that. You just display the price from the Parts
table based on PartID, you don't need to redundantly store it in the Parts
Used table. There are a few different methods you could use to display the
price like DLookup, combo box columns etc. Which method is best will depend
the types of controls you are already using on your forms, your preferences,
etc.

Also, based on your description, it appears that your Parts Used table
should be serving the purpose of a junction table between Jobs and Parts, so
it should have field like JobID as a foreign key to the Jobs table (perhaps
it already does, I can't tell for sure from your post)

 

[Amy Blankenship]

It's not necessary to do that. You just display the price from the Parts
table based on PartID, you don't need to redundantly store it in the Parts
Used table. There are a few different methods you could use to display the
price like DLookup, combo box columns etc. Which method is best will
depend
the types of controls you are already using on your forms, your
preferences,
etc.

Also, based on your description, it appears that your Parts Used table
should be serving the purpose of a junction table between Jobs and Parts,
so
it should have field like JobID as a foreign key to the Jobs table
(perhaps
it already does, I can't tell for sure from your post)

There might be an argument for storing the price in parts used if that price
will change over time, to allow for old invoices to reflect the correct
information. Another possible structure is to keep prices in a separate
table with begin and end dates, so that the form looks at the price that was
current whenever the form was filled.

-Amy

 

[Beetle]

You're right Amy. Thanks for correcting my oversight.