Query not updatable...

[mshakeelattari]

I have the following tables:

1.Students

ID     (pk)
Urdu Name
English Name
....
....
 

2. Classes

ClassID   (pk)
Grade
Section
....
....
 

3.Students and Classes

StudentID (related to Students.ID)     | PK
ClassID   (related to Classes.ClassID) | PK
RollNo
....
....
 

4. Student Attendance

ID       (PK)
Student  (StudentID)
Attendance Date
Status
 

Now the issue is that when I make a select query from the tables "Students and Classes" and "Student Attendance" related by StudentID and Student, the resulting query is not updatable. The Query is:

SELECT 
      [Students and Classes].ClassID
    , [Students and Classes].StudentID
    , [Students and Classes].RollNo
    , [Student Attendance].[Attendance Date]
    , [Student Attendance].Status
FROM 
      [Students and Classes] 
      LEFT JOIN 
      [Student Attendance]
      ON 
      [Students and Classes].StudentID = [Student Attendance].Student;
 

The record is not updatable.
Waiting for any help.

[jforbes]

It sounds about right that your Recordset is not updatable. This may help you understand a little better of what is acceptable and what is not acceptable for the basis of a Form when you want it to be updatable: Why is my query read-only?

Another way to put it is using One-To-Many Joins makes your Recordset Readonly.

Typically in a situation like yours, where you want to update records in multiple tables (or display information from multiple tables), you create a Main/Subform setup where each Form is based on a single table.

[mshakeelattari]

I have a form that Shows ClassID from table "Classes", with a subform showing StudentID's from "Students and Classes" table. The other form is required to show "Students, Attendance Date, Status" from the "Student Attendance" table for the studentID which have ClassID equal to the ClassID on the former form and the latter should be updatable. Is there anysolution please?

[jforbes]

I don't currently use Access as my backend database, so I'm a bit rusty on Access' capabilities regarding this. It doesn't quite play by the same rules as SQL Server, and there may be a way to get it updatable by Defining the Relationships of all the tables involved. But I can't guarantee that.

In a case like this, I typically create the SubForm based on the base table (non-query) and then either use a Filter to narrow it down to the Records specific to the MainForm, or supply the an entire RecordSource Select Statement. This is usually updated/assigned to the SubForm in the MainForm's OnCurrent Event. Also in the MainForm's OnCurrent Event, any default values (like Foreign Keys) are defined for the SubForm.

This approach gets a little tricky in the case of a LinkTable, which is what [Students and Classes] happens to be. Especially when Adding a new link, like enrolling a Student in a Class. To make this happen is typically more complex than navigating to the NewRecord and typing in some stuff. It usually takes navigation to one Record (like Classes) and then clicking a button to look up the second Record (like Student) and then clicking another button which takes the two pieces of information and formulates an Insert Statement against the LinkTable and then executes it.

[Seth Schrock]

See the following link as it should help you: When can I update data from a query?. It has some suggestions on how to make your query updatable.

[mshakeelattari]

I am still not understanding what to do...

[Seth Schrock]

I think that I understand now what is happening. You need to change your table design for [Students and Classes] to have a single field primary key field instead of a composite key as you have now. You would then change [Student Attendance] to link to this new field instead of the student ID.

[mshakeelattari]

This is a good idea. I appreciate for this idea. But then, is there a way to preven any duplicate stdent for on class or a duplicate class fo one student in the students and classes table?

[Seth Schrock]

It can be done via a multi-field unique index constraint, but personally I would recommend doing it via code (like a DCount() function to see if a record already exists for a particular student and class) as that then allows me to provide a custom error message instead of getting one that Access generates that users won't be able to understand. On your form's BeforeInsert event, just run the test and if a record already exists, then set Cancel = True to stop in insertion of a new record.

[NeoPa]

I'd recommend doing it in both if you want a code solution.

It should always be set up to have a unique compound (multi-field) index.

Code can be got around. Tables can be accessed directly. Relying solely on the code is not a wise approach.