Using MS Access/Table Relations in Form and Subform
QUESTION: I have a problem organizing tables and their relations. Here is what I have (made up for simplicity):
Table1: CustomerID (PK), CustomerName, Description, Address
Table2: ProductID (PK), ProductDescription, Cost
Goal: Create a form for recording all purchased transactions in a given date (TransactionDate). In the form a use selects CustomerName (dropdown) and TransactionDate. In the subform the user selects ProductDescription (dropdown), Cost is filled out automatically. In the same subform the user enters Quantity and Delivery method for each transaction. Each time the form is opened it has to be completely blank (form property Data Entry = Yes). All transaction data should be stored only in one table.
So I created Table3 with TransactionID (PK), TransactionDate, CustomerID (FK), ProductID (FK), Quantity and Delivery
Tables 1, 2 and 3 are linked through their PK-FK. All is good so far.
I created a query based on the relations above. This query becomes a source for the main form and subform.
Here is my problem: Instead of one record in Table3 I have 2 records:
1. CustomerName and TransactionDate
2. CustomerName, ProductDescription, Quantity and Delivery. Apparently, I am doing something wrong with Transaction Date. Any hint on what I am doing wrong?
ANSWER: "This query becomes a source for the main form and subform."
That's probably where you go awry.
So If I understand you, you are not tracking orders per se. You just want to know what products a customer bought, when and how much?
So your tables are OK. But the main form should be based ONLY on Table 1. The subform ONLY on Table 3 and linked on CustomerID. The combo bound to the ProductID FK should have a Rowsource of:
SELECT ProductID, ProductDescription, Cost
ORDER BY ProductDescription;
The bound column should be 1, the Column count 3 and the column widths 0";2";0"
Then in the After Update event you have one line of code:
Me.txtCost = Me.cboProduct.Column(2)
Now I don't know if you really want the subform to be Data Entry = Yes. Unless you have a single Form view.
---------- FOLLOW-UP ----------
QUESTION: If my main form would be based ONLY on Table1 then where I get TransactionDate? It is now in the Table3
ANSWER: Right, that's where it belongs. Your subform is bound to Table 3.
---------- FOLLOW-UP ----------
QUESTION: Sorry, I do not follow you. My TransactionDate is actually located in the main form because in one day for one customer there will be many products purchased. The form is filled out on the daily basis.
The Transaction Date belongs in the Transaction table. IF you had an Order table that sat as the header for the Transactions, then the Transaction date would belong in there. In that case the Order table would be the source of your main form. But that's not the way you described it.
You can still put an UNBOUND control on the main form and enter the date into there. Then have the Default value of the Transactiondate field in the subform, reference that control so it pulls the date from there. This way you only have to enter the date once and all the new Transactions you enter will pull the same date.
But, if the form is filled on a daily basis so the records are being entered the same day, then all you need is to default the TransactionDate to =Date().