Cannot update object? 1

[beckyr51]

Another one for you geniuses,

Im trying to assign students to tutors until a tutors chamber is full. I have tried the code below but it wont let me update the space a tutor has left in their chamber. Its says "cannot update: database or object is read only". Does anyone know how i can get around this please??


Set rs = db.OpenRecordset("Select * from [tblWorking]")
Set rs2 = db.OpenRecordset("Select [tblTutor].TU_CODE, [tblTutor].TU_CHAMBER_SIZE, [tblTutor].TU_DP_NO, [TU_CHAMBER_SIZE]-Count([STU_TU_CODE]) AS space from ([tblTutor] inner join [tblStudents] on [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) group by tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE,[tblTutor].TU_DP_NO, tblStudents.STU_TU_CODE having [tblTutor].TU_DP_NO = 57 ")

Do While Not rs.EOF
If IsNull(rs("STU_TU_CODE")) Then

If rs2("space") > 0 Then
rs.Edit
rs!STU_TU_CODE = rs2("TU_CODE")
rs.Update
rs2("space") = (rs2("space") - 1)
rs.MoveNext

Else
rs2.MoveNext
End If
End If
Loop

 

[gwinn7]

First, try specifying the recordset type...

Example...

Set rs = db.OpenRecordset("Select * from [tblWorking]", dbopendynaset)

Then, check your permissions on the table.

Gary
gwinn7

 

[beckyr51]

i tried inserting dbopendynaset but i get the same error?

 

[genomon]

Make sure your table has a primary key defined.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[pbaldy]

As I pointed out elsewhere you posted the same question, space is a calculated field and thus not updateable. Also, rs2 contains a GROUP BY clause, and therefore is not updateable. I suggested using a variable to track the value.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[beckyr51]

Im sorry but im still really confused, ive tried going about it a slighly different way by selecting the first tutor with the right requirements and then assigning the student to them (as below)

strsql2 = "Select tblWorking.STU_ID, tblWorking.STU_FORENAME, tblWorking.STU_SURNAME, tblWorking.STU_COURSE_CODE, tblWorking.STU_STANDING From tblWorking "
Set rs = db.OpenRecordset(strsql2)

With rs
Do While Not .EOF

strsql3 = "SELECT TOP 1 tblTutor.TU_CODE " _
& "FROM tblTutor LEFT JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE " _
& "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblStudents.STU_TU_CODE " _
& "HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND (tblTutor.TU_DP_NO = 57) )"

Set rs2 = db.OpenRecordset(strsql3)


.Edit
rs!STU_TU_CODE = rs2
.Update

.MoveNext

Loop

End With

But the line rs!STU_TU_CODE = rs2 is highlighted and an error saying "Item not found in this collection"

 

[pbaldy]

STU_TU_CODE is not one of the fields in that recordset. You can't update a field that's not in the recordset.

Paul
MS Access MVP 2007/2008

 

[beckyr51]

Sorry that was a silly mistake, now all the STU_TU_CODEs are the first tutor. This is because im not yet moving those allocated into the permanent students table. Is there a way of saying, if STU_TU_CODE contains something eg:

strsql = "Insert into [tblStudents] ( STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_STANDING, STU_TU_CODE ) " _
& "Select [tblWorking].STU_ID, [tblWorking].STU_FORENAME, [tblWorking].STU_SURNAME, [tblWorking].STU_COURSE_CODE, [tblWorking].STU_STANDING, [tblWorking].STU_TU_CODE " _
& "From [tblWorking] Where tblWorking.STU_TU_CODE = ANYTHING "

Im putting this in after .Update but before .Movenext

 

[pbaldy]

How about

Where tblWorking.STU_TU_CODE Is Not Null


Paul
MS Access MVP 2007/2008

 

[beckyr51]

Sorry i had tried = Is Not Null

Everything is working great now thank you very much, i really appreciate it