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)>

 

[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