I am trying to make a data entry form for a supply database. Since I am a former approach user, I am use to being able to have forms that relate to more than one table. If I am understanding Access correctly, a form can only be base on one form or query. So, am I correct in thinking that to make the form I need which is based on 3 different tables that my form is going to have to be made up of 2 or 3 different sub forms? Am I on the right track? If I am totally off base could someone try to provide me with a simple explanation. Thank you!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Data Entry Forms Questions
- Thread starter Ctrain
- Start date
I am trying to make a data entry form for a supply database. Since I am a former approach user, I am use to being able to have forms that relate to more than one table.
> You can do the same with Access
If I am understanding Access correctly, a form can only be base on one form or query.
> No, do a little reading on queries. Queries can use multiple tables. The form recordsource can be based on a query.
So, am I correct in thinking that to make the form I need which is based on 3 different tables that my form is going to have to be made up of 2 or 3 different sub forms?
> No, a beginners book in Access or one of the many online turorials will help you with a better understanding of Access functionality.
> You can do the same with Access
If I am understanding Access correctly, a form can only be base on one form or query.
> No, do a little reading on queries. Queries can use multiple tables. The form recordsource can be based on a query.
So, am I correct in thinking that to make the form I need which is based on 3 different tables that my form is going to have to be made up of 2 or 3 different sub forms?
> No, a beginners book in Access or one of the many online turorials will help you with a better understanding of Access functionality.
Ctrain
A fundemental issue with data integrity is that the child records, for example PARTS for and ORDER must have access to the primary key of the primary key for the parent key which is used as the foreign key in the child records.
As long as you can maintain this concept, you should be okay.
From a practicle perspective, for a one-to-many relationship, this usually means you can append/add on the ONE side or the MANY side but not both sides of relationship at one time.
Useful approaches to handling mutiple tables is to use subforms embedded in a main form. The main form holds info from the "parent" record, and the subform(s) are used for the child records.
Subforms can be improved upon by...
- making them contineous / tabular / datasheet where many records for the child record for the specific parent can be seen at once.
- using a tab for where each tab / page references different componets / subforms.
Example:
You place an order for parts.
OrderID is the primary key.
I will use a tab form with single and contineous subforms.
Main form depicts basic order info
OrderID, OrderDate, ETADate, Supplier
First tab / page of tab form dispalys the details for the order. Each Parts record includes the OrderID as the foreign key. This is a contineous form. As orders are placed, the system maintains data integrity by automatically adding the OrderID for each new record.
Second tab / page depicts details on the customer - address, phone number, sales rep. This is just a simple single subform keyed on the SupplierID on the Order table (foreign key) and SupplierID on the Supplier table (primary key).
Third tab / page depicts past orders with the same Supplier. Again a contineous form is used to display multiple orders. A command button allows the end user to select a specific past order to look at the details to reference Order, prices, etc.
I hope this helps you envision your application.
Parting words - spend quality time on the design so the database meets your expectations.
Richard
A fundemental issue with data integrity is that the child records, for example PARTS for and ORDER must have access to the primary key of the primary key for the parent key which is used as the foreign key in the child records.
As long as you can maintain this concept, you should be okay.
From a practicle perspective, for a one-to-many relationship, this usually means you can append/add on the ONE side or the MANY side but not both sides of relationship at one time.
Useful approaches to handling mutiple tables is to use subforms embedded in a main form. The main form holds info from the "parent" record, and the subform(s) are used for the child records.
Subforms can be improved upon by...
- making them contineous / tabular / datasheet where many records for the child record for the specific parent can be seen at once.
- using a tab for where each tab / page references different componets / subforms.
Example:
You place an order for parts.
OrderID is the primary key.
I will use a tab form with single and contineous subforms.
Main form depicts basic order info
OrderID, OrderDate, ETADate, Supplier
First tab / page of tab form dispalys the details for the order. Each Parts record includes the OrderID as the foreign key. This is a contineous form. As orders are placed, the system maintains data integrity by automatically adding the OrderID for each new record.
Second tab / page depicts details on the customer - address, phone number, sales rep. This is just a simple single subform keyed on the SupplierID on the Order table (foreign key) and SupplierID on the Supplier table (primary key).
Third tab / page depicts past orders with the same Supplier. Again a contineous form is used to display multiple orders. A command button allows the end user to select a specific past order to look at the details to reference Order, prices, etc.
I hope this helps you envision your application.
Parting words - spend quality time on the design so the database meets your expectations.
Richard
- Thread starter
- #4
- Status
Similar threads
