Setting a field's default value as an value from another table

[a***@gmail.com]

I have two tables:

One called Invoice Details and the other called Parts. They are both
related via the PartID field (the autonumber key field).

In the Invoice Details table I have a field called UnitPrice and in
the Parts table, I have a field called Price.

Question: How can I set the default value of the UnitPrice field in
the Invoice Details table as the value in the Price field in the Parts
table?

I'm assuming I can use a query for this, but I have no idea how to
write it.

I'm familiar with Access, but have very limited coding experience.
I'm using Access 2003.

Thank you,

AR

[Lord Kelvan]

sounds like you want a trigger but access dose not support triggers.
If the price of your products never change then you dont need to do
that but if they do then what you have done is correct.

i do supose you are using a form to enter data you can have an
afterupdate event on a form to where you enter invoice details to
automatically insert that value into that field woudl be the best way.

Regards
Kelvan

[Ken Sheridan]

You can't do it in the table definition, but you'll find an example in the
Orders Subform in the sample Northwind database, where the AfterUpdate event
procedure of the ProductID control looks up the value of the UnitPrice column
for the selected product from the Products table and assigns it to a control
bound to the UnitPrice column in the OrderDetails table, using the following
code:

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Ken Sheridan
Stafford, England

Post by a***@gmail.com
One called Invoice Details and the other called Parts. They are both
related via the PartID field (the autonumber key field).
In the Invoice Details table I have a field called UnitPrice and in
the Parts table, I have a field called Price.
Question: How can I set the default value of the UnitPrice field in
the Invoice Details table as the value in the Price field in the Parts
table?
I'm assuming I can use a query for this, but I have no idea how to
write it.
I'm familiar with Access, but have very limited coding experience.
I'm using Access 2003.
Thank you,
AR

[a***@gmail.com]

On Sep 22, 5:53 pm, Ken Sheridan

Post by Ken Sheridan
You can't do it in the table definition, but you'll find an example in the
Orders Subform in the sample Northwind database, where the AfterUpdate event
procedure of the ProductID control looks up the value of the UnitPrice column
for the selected product from the Products table and assigns it to a control
bound to the UnitPrice column in the OrderDetails table, using the following
    Dim strFilter As String
    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "ProductID = " & Me!ProductID
    ' Look up product's unit price and assign it to UnitPrice control.
    Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)
Ken Sheridan
Stafford, England

One called Invoice Details and the other called Parts.  They are both
related via the PartID field (the autonumber key field).
In the Invoice Details table I have a field called UnitPrice and in
the Parts table, I have a field called Price.
Question: How can I set the default value of the UnitPrice field in
the Invoice Details table as the value in the Price field in the Parts
table?
I'm assuming I can use a query for this, but I have no idea how to
write it.
I'm familiar with Access, but have very limited coding experience.
I'm using Access 2003.
Thank you,
AR

Thank you Ken

That is exactly what I was looking for. A few modifications to the
code and it worked like a charm.

Regards,
Ashour

[r***@gmail.com]

Dude I have questions in access, just wondrin if u can help me

[John W. Vinson]

Post by r***@gmail.com
Dude I have questions in access, just wondrin if u can help me

Yes, but only if you ask the question.

Do note that this newsgroup was abandoned by Microsoft several years ago. Only
a very few of us ghosts still hang around it. There's a lot more traffic on
the Web forums listed below in my sig.

[r***@gmail.com]

Dude I have questions n access wondrin u can help me