Get default value from another table


G

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?
 
Ad

Advertisements

J

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/
 
G

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?

Jeff Boyce said:
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/


kourkoutas said:
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?
 
U

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
 
Ad

Advertisements

J

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/

kourkoutas said:
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?

Jeff Boyce said:
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/


kourkoutas said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top