M
Matt F
I have a very simple problem that is driving me nuts. I have the following
tables with these pertinent fields:
Products
-ID
-ProductCode
-ProductName
-Price (current price)
Transactions
-ID
-Date, customer, etc
TransactionDetails (one record per item sold)
-ID
-TransactionID
-ProductID
-Qty
-Item Price (this need not be the same as the price in the Products table.
This is data that refers to a specific transaction detail and I do not want
historical data to change when I alter my current prices on the Products
table.)
Query:
Transaction Details Extended
-Fields for extended price information ie: taxes
Forms:
Transaction Form
Transaction details subform
In the transaction details subform I can create a new transaction details
record, and by looking up an item by product code I can have my product ID
and product name all display correctly in the subform. The default value for
'quantity' in the transaction details table is '1' and this works great. My
price shows up as $0.00. Now, when I change the price to the value I want,
all my extended price information (taxes) works just fine. If I change the
value of the 'product name' field in this subform, it changes the value in my
Products table. This is not a problem because I can just lock it. I want to
be able to change the price to whatever I want from this form, and this is
why I do not have a relationship to the products table price field. However,
when I create a new record by looking up an item by product code, I would
like the current price, located in the Products table, to be the default
value for the Item Price in the Transaction Details table. Instead, the value
that comes up for price is $0.00.
In the Northwind example, this works perfectly in the 'Orders' form. I've
looked at it and can't figure out what I have wrong. Any suggestions would be
very appreciated, and I would be happy to provide any extra details or
clarifications if neccesary.
Thanks!
tables with these pertinent fields:
Products
-ID
-ProductCode
-ProductName
-Price (current price)
Transactions
-ID
-Date, customer, etc
TransactionDetails (one record per item sold)
-ID
-TransactionID
-ProductID
-Qty
-Item Price (this need not be the same as the price in the Products table.
This is data that refers to a specific transaction detail and I do not want
historical data to change when I alter my current prices on the Products
table.)
Query:
Transaction Details Extended
-Fields for extended price information ie: taxes
Forms:
Transaction Form
Transaction details subform
In the transaction details subform I can create a new transaction details
record, and by looking up an item by product code I can have my product ID
and product name all display correctly in the subform. The default value for
'quantity' in the transaction details table is '1' and this works great. My
price shows up as $0.00. Now, when I change the price to the value I want,
all my extended price information (taxes) works just fine. If I change the
value of the 'product name' field in this subform, it changes the value in my
Products table. This is not a problem because I can just lock it. I want to
be able to change the price to whatever I want from this form, and this is
why I do not have a relationship to the products table price field. However,
when I create a new record by looking up an item by product code, I would
like the current price, located in the Products table, to be the default
value for the Item Price in the Transaction Details table. Instead, the value
that comes up for price is $0.00.
In the Northwind example, this works perfectly in the 'Orders' form. I've
looked at it and can't figure out what I have wrong. Any suggestions would be
very appreciated, and I would be happy to provide any extra details or
clarifications if neccesary.
Thanks!