3

I'm sure that this is a basic question but I'm struggling to get to grips with Access.

In my case I have two tables: Student and Subject, linked by a junction table, StudentSubject. Student and Subject have a many to many relationship, as one student can study multiple subjects, and one subject can be studied by many students.

What I don't understand specifically is how to input data so that each student can study more than one subject. I've created a Form with a Subform that looks like this:

enter image description here

When I try to input anything into the "SubjectName" field, it says "Field cannot be updated". If I press "Okay" the message goes away and I can type something in the field, but as soon as I try to put something else in the record below for "Subject", the same error comes up. Then when I press the arrow to go to the Next Record on the Form, and save the form, nothing has changed in any of the tables.

enter image description here

Here are the three tables and their relationships:

enter image description here

I'm not too sure what I'm doing wrong here, but the end goal is that I can input a student and also input all of the subjects that they are doing, so that each student's individual list of subjects is stored alongside their name. I don't know where the "Field cannot be updated" error is coming from.

Could anyone advise?

Lou
  • 2,200
  • 2
  • 33
  • 66
  • This question is too broad. Data entry with many-to-many relationships and junction tables generally is pretty hard and a user-friendly UI can't really be developed without using VBA. Personally, I tend to use a data sheet with all possible entries, and a check box to indicate if there's a relationship between the entries that can be toggled using VBA. A common alternative is a list box with possible entries where entries can be checked or unchecked, but that also requires VBA. – Erik A Apr 25 '19 at 13:10

2 Answers2

2

You need 3 forms:

  • One form for students,
  • One form for subjects
  • One form for the Student-Subject table

The Student-Subject form will be a sub-form in both of the other forms. This form can have combo-boxes for the student and subject. For example, here's what editing a student might look like:

Student form with subject sub-form

This way, you can easily add new subjects a student is taking (and similarly, add new students that are taking a subject).

Note that both the Subject and Student columns are being shown. What I've done in the past is have a bit of VBA to hide the appropriate column based on what the parent form is:

Option Explicit

' note that this code goes in the Student-Subject form.
Private Sub Form_Load()

If Me.HasParent Then
    Me.cboStudentID.ColumnHidden = (Me.Parent.Name = "frmStudent")
    Me.cboSubjectID.ColumnHidden = (Me.Parent.Name = "frmSubject")
End If

End Sub
'''''''''''''''''''''''''''''''''''''''''''
Public Function HasParent() As Boolean
    On Error GoTo err_handle

    HasParent = Not Me.Parent Is Nothing
    Exit Function

err_handle:
    HasParent = False
    Exit Function

End Function

This way, the appropriate column is hidden based on whether you're editing a student (and the Student column will be hidden) or editing a subject (and the Subject column will be hidden).

Zack
  • 2,220
  • 1
  • 8
  • 12
0

Typically you have two different views on such a multi to multi relation. One is you are in the student's view and the other is if you are in the subject's view. In the case of the student's view you must create a form to edit the student's table and you have to add a subform to edit the detail data table, i.e. StudentSubject. This detail table has to be synchronized with your StudentID from the main form. Usually this is already done by the wizard. But please be aware that you have only the ids in the detail table. To display a readable information you need to change the text boxes to combo boxes and to bind the combo boxes to the underlying tables. Because you have only numbers in the detail table StudentSubject you cannot input text directly into this box. If the SubjectName has not been registered in the table Subject you have to add the new entry before you can select this. To dynamically add a new text entry you may use the "Not in List" event to handle this.

xfh2002
  • 56
  • 3