Link to home
Create AccountLog in
Avatar of Taras
TarasFlag for Canada

asked on

MS Access update sub form from another sub form.

I want to keep track of particular expenses for tutoring for each course a student take if it exist. I attached some example db to better present the issue.

On the main form I have data about student and  two sub forms Courses and Tutoring Expenses.

I want to select student e.g.  Bob Jonson and got on Course form one record course = Math and on Tutoring form two records. I entered sample data that should match that.

If I select Susane Perkin I got on Course form two record on first record English she does not have tutoring expenses when switched to second record (course- Art) she has one tutoring expenses.

If I want to add new course on form Course at that point tutoring form should be empty.

Switching between courses of one student should switch its tutoring expenses too if exist on tutoring form.

If I want to add tutoring expenses I should be able to add it on tutoring expenses form and it will be related to course that is displayed.

On attached db selection of student only refer to particular courses on course form. Tutoring Expenses form is not connected, not sure how to connect it.

What is the best way to do this?

 Setup Tutoring form as sub form of Course and make Parent Child relation or another way that another way should be update of Tutoring form after any change of Courses form?

I would appreciate some details or steps for any suggestions. Thank you.

Avatar of Taras
Flag of Canada image


Avatar of Bitsqueezer
Flag of Germany image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Taras


Thank you so much bitsqueezer, it was very helpfull with a lot detail and good sugestion. Excelent job.
Avatar of Taras


Bitsqueezer just one small question ,How record in form header in frm_Course is changed when you click on course  in detail as they are not connected and coming from two different sources?


The course information I've locked in the header to avoid that someone changes it here. Reason is that the course list is a general list which can be assigned to many students. If you change it here (which would be possibe) it would change that also for all students who use that course. If you want i.e. an individual price for different students you could add that to the Tutoring table as new row or you could add it to the m:n table.

But usually, a lookup table would not be changed in such form, you would normally create an own input form for the course to show the user that this is a general information. You could also display the number of students assigned to it.

Of course this model is indeed incomplete. A course can be the same over years, but surely the price will change over time. So normally you would add another table with the course prices and a date since when it is valid so you can calculate older course/student values and also current.

You can unlock the course fields in the header, if you want. But be careful: Usually an UPDATE operation (which also happens here in the background if you change and save the data) can only change exactly one table. Access is a little bit different as it sometimes allows more than that, but that's only be done with some internal tricks. So the main table you change in the middle form is the student/course m:n table, nothing else! It can happen, if you unlock the course fields here and change course data and also m:n table data at the same time and try to save that that Access will throw a (correct!) error then. So better don't do that, use an own form.
Another possibility is to insert a subform with only the course table fields into the header (and ignore the error message that this is not possible with a continous form and reset the form to continous after clicking away the error - it IS possible...veeery old error of Access...). In that case the middle form is still only to enter data into the m:n table and the course subform only handles the course (and the subform can also be bound to the middle form with the course ID).

Oh, and not to forget: If you click the subform container control, you see the connection between the forms in the controls "link" fields in the "data" tab. This is what Access automatically let find the right records, this also inserts new IDs of the main form to the subform. Magic of Access...:)