Accessing a non-primary key field from one table to another

[MichaelJohnson168]

I am trying to grab the UnitPrice from Table 1 and store it in the
UnitPrice field in Table 2 whereever there is a match ProductID wise.
Table 1
[Products Table] - ProductID (Prim Key), ProductName, UnitPrice
Table 2
[Order Details Table] - OrderID (Prim Key), ProductID (Prim Key),
UnitPrice, Quantity
In the [Order Details Table] I have accessed the ProductName from the
[Products Table] by establishing a Many-to-One relationship and using a

lookup column.
[Order Details Table] is the "Many"
[Products Table] is the "One"
The problem that I am having is that the UnitPrice in the [Order
Details Table] cannot access the UnitPrice in the [Products Table].
Is there any way for the [Order Details Table] to access the
"UnitPrice" from the [Products Table] and store it in its own
"UnitPrice" field for each order detail in the [Order Details Table]?

[pietlinden]

Yes. It's called a query. =)

[MichaelJohnson168]

I have already tried a query. The [Order Details Table]'s UnitPrice
field continuously displays $0.00.

I just want the UnitPrice field in the [Order Details Table] to have
the same value of the UnitPrice field in the [Products Table].

[pietlinden]

so your goal is to get the *current* price at the time of the sale (so
if the price changes later in the Products table, your past Invoice
totals won't get hosed)?

You could use DLookup to set the price. If you download the Northwind
sample database form the MS site, there's an example of it in the
Orders subform. If you open that in design view, you'll see this
code... (well, among other things)

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

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)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub

What's happening is that the price *at the time of the sale* is being
looked up and *copied* into the subform. The version of the database I
found on their website was for (I think) Access 2000, but you can
convert it easily enough...