1 form updating multiple tables

[Cedarguy]

Fairly new Access user; finally figured out my data model - a simple one; created all the tables (5) and relationships; cleansed and imported my data from excel and it looks pretty good; I can't for the life of me figure out how to develop a custom form (unlike a typical Access form with imbedded tables), that would create new records on the three tables; basically its an enrollment form that has customer information (table1), enrollment details (table2) and course details (table3); would appreciate some guidance; thanks

 

[rammudali]

hi
very simple. if these 3 tables are linked , create a view and place the filelds in the form which will enable to update all the three tables at a time

 

[Cedarguy]

Thank you for your advice, rammudali.
By view, I suppose you mean a query; if so, would it be an append query? last time I tried this the append query didn't appear as a selection while creating a form through the form wizard.
How does a new row get written to a table? Is it just by virtue of moving to the next new record?
You can see, I need a lot of help.
Thanks

 

[gemma-the-husky]

if you have 3 tables, then basically each table is handled separately

student details, course details, enrolment details

so lets say you have student form.

on that you can embed (link) an enrolment subform for the students enrolment on courses. with that, you can pick courses and enrol the studenbt in those courses.

you also need a way of adding new courses - which needs a completely separate form - the point being that a student and a course have nothing in common.

there are various ways of dealing with the user interface/look and feel

but basically - 3 tables = 3 forms.

if you think about, you need to add a student first. once the student is added, then you can enrol him on some courses.

here is an example i did for another thread.
View attachment members.zip

 

[Cedarguy]

Thanks GTH; so basically I'm SOL on having 1 form (reflecting the enrollment of a student) that generates a new student record, a new enrollment record and a new course record! what if I drop the course requirement as its only a support table? would the form work for just the 2 remaining tables that are linked?

 

[missinglinq]

Thanks GTH; so basically I'm SOL on having 1 form (reflecting the enrollment of a student) that generates a new student record, a new enrollment record and a new course record!

Correct! A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Several developers I know, experienced in both Visual Basic database development and Access development, estimate that development using Unbound Forms by experienced developers takes two to three times as long as it does when using Access and Bound Forms.

The only way that Bound Forms can include Data from multiple Tables is through he use of a Query, to join the Tables, and the vast majority of Multi-Table Queries are Read-Only.

As Dave said, the standard way of handling this is through the use of a Main Form/Subform scenario. This allows for the entering of data for multiple Tables from one central location, if you will.

Linq ;0)>

 

[Pat Hartman]

and the vast majority of Multi-Table Queries are Read-Only

Not so. In fact, most mulit-table select queries are updateable. It is the exception when they are not. Crosstabs and totals queries aggregate data and so they are not updateable because the identity of the individual records is not maintained so there is no way for the query engine to know which record should be updated. This also extents to queries that join to non-updateable queries. They are also not updateable. The other category of non-updateable queries is those that are based on linked tables that are not updateable. If you can't update the table, you can't update a query based on it. In my largest application, fewer than 5% of the select queries are not updateable.

Cedarguy,
Why do you think it will be easier to use a single form rather than a mainform with subforms? I can tell you from experience, that is not the case. If you create a query that joins the customer and enrollment tables, you could create a form that adds/updates records in both tables at once. But, this is a 1-many relationship so you'll end up duplicating the 1-side data for each enrolment row you add. The main/sub form eliminates that issue. In any case, a query of the three tables doesn't make sense for maintenance purposes. This query implements a many-to-many join and that means you'll have "duplication" on both sides - the customer side and the class side. Very confusing and hard to work with. Very simple to work with if you create the correct form structure.

 

[Cedarguy]

Thanks Pat,
I take it from your second para that I should stick to the form/sub-form structure as also suggested by linq earlier and utilize Access' functionality to ensure data integrity.
Thanks & Regards