Results 1 to 6 of 6
  1. #1
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101

    Editing data on single/multiple subforms

    I have 2 tables similar to the following example:
    Table A: Customer details
    The other tables contain details of orders placed by the customer
    Table B: Orders - Books
    Table C: Orders - Groceries
    Table D: Orders - Electronics
    Table A has one-to-many relationship with Tables B, C, and D.

    Question 1: One form and one sub-form


    Suppose I create a single form, where I place controls from Table A and Table B as a sub-form (displaying records as a spreadsheet/table) [NOTE: I did not display the sub-form as a form, as it would display only one record at a time while a spreadsheet/table would display a list of records linked to the customer]. User should now be able to click on a customer, and then add/edit records in the sub-form. Assuming that Table B has numerous fields, the user now has to drag the horizontal scroll bar on the sub-form to enter data. Now, is that a good idea?

    Question 2: One form and multiple sub-forms
    Suppose I wish the user to enter each of the orders pertaining to a customer sequentially (e.g. Customer details (Table A) > Orders - Books (Table B) > Orders - Groceries (Table C) > Orders > Electronics (Table D)), what is the best option for that?

    Option A: Single form with multiple sub-forms on the pages of a tabbed form

    • Create a single form
    • Add controls from Table A
    • Beside it add a tabbed form
    • Add all the other tables as sub-forms on the pages of the tabbed form


    Option B: Single form with sequential sub-form pop-ups (I do not know how to do this!)

    • Make the user enter enter the customer details first
    • He clicks on a button which opens 'Orders - Books' as a sub-form pop-up window
    • After completing entry here, he clicks on a 'Next' button' on this sub-form, which closes the current sub-form and open the next sub-form 'Orders-Groceries'.
    • The second sub-form 'Orders - Groceries' will have two buttons 'Previous' and 'Next' to navigate between the sub-forms, and so on.


    Option C: Separate forms for each table

    • User enters Form A with details from Table A
    • Clicks a button which opens Form B for Table B
    • I do not prefer this, as this method will not display the relation between the customer and his orders as 'Master and Child'.

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 10 Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    Here is a tutorial on sub forms. BTW. If I were doing this, I would only have 2 tables. 1 for the customer and 1 for the orders. I would add a field to the order table indicating the type of order (ie. Books, Groceries, Electronics). This will make retrieving the data easier and will help to normalize your data. You may want to read about normalization in the second link.

    http://www.datapigtechnologies.com/f...subforms1.html
    http://sbuweb.tcu.edu/bjones/20263/a...sdb_design.pdf

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    I agree with Alan re tables.

    Customer --->Order

    If an Order can be for Many Products then I would revise that set up to 4 tables

    Customer--->Order--->OrderDetails<----Product

    I would start with a draft model of the things involved, and use the business description/facts to establish relationships. Do you have a clear description of the business and processes?
    I would not start with Forms.

    Good luck with your project.

  4. #4
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    Thanks for the information, but I am still having some issues:
    @alansidman: How can I use just 2 tables? Suppose I use CustomerTable and OrderTable. A customer can place many orders. Each order can contain different items (Books, groceries, electronics). The item description is different for each item (Books - title, pages, weight; electronics: display size, battery duration). So, I have to create a separate table for each item.
    Suppose I do manage to use only two tables and then display them as a form and sub-form, respectively, editing data on the sub-form will be difficult as it will have too many fields (as explained in Question 1). Is there a better method for displaying OrderTable?

    @Orange: "Customer--->Order--->OrderDetails<----Product" seems okay, but I am not able to correctly create tables for it. Could you please help me out?
    I have attached the image of an excel table for the desired structure:
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	36.8 KB 
ID:	25372
    Please note that the ItemDescription will be different for different items, so different items cannot be placed in similar columns and they have to have their own tables with their own -item-specific description columns.

    I understand that I have to create the following tables:
    -CustomerTable (has customer name, address, etc.): CustomerID is primary key and has one-to-many relation with OrderIDTable
    -OrderIDTable (has order date and other details): OrderID is the primary key
    -BookTable (has the descriptive columns for books): OrderID is the primary key
    -ElectronicsTable (has the descriptive columns for electronics): ElectronicsID is the primary key
    -GroceriesTable (has the descriptive columns for groceries): GroceriesID is the primary key
    OrderID Table has one to many relationship with the ID of each of the above tables.

    But this is not exactly what you suggested it seems!

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Please note that the ItemDescription will be different for different items, so different items cannot be placed in similar columns and they have to have their own tables with their own -item-specific description columns.
    This is incorrect, generally.
    Database is quite different than spreadsheet.
    With database you would have an Item table and possibly an ItemType table.
    If you really have different Items with different attributes, then tell us more about your requirements.

    This is a general approach to Item and ItemType.

    tblItem

    ItemID PK
    ItemName
    ItemDescription
    ItemTypeId FK to ItemType table.


    tblItemType
    ItemTypeId PK
    ItemTypeName
    OtherItemTypeInfo???

    ItemTypeName could be groceries/Electronics.....

    You should review database Normalization.

    Here are some samples of generic Customer Order Product data models. I hope they are useful.

    Click image for larger version. 

Name:	CustAddrOrderProd_Ambler.jpg 
Views:	13 
Size:	20.7 KB 
ID:	25373

    CustomerOrders

    If you do require separate tables for different Item types, you should research Database Supertype and Subtype.
    Here is a link to get you started, but read the caveats and related materials.


    I recommend you watch videos #1,2 and 4 in this free youtube Database series by Dr. Daniel Soper before you do any design. The series starts here.

    Good luck with your project.

  6. #6
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    Thanks a lot!

    I will go through all the information in detail and report back if the issue persists!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding data to multiple tables with subforms
    By mooserider2 in forum Access
    Replies: 3
    Last Post: 03-02-2015, 02:41 PM
  2. Replies: 7
    Last Post: 02-18-2015, 10:13 PM
  3. Multiple Subforms with Related Data
    By funkymuppet in forum Forms
    Replies: 1
    Last Post: 04-01-2014, 08:40 PM
  4. Editing data from multiple queries
    By MHernan1 in forum Queries
    Replies: 2
    Last Post: 08-16-2012, 05:01 PM
  5. Editing Multiple Rows of Data with (Ctrl F)
    By Orangeworker in forum Access
    Replies: 2
    Last Post: 11-11-2010, 03:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums