Solved Teachers database RELATIONSHIPS (1 Viewer)

GPGeorge

Grover Park George
Local time
Today, 14:34
Joined
Nov 25, 2004
Messages
1,776
"The use of subdatasheets is the best to enforce the right relationships." No, not really. They are an artifact created by enforcing referential integrity, and they are part of the interface, not the database engine, to boot.

Pay heed to Colin's advise.
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
"The use of subdatasheets is the best to enforce the right relationships." No, not really. They are an artifact created by enforcing referential integrity, and they are part of the interface, not the database engine, to boot.

Pay heed to Colin's advise.
But it works perfectly for me so I guess I will stick to @arnelgp table structure but thanks anyway
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
"The use of subdatasheets is the best to enforce the right relationships." No, not really. They are an artifact created by enforcing referential integrity, and they are part of the interface, not the database engine, to boot.

Pay heed to Colin's advise.
By the way what is the other alternatives to creating forms and subforms or rather subdatasheets?
 

GPGeorge

Grover Park George
Local time
Today, 14:34
Joined
Nov 25, 2004
Messages
1,776
Ah, perhaps there's a bit of confusion over the difference between the term "form and subform" and the term "subdatasheet".

Also, Referential Integrity is enforced at the table level. The interface (i.e. forms and subforms) can be designed to facilitate it, but they do not enforce it because a user can, at least in theory, bypass your interface.

So, a form is an interface object through which users interact with data in tables. Forms can be bound to the table or to a query based on a table. In some situations, of course, you can embed one form inside another, the "subform". Typically, the main form is this particular interface design is bound to the table on the table on the "one" side of a one-to-many relationship; the sub form is bound to table on the "many" side of that one-to-many relationship. The use of the Parent Child Linking field property in these interface designs facilitates the insertion of the appropriate Foreign Key in the many side table. However, that can not replace the actual table-level enforcement of the relationship. It merely assists you in managing the values.

On the other hand, Access -- in keeping with its penchant for blurring the line between tables and interface elements -- has what are called "subdatasheets" and that's what Colin was talking about.

Here's a screenshot, just to be clear. I normally remove these things immediately upon taking on a new relational database application for the reasons Colin stated. Note that these things are identifiable by the little +/- icon, as highlighted here. What that means is that the ENTIRE table on the many side is dragged into the current recordset along with its parent, an unnecessary burden on resources on your computer. Plus, to me it's much more confusing.

1647611939863.png


Like a lot of things Microsoft has done over the years, it was intended to make it "easy" for inexperienced people to use Access. They munged two different kinds of things together--the "interface" appearance of the subdatasheet and the tables. If you don't find the overhead problematic, they are not so bad, I suppose. But they are ultimately useless, IMO.
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
Oh ok. How do you remove them yet it was automatically created by access when you enforce referential integrity? I guess there is alot to learn but I am happy I am in the right forum. Thanks for your explanations. I appreciate.
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
Ah, perhaps there's a bit of confusion over the difference between the term "form and subform" and the term "subdatasheet".

Also, Referential Integrity is enforced at the table level. The interface (i.e. forms and subforms) can be designed to facilitate it, but they do not enforce it because a user can, at least in theory, bypass your interface.

So, a form is an interface object through which users interact with data in tables. Forms can be bound to the table or to a query based on a table. In some situations, of course, you can embed one form inside another, the "subform". Typically, the main form is this particular interface design is bound to the table on the table on the "one" side of a one-to-many relationship; the sub form is bound to table on the "many" side of that one-to-many relationship. The use of the Parent Child Linking field property in these interface designs facilitates the insertion of the appropriate Foreign Key in the many side table. However, that can not replace the actual table-level enforcement of the relationship. It merely assists you in managing the values.

On the other hand, Access -- in keeping with its penchant for blurring the line between tables and interface elements -- has what are called "subdatasheets" and that's what Colin was talking about.

Here's a screenshot, just to be clear. I normally remove these things immediately upon taking on a new relational database application for the reasons Colin stated. Note that these things are identifiable by the little +/- icon, as highlighted here. What that means is that the ENTIRE table on the many side is dragged into the current recordset along with its parent, an unnecessary burden on resources on your computer. Plus, to me it's much more confusing.

View attachment 99243

Like a lot of things Microsoft has done over the years, it was intended to make it "easy" for inexperienced people to use Access. They munged two different kinds of things together--the "interface" appearance of the subdatasheet and the tables. If you don't find the overhead problematic, they are not so bad, I suppose. But they are ultimately useless, IMO.
Oh I get it. I will change in table property sheet from auto to none. Thanks
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
another demo, i made "new" tables to hold some informations.
@arnelgp I guess I am not asking too much. Please kindly assist me with vba code for this database such that the moment you select the name of the teacher
(1) the Subjects which the teacher teach are automatically shown in the teacherssubjectroom only and
(2) the Subjects are shown and the Students are shown.

So I believe there will be two forms with the two vba codes.

Thanks in advance @arnelgp
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:34
Joined
May 7, 2009
Messages
19,169
choose 2_TeacherAllSchedule form.
the form is minimal.
 

Attachments

  • Teachers.accdb
    2.5 MB · Views: 96

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
choose 2_TeacherAllSchedule form.
the form is minimal.
@arnelgp how do you capture the error. If by mistake, the user writes the year manually in 2_TeacherAllSchedule it produces the error:
Run-time error '3464'
Data type mismatch in criteria expression.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:34
Joined
May 7, 2009
Messages
19,169
i cannot produced the error, i have both combo's set Limit To List to Yes.
meaning if you type invalid date or teacher, the combo will dropdown
and show you valid list to choose from.

close the db and try again. make sure to put the db in Trusted location.
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
Ok thanks
i cannot produced the error, i have both combo's set Limit To List to Yes.
meaning if you type invalid date or teacher, the combo will dropdown
and show you valid list to choose from.

close the db and try again. make sure to put the db in Trusted location.
Ok thanks
 

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
choose 2_TeacherAllSchedule form.
the form is minimal.
@arnelgp I want to use 1_TeacherSchedule. Is it possible that the moment you select the teacher whose records have not been entered the teacherssubjectroom subform becomes blank because the records for the teacher have not been entered???? Records to be shown when a teacher whose records have been entered to be shown. Ignore qryTeachersStudents subform. Thanks in advance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:34
Joined
May 7, 2009
Messages
19,169
i added a "button" to the Right of the Teacher combobox.
you click on the button and it will show you list of teachers
not yet entered for this year.
doub-click on the record to select and put on the 1_TeacherSchedule.
 

Attachments

  • Teachers.accdb
    2.6 MB · Views: 89

ElizabethTaylor

New member
Local time
Tomorrow, 00:34
Joined
Mar 15, 2022
Messages
28
@arnelgp thanks so much for your help. I am trying to recreate the Report from teachers schedule to be like the one in this attached excel spreadsheet. It has taken the whole night and the whole day and I am defeated. Kindly assist me. Kindly look at this excel spreadsheet
 

Attachments

  • TEACHERS SCHEDULE REPORT.zip
    6.4 KB · Views: 87

Users who are viewing this thread

Top Bottom