Im doing a coursework on ms access on School Management System. I have a table 'tblSubject' containing fields: 'Subject ID' and 'Subject Name' . A student has the right only to choose a maximum of 8 subjects, that is , the field 'Subject ID' must have only 8 records/entries.
I believe you mean you want to limit the data entry into the table to contain only 8 rows per student. You would need to test for the count of rows per student in the form you are using for data entry. Place the code in the forms Before Update event.
If Me.NewRecord And Dcount("*","tblSubject","[Subject ID]=" & Me.Subject_ID) = 8 Then
Msgbox "This student already has 8 subjects"
Cancel = 1 'cancel the update
Me.undo 'clear all entries
The only problem here is if you are editing an existing record such that it creates a 9th subject for a student. If you have a Primary Key ID for the table then you could also verify that situation.