locked
Pass Control from Details Form within Subform to Main Form RRS feed

  • Question

  • I have a Main Form with Customer Information.

    On the Main Form I have a Subform [OrderDescription_subfrm] viewed as Continuous with a Button that displays the Order# and a TextBox that has a concatenation string from a 2nd Form [OrderDetails_frm]

    John Smith Customer#1

    {Order1} 5-Hats; 6-Shirts; 3-Sweatshirts

    {Order2} 10-Hats; 5-Shirts; 1-Sweatshirt

    {NEW}

    When I click on the button {Order1} in the subform, the [OrderDetails_frm] opens to display the details of that order

    Order1

    Hats {5}

    Shirts {6}

    Sweatshirts {3}

    I can change the amount for each order, and all works fine.  However, when I click the {NEW} button in the subform, the [OrderDetails_frm] opens, I enter the amounts of each item, but when I close the [OrderDetails_frm] the new order does not link to the Main Form.  I new record is created without the CustomerID from the Customer_tbl.

    How can I pass the CustomerID through the subform to the details form? 


    • Edited by tripntoys Sunday, February 26, 2017 2:52 AM
    Sunday, February 26, 2017 2:50 AM

All replies

  • Hi, You have at least two options: 1. Pass the ID using the OpenArgs method and assign it to the foreign key in the Open event of the popup form, or 2. Set the default value of the popup form with a reference to the main form Hope it helps...
    Sunday, February 26, 2017 4:26 AM
  • Firstly, it should not be possible for such a row to be inserted.  It either means that the foreign key CustomerID column in the OrderDetails table allows Nulls (its Required property is False when it should be True), or it has a DefaultValue property of 0, which Access has automatically given it when you designed the table.  In the latter case the 0 should be deleted from the property in table design view.  Whichever is the case, it suggests that referential integrity is not being enforced; it should be.

    Having said that, your OrderDetails table should not have a CustomerID column. It suggests that you don't have an Orders table, but I find that hard to believe.  It's more likely the column is included redundantly in the table, which would mean it is not normalized to Second Normal Form (2NF).  The standard model for an ordering database comprises, in broad outline the following tables:

    Customers
    ….CustomerID  (PK)
    ….FirstName
    ….LastName
    ….etc

    Orders
    ….OrderID  (PK)
    ….CustomerID  (FK)
    ….OrderDate

    Products
    ….ProductID  (PK)
    ….Product
    ….UnitPrice

    And to model the many-to-many relationship type between Orders and Products:

    OrderDetails
    ….OrderID  (FK)
    ….ProductID  (FK)
    ….UnitPrice
    ….Quantity

    If the above OrderDetails table were to include a CustomerID column the table would not be in 2NF as CustomerID would be functionally determined by OrderID, which is only one part of the composite key OrderID/ProductID.

    The model is thus like this:

    Customers---<Orders---<OrderDetails>---Products

    The usual interface would be an orders form, in single form view, within which is an OrderDetails form, in continuous forms view.  You'll find a simple example of this in Inventory.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates some basic methodologies for inventory management, but happens to include an orders form/subform as described above.  A more complex form can be found in InvoicePDF.zip in the same OneDrive folder.  The latter file is primarily intended to illustrate how to output a report (invoice) as a PDF file, but is based on a hypothetical invoicing database, whose structure is identical to an ordering database.

    With such a model, you could if you wished, show the items ordered per order as a concatenated list in an orders subform within a customer's form, using a suitable function to concatenate values from a related table (see Concat.zip in my OneDrive folder for such a function), but to insert a new order for the current customer you would have to open an orders form with an order details subform as described above.  The CustomerID value would then have to be passed to the orders form as described by theDBguy, e.g. by means of the OpenArgs mechanism.

    A better solution, to my mind, would be to have two correlated subforms within a customers form.  The CorrelatedSubs demo in my OneDrive folder illustrates this, using data from Northwind, and also includes a solution using nested subforms.


    Ken Sheridan, Stafford, England


    Sunday, February 26, 2017 6:27 PM