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