From your reply to John Vinson:
I have two tables: table "items" with details on items I sell, second table
called "prices" which has the historical prices of each item. I want a
report and form to show me the appropriate price corresponding to the date of
entry?
While you can implement a design that stores historical prices for each
item, in order to later produce a report that shows price vs. time for a
given item, I think such a design would be a lot more difficult to pair up
with a given Order Detail record. So, this historical information, if you
choose to keep it, would be an additional table, but not a replacement for a
more standard many-to-many (M:N) relationship that involves three tables:
Products
Order Details
Orders
where UnitPrice is stored in the Products and Order Details tables. The
many-to-many relationship is made up of two one-to-many (1:M) relationships,
where the Order Details table has the foreign key of both relationships. In
words, you have the following:
1.) An order can include zero to many products and
2.) A product can be found in zero to many orders
But, how does Northwind make the link of to "orders detail" table?
The Orders form includes an Orders Subform. The Northwind sample for Access
2003 and prior versions utilizes an AfterUpdate event procedure (ie. VBA
code) for the combo box on the Orders Subform named Products. It includes use
of a Domain Aggregrate function, DLookup, to assign a "lookup up" price to
the UnitPrice control that is bound to the UnitPrice field in the Order
Details table. Here is the associated code that runs, when a person selects a
product:
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
Frankly, an easier (and more efficient method, IMHO) would be to include the
Products table UnitPrice (the current price) in the Row Source for the
Products combo box. This way, you don't need to call the DLookup function at
all. Something like this:
Original RowSource is a SQL (Structured Query Language) statement. You can
leave it as a SQL statement, or save it as a saved query. In either case, it
is still considered a query:
SELECT DISTINCT [ProductID], [ProductName], [Discontinued]
FROM Products
ORDER BY [ProductName];
Revised Rowsource
Note: You shouldn't need the DISTINCT keyword. Also, the square brackets are
required only if your field names include spaces or other special characters
such as a hyphen, or reserved words:
SELECT ProductID, ProductName, Discontinued, UnitPrice
FROM Products
ORDER BY Products.ProductName;
Increase Column Count from 3 to 4
Modify column widths: 0";2.2";1" ---> 0";2.2";1";0" (or 0.5" to display
price)
Change the AfterUpdate procedure for the ProductID combo box to this:
Private Sub ProductID_AfterUpdate()
On Error GoTo ProcError
Me.UnitPrice = Me.ProductID.Column(3)
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ProductID_AfterUpdate..."
Resume ExitProc
End Sub
Notes:
In VBA code, the columns of a combo box are "zero-based". Thus, column 0 is
the first column in the row source, column 1 is the second column in the row
source, etc. This is why I specified column(3), above, since the new
UnitPrice field that I added was added as the fourth field to the rowsource
for the combo box.
I recommend using the dot notation, with the period, instead of the bang
notation (!), when you have a control on the form with the referenced name
(UnitPrice control).
Do a Debug | Compile {ProjectName} after making changes to VBA code. You
want to ensure that you do not have any compile-time errors.
~~~~~~~~~~~~~~~~~~
I recommend that you spend some time reading up on database design. Here is
a good link to start with:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
Make sure to read the first two papers by Michael Hernandez. He is the
author of the book "Database Design for Mere Mortals", and is considered an
expert in this field.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
