Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2008
    Posts
    13

    Unanswered: Is it possible to have a form update a table that is different than the source table?

    Hi,

    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.

    Let me know if you need more info.

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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).
    Paul

  3. #3
    Join Date
    Mar 2008
    Posts
    13
    Thank You. That was the direction I thought that I should go. I will need to make some changes and I'm sure I will have more questions.

  4. #4
    Join Date
    Mar 2008
    Posts
    13
    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.

    Thank You for your help.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db with your table structure?
    Paul

  6. #6
    Join Date
    Mar 2008
    Posts
    13
    Here is what I have so far.

    Tables:
    [tblMembers] with fields:
    MemberId (Primary key)
    CAPID (Indexed, no duplicates)
    Name
    Rank
    Address1
    Address2
    City
    State
    Zip
    PhoneNumber

    [tblProducts]
    ProductID (Primary key)
    SKU# (Indexed, no duplicates)
    Item Description
    Unit
    Price

    [tblOrderDetails]
    OrderDetailID (Primary key)
    OrderID
    ProductID
    Quantity
    Total Price

    [tblOrders] - Not sure if I even need this table
    OrderID (Primary key)
    MemberID
    Order Date

    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#];

    Let me know if you need anything else.
    Thanks

    The Main and subforms are linked by OrderID

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  8. #8
    Join Date
    Mar 2008
    Posts
    13
    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)
    End Sub

    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)
    End Sub


    Thank you so much for all your help. I now understand what I was doing wrong with the forms and tables.

    Thanks
    Mary

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If both the combo and the controls to be filled are on the same subform, then you just want to use

    Me.[Item Description] = Me![SKU].Column(2)
    Paul

  10. #10
    Join Date
    Mar 2008
    Posts
    13
    After seaching around, I found that this worked:

    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)
    End Sub

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

    Thanks
    Mary

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you just want to display those for the user, since they're already saved, your textboxes can have a control source like:

    =[SKU].Column(2)
    Paul

  12. #12
    Join Date
    Mar 2008
    Posts
    13
    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?

    Thanks
    Mary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •