Run-time error '3027' Cannot update for recordset of query (1 Viewer)

[NT100]

Hi,

I've a query (qryPIP1_TSessionsCnt) to cnt the number of occurrences of tutors' availabilities for teaching. It works fine.

"qryPIP1_TSessionsCnt"
SELECT tblTAvail_PIP.TRef, Count(tblTAvail_PIP.Session) AS SessionCnt
FROM tblTAvail_PIP
WHERE (((tblTAvail_PIP.AcademicYr)=2016) AND ((tblTAvail_PIP.PIPYr)=1))
GROUP BY tblTAvail_PIP.TRef
ORDER BY Count(tblTAvail_PIP.Session);


I've another query based on "qryPIP1_TSessionsCnt" above to get the tutors' detail fields from tblTAvail_PIP as below. It also works fine.

"qryPIP1_TLeastAvailDetails"

SELECT TA.TRef, TA.PIPYr, TA.Session, TA.StudentPerSession, TA.TotStudents, TA.AcademicYr, TA.Remarks, TA.TTID, TA.Mapped
FROM qryPIP1_TSessionsCnt AS SC INNER JOIN tblTAvail_PIP AS TA ON SC.TRef = TA.TRef
WHERE (((TA.PIPYr)=1) AND ((TA.AcademicYr)=2016))
ORDER BY SC.SessionCnt, SC.TRef;


This is a script shown below to map the teachers with the students and add their relation to a new table (tblST_Map_PIP), then update "Mapped" fields to "Yes" in both tblTAvail_PIP and tblStudent_PIP.

However, I get "Run-time error '3027' Cannot update. Database or object is read-only" and stops at the statement of "rsTRef!mapped = -1"


Private Sub cmdPIP1_STMapping_Click()

Dim rsTRef, rsStudent, rsST_Map As Recordset
Dim sStudent As String

Set db = CurrentDb

sStudent = "SELECT * FROM tblStudent_PIP WHERE (AcademicYr = 2016) AND (PIPYr = 1)"

Set rsTRef = db.OpenRecordset("qryPIP1_TLeastAvailDetails", dbOpenDynaset)
Set rsStudent = db.OpenRecordset(sStudent, dbOpenDynaset)
Set rsST_Map = db.OpenRecordset("tblST_Map_PIP", dbOpenDynaset)


rsTRef.MoveFirst
Do Until rsTRef.EOF

rsStudent.MoveFirst
Do While Not rsStudent.EOF
If rsStudent!mapped = 0 Then
If rsTRef!TTID = rsStudent!TTID Then
rsST_Map.AddNew

rsST_Map!AcademicYr = rsTRef!AcademicYr
rsST_Map!TRef = rsTRef!TRef
rsST_Map!SUID = rsStudent!SUID
rsST_Map!TTID = rsStudent!TTID

rsST_Map.Update

rsTRef.Edit
rsTRef!mapped = -1
rsTRef.Update

rsStudent.Edit
rsStudent!mapped = -1
rsStudent.Update

End If

End If
rsStudent.MoveNext
Loop
rsStudent.MoveFirst

rsTRef.MoveNext
Loop

db.Close

Set db = Nothing
Set rsTRef = Nothing
Set rsStudent = Nothing
Set rsST_Map = Nothing


End Sub

I surfed the net and there're a lot of suggestions. Do you have any advices on this.

Thank you in advance.

 

[JHB]

Below is a link to explanation why a query isn't updateable.
http://allenbrowne.com/ser-61.html

And also a link to how to declare variables.
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/declaring-variables

 

[arnelgp]

tracking back, rsTRef is a recordset from qryPIP1_TLeastAvailableDetails query.
this query is not updatable because it is based on Total query (Grouped).
but hope is not all lost.
on the said query, "mapped" field is in table tblTAvail_PIP.
so we only need to find the correct record from this table
and update its mapped field.

NOTE: i assumed TTID is STRING on the code.
if it is numeric, remove the extra single quote from the Update Query below.

change the portion of your code to this:

'...
'... rest of your code before here
'...
with rsTRef
	.MoveFirst
	While not .EOF
		rsStudent.FindFirst "TTID='" !TTID & "'"
		IF Not rsStudent.NoMatch Then
			rsST_Map.AddNew
			rsST_Map!AcademicYr = !AcademicYr
			rsST_Map!TRef = !TRef
			rsST_Map!SUID = !SUID
			rsST_Map!TTID = !TTID
			.Upate
		
			'' update tblTAvail_PiP
			'' can you see the logic?
			db.execute "update tblTAvail_PIP set mapped=-1 where " & _
					"ttid='" & !ttid & "' and PIPYr=" & !PIPYr & " and ACADEMICYr=" & !ACADEMICYr & ";"
	
			'' update student
			rsStudent.Edit
			rsStudent.mapped = -1
			rsStudent.Update
		End If
		.MoveNext
	wend
end with
rsTRef.close
rsStudent.close
rsST_Map.close

set rsTRef=Nothing
set rsStudent=nothing
set rsST_Map=nothing
set db=nothing
end sub