Get default value from another table

[Guest]

I have two tables, parts and parts used. These have a one to many
relationship. On a form I have all the fields from parts used table. To make
a new record in the parts used table I select from a lookup list a record
from the parts table. Both tables have price as a field.
What I want is to update the default value of the price (parts used) with
the price of the parts table. I thought of using VB coding in the lookup's
After Update event, however something seems to go wrong. Any ideas how I can
update this value?

 

[Jeff Boyce]

If your [Parts] table already holds both [part] and [partprice], why do you
need both in another table (i.e., [PartsUsed])? Would it be sufficient to
store [partID] in the second table and use a query to get the price from the
corresponding row in [Parts]?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Because a part's price tends to change from time to time, and therefore,
changing it in parts table will affect previous sales. Therefore, keeping a
separate price in the [parts used] table is essential, since it will provide
me a history of the prices each part has been sold. Is there a way to do so?

If your [Parts] table already holds both [part] and [partprice], why do you
need both in another table (i.e., [PartsUsed])? Would it be sufficient to
store [partID] in the second table and use a query to get the price from the
corresponding row in [Parts]?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have two tables, parts and parts used. These have a one to many
relationship. On a form I have all the fields from parts used table. To make
a new record in the parts used table I select from a lookup list a record
from the parts table. Both tables have price as a field.
What I want is to update the default value of the price (parts used) with
the price of the parts table. I thought of using VB coding in the lookup's
After Update event, however something seems to go wrong. Any ideas how I can
update this value?

 

[UpRider]

I thought of using VB coding in the lookup's

After Update event,

I assume this is the after update event of a combo box. Please post the VBA
code.

UpRider

 

[Jeff Boyce]

Check Access HELP for use of the INSERT SQL statement.

Another approach would be to create an append query and look at the SQL view
of that to get a sense of the syntax you'd need.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Because a part's price tends to change from time to time, and therefore,
changing it in parts table will affect previous sales. Therefore, keeping a
separate price in the [parts used] table is essential, since it will provide
me a history of the prices each part has been sold. Is there a way to do so?

If your [Parts] table already holds both [part] and [partprice], why do you
need both in another table (i.e., [PartsUsed])? Would it be sufficient to
store [partID] in the second table and use a query to get the price from the
corresponding row in [Parts]?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have two tables, parts and parts used. These have a one to many
relationship. On a form I have all the fields from parts used table.

To
make

a new record in the parts used table I select from a lookup list a record
from the parts table. Both tables have price as a field.
What I want is to update the default value of the price (parts used) with
the price of the parts table. I thought of using VB coding in the lookup's
After Update event, however something seems to go wrong. Any ideas how

I
can

update this value?