Unanswered: Is it possible to have a form update a table that is different than the source table?
I'm using Access 2003. I have a form with a subform which will be used as an order form. It pulls info from 2 different tables, the customer info and the products available. When I choose a customer and product and add quantity, price and total price, I want it to update the Orders table, but it is currently updating the Products table because that is the record source. Is this possible? I have been struggling with this for a month now and i have found anything that helps me.
Well, it's possible but it sounds like you have it backwards. Your form should be bound to the orders table. Typically you might choose a product from a combo box that gets its data from the products table, but is bound to the orders table (ie, row source is products table but control source is orders table).
OK, I started from scratch, but I'm stuck again. When I created the forms, it asks what table I'm getting the info from, so I choose the Member table. For the subform, I choose the Products table.
I then chose all the fields that I wanted on the Form choosing fields from different tables. A subform for the products was automatically created. So the Record Source for both forms is a SELECT statement with all of the fields that I selected.
Now, when I enter Member and Product info on the form and close out, it updates the Member and Product tables. So I still think that I'm missing something here. I can change the record source to the Orders table but that doesn't work, because I don't have all of the fields on the form in the Orders table.
[tblMembers] with fields:
MemberId (Primary key)
CAPID (Indexed, no duplicates)
ProductID (Primary key)
SKU# (Indexed, no duplicates)
OrderDetailID (Primary key)
[tblOrders] - Not sure if I even need this table
OrderID (Primary key)
tblMember is linked to tblOrders by MemberID
tblOrders is linked to tblOrderDetails by OrderID
tblOrderDetails is linked to tblProducts by ProductID
My Main Form (frmOrders) has a record source of tblMember
The Subform (frmOrdersDetails) has a record source of tblProducts
The Name field from tblMember is a combobox that has the Name, Rank, Address1, etc as choices and autofills the info depending on the Name you pick. The Rowsource of the combobox is:
SELECT tblMember.Name, tblMember.Rank, tblMember.Address1, tblMember.Address2, tblMember.City, tblMember.State, tblMember.ZipCode, tblMember.MemberID, tblMember.CAPID FROM tblMember ORDER BY [Name];
The SKU# field from the tblProducts is a combobox that has the SKU#, Item Description, Unit and Price. It also has the Quantity and Total Price from the tblOrderDetails table. The rowsource for this combobox is:
SELECT tblProducts.ProductID, tblProducts.[SKU#], tblProducts.[Item Description], tblProducts.Unit, tblProducts.Price FROM tblProducts ORDER BY [SKU#];
Your orders form should have a source of the orders table (which I do think you need). The subform would have a source of the order details table. As I mentioned, while in the orders form, I would probably have a combo box to select which member the order was for. That combo would have a row source that looked at the member table, but its control source would be the field in the orders table. Same thing for products in the order detail table.
You mentioned not having those fields in the order table, and I assume you mean the member address, etc. Generally, you wouldn't save that info there, just the member ID. There are some situations when you might want to save it, since addresses can change and you might want to document what address each order was sent to, in case it changes later. If that's the case here, you'd add the fields to the orders table and fill them in when you select a customer.
OK, I think I'm on the right track now. My Orders and OrdersDetails table are being updated with the correct information and I ran a test query (for a report down the road) and I got all the info that I wanted. There are still a few things I have the work out, but I will try to figure them out myself and then come back.
One problem I'm having now is autofilling the ItemDescription, Unit and Price when choosing the SKU# from a combobox in the Subform. I do the same thing in the main form when choosing the Member Name from a combobox and it works perfectly.
Here is what I have in the AfterUpdate event of the Name combobox of the main form:
Private Sub Name_AfterUpdate()
Me![Rank] = Me![Name].Column(2)
Me![Address1] = Me![Name].Column(3)
Me![Address2] = Me![Name].Column(4)
Me![City] = Me![Name].Column(5)
Me![State] = Me![Name].Column(6)
Me![ZipCode] = Me![Name].Column(7)
Me![CAPID] = Me![Name].Column(8)
I put the same type of code in for the subform but it didn't work, so I changed the code in the After Update Event in the SKU combobox on the subform to this:
Private Sub SKU_AfterUpdate()
Me![frmOrderDetails Subform].Form![Item Description] = Me![SKU].Column(2)
Me![frmOrderDetails Subform].Form![Unit] = Me![SKU].Column(3)
Me![frmOrderDetails Subform].Form![Price] = Me![SKU].Column(4)
Thank you so much for all your help. I now understand what I was doing wrong with the forms and tables.
Private Sub SKU_AfterUpdate()
Me![Item Description].Value = Me![SKU].Column(2)
Me![Unit].Value = Me![SKU].Column(3)
Me![Price].Value = Me![SKU].Column(4)
But now I have a new problem...LOL...I fix one thing and something else doesn't work.
The problem now is that when I choose a 2nd and 3rd SKU#, the ItemDescription,etc. updates all of the items on the subform with the current one. I'm assuming that it is because the ItemDescription, Unit and Price are unbound controls. When I try to set the controlsource of the control the dropdown box only shows the fields for the OrderDetails table which is the recordsource of the subform. So, I tried to use the Expression Builder to point it to the Products table/ItemDescription field, but ended up with #Name? in the control when I open the form. This is what the controlsource looked like when I used the Expression Builder: [tblProducts]![Item Description].
Thank You. That worked and it makes sense to me. I have a few other things to work on like clearing the form when I open it so that it is blank and saving the records. Should I need to create a command button like "Submit Order" which will update the table with a new record and do whatever else I want to do, like print a report?