468,253 Members | 1,284 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

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

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

Nov 13 '05 #1
3 1702
Yes. It's called a query. =)

Nov 13 '05 #2
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].

Nov 13 '05 #3
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...

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by sachin_mzn | last post: by
1 post views Thread by mark kurten | last post: by
3 posts views Thread by sam | last post: by
2 posts views Thread by =?Utf-8?B?SklNLkgu?= | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.