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

asked on

MS Access parent child connection on bound forms

In access 2016 I have main form frmStudent with two sub forms( sfrmCourses ,sfrmTutoring_Payment).

Sub forms are in detail section of frmStudent.

All forms are bound to queries.

I have four tables, I will not mention all fields from those tables only those  that are important for my question:

  1. tblStudent Pk Student_ID auto number,
  2.  tblCourse Pk Course_ID auto number
  3.  tblTuroring Pk Tutoring_ID auto number and Student_Course_ID as foreign key from table tblStudent_Courses
  4. tblStudent_Courses with three fields (Student_Course_Id auto number pk, Student_Id, Course_ID)

table tblStudent_Couses  is cross table between Student and Courses.

 

My question is regarding connecting main and sub forms through parent child fields and relating queries.

When I am creating query for main form(frmStudent) do I have to base it on tblStudent and tblStudent_Course and when I am creating query for sub form sfrmCourse should It be based on table tblCourse and tblStudent_Course. On that way I can use Parent field Course_ID as Parent Child connection or it should be something different… ? At the end which table to use  for query that sub form sfrmTutoring_Payments should be based and what is parent child fields.

Some rules: One student could have several courses, and a course could be taken by several student, and for one course that a particular student takes it could be several Tutoring payments.

 

 

 

ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

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

ASKER

I Christian, thank you for those clarification I went back to your demo database and checked connection between main and sub form it is more clear now. One question you mentioned your demo db if you can explain how you make it that when you click on combo box in courses detail section courses change in form header, there is not event to manage this and source for combo box is one query and source for course fields is another query. Please can you explain how this happening in more detail. Thank you so much one more time.  

Hi,


sure, that's a little bit "Access magic"...:)


No, it's of course very simple: As I said above, one form should edit only one table at the same time. As this is "tbl_Student_Course" in this case you would only have the two fields "Student_ID" and "Course_ID". The Student_ID is already provided through the link fields from the main form, and the student information is also already displayed here. So it would be enough to show the Course information with the text in the combobox. You could also create a Display column in the query of the combobox which would then not only show the name but also start or end date or whatever, separated by comma or anything else.

But this looks ugly, especially in a continous form. Moreover it would need a very wide formatted combobox to show all the text then.

The combobox already contains all other fields of the course table to show the desired information in a table style when you dropdown the field. That is enough for information what you want to select.

You could now use additional unbound fields to display the column 1,2,3 and so on of the combobox after selection. But that would need a formula (beginning with "=") or VBA - and unbound fields in a continous form always shows the same contents in all rows as the current selected row.


So another possibility is, add it to the query of the subform. If you look into the subform "Data" tab you'll find the form's query "qryFRM_Students_SF_Courses". If you open it you see that I added the "tbl_Course" to the query and the fields of this table I want to display.


But doesn't that be a conflict to "edit one table with one form" above? No. I still edit only the "tbl_Student_Course" table with this subform - but there's nothing against to collect further information from related tables which is "tbl_Course" here (as it is an n:m connection, so I fulfill the other end of the two 1:n connections).


In the subform I simply add all the fields of table "tbl_Course" in the header area so the trick here is just simple: You can display the fields of a form in a continous form either in the detail section, the header section or the footer section. If in the detail section, it will be displayed as a list of more than one row. If in header or footer, it will display only the fields of the current record, which is from the query above, which provides the fields of the "tbl_Student_Course" and also from the "tbl_Course".


But wouldn't that allow the user to edit the course also? Yes, because Access allows in some queries to edit more than one table at the same time or at least edit fields from one OR the other table if you don't change them at the same time (so editing then saving fields from TabA is possible and editing and saving fields from TabB is possible, but not editing TabA and TabB at the same time and trying to save that - but sometimes even that is possible in Access - please avoid that always!).


So to strictly follow the rule to edit only one table at the same time the solution is to simply lock all the fields coming from other tables than the table you want to edit (so all fields of tbl_Course in the header are locked). The fields are still activated (which could also be disabled) which I always prefer because with that you can at least enter the field and copy/paste information to something else like an editor or Excel or whatever. It is also always a good idea to remove them from tab order (In "Other" tab) which I didn't have done here because it was a short demo only.


No event needed, no code needed - it's just the normal change of field contents when you walk through records of a form (that was the short way to explain, but I thought you need to read a bit... ;) ).


Cheers,


Christian