none
Help Me Build This Form RRS feed

  • Question

  • I have two tables. One is a list of customers. Each one has a unique Id (ClientID) which is also the primary key. The other table is orders. Each order is associated with the ClientID as a foreign key. So when I link the two tables in a query I get a complete record customer info and their orders. 

    I'm creating two forms. I've completed the one that allows users to enter new customers. It assigns a new ClientID for each new client that is entered. Now I want to create a form that allows users to enter customer orders. First the user must choose an already existing costumer so that their ClientID can be associated with this new order. My thought is to have a section on the form where users can search for the customer by first or last name and then choose the one that they want. Then when the user selects a customer it will grab that ClientID and allow the user to continue filling in the order details.

    Hope this makes sense. I hope I haven't asked to much at once. Any help, even just a little to get me going in the right direction, would be helpful.

    tod

    Tuesday, May 24, 2016 8:35 PM

Answers

  • You might like to take look at 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 is primarily to illustrate simple inventory management, but contains an orders form of the type you have in mind.  The customer is selected via a bound combo box and their address data automatically shows in read-only controls in the form.  Note that the form also contains an order details subform. The model for this database is as below.  You can ignore the three tables on the far right if you are not managing inventory.


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, May 25, 2016 11:05 AM Typo corrected.
    • Marked as answer by David_JunFeng Thursday, June 2, 2016 5:33 AM
    Wednesday, May 25, 2016 11:01 AM

All replies

  • Hi todtown ,

    According to your description, you could create a form that contains a subform (a one-to-many form). A subform is a form that is inserted in another form. The primary form is called the main form,

    and the form that is enclosed in form is called the subform. A form/subform combination is sometimes referred to as a hierarchical form, a master/detail form, or a parent/child form.

    Subforms are especially effective when you want to show data from tables or queries that have a one-to-many relationship. A one-to-many relationship is an association between two tables in which

    the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.

    For more information, click here to refer about Create a form that contains a subform (a one-to-many form)

    Wednesday, May 25, 2016 2:00 AM
  • You might like to take look at 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 is primarily to illustrate simple inventory management, but contains an orders form of the type you have in mind.  The customer is selected via a bound combo box and their address data automatically shows in read-only controls in the form.  Note that the form also contains an order details subform. The model for this database is as below.  You can ignore the three tables on the far right if you are not managing inventory.


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, May 25, 2016 11:05 AM Typo corrected.
    • Marked as answer by David_JunFeng Thursday, June 2, 2016 5:33 AM
    Wednesday, May 25, 2016 11:01 AM