Updatable recordset

[beckyr]

Hi there. Is it true that you cannot update a recordset if it has a group by clause? Im trying to make sure that a tutor has space (TU_SPACE) and then decrease their space by one when a student is assigned to them but the following results in "cannot update database or object is read only" (highlighting rs2.edit)

strsql2 = "Select * From tblWorking "
    Set rs = db.OpenRecordset(strsql2)
    
    
    With rs
        Do While Not .EOF   'Loop through the nursing students
        
        course = rs("STU_COURSE_CODE") 'Get the students course
        fac = DLookup("[CS_FAC_NO]", "tblCourse", "CS_CODE = '" & course & "'")
        
        
        'Selects the first tutor in the nursing department with free space
        strsql3 = "SELECT TOP 1 tblTutor.TU_CODE " _
                & "FROM tblTutor " _
                & "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblTutor.TU_SPACE " _
                & "HAVING ([TU_CHAMBER_SIZE] > tblTutor.TU_SPACE) AND (tblTutor.TU_DP_NO = 57) "
        
        Set rs2 = db.OpenRecordset(strsql3)
                
        If rs2.RecordCount <> 0 Then  'Assigns the student to this tutor
            rs.Edit
            rs!STU_TU_CODE = rs2!TU_CODE
            rs.Update
            rs2.Edit
            rs2!TU_SPACE = rs2!TU_SPACE - 1
            rs2.Update
        End If

I then tried making a variable out of space and doing it that way but it says "Item not found in this collection" Also if i did it this way how do i put the value of space back in the table for assigning the next student?

Set rs2 = db.OpenRecordset(strsql3)
        freespace = rs2("TU_SPACE")
                If rs2.RecordCount <> 0 Then  'Assigns the student to this tutor
            rs.Edit
            rs!STU_TU_CODE = rs2!TU_CODE
            rs.Update
            freespace = freespace - 1
        End If

Has anyone any suggestions please!

 

[Rabbie]

See this link for info on where a query is read only.

 

[gemma-the-husky]

when you say "space" do yuo mean that you are trying to establish how many students, a given lecturer has?

if so - do NOT try to store the value of his spaces

instead store his maximum capacity (eg 12). then do a totals query to find out how many students he currently has. His space is the difference between the two.

bascially, you should NEVER try to store anything you can calculate (there are rare exceptions when it makes sense, but i dont think this is one of them)

 

[beckyr]

The original system was designed so that it does store students and calculates space from this as you suggested Gemma but now we've come to a roadblock where it might not be possible to store student information on the system so i may have to go about it this way by initially saying how much space a Tutor has left and working from there

 

[gemma-the-husky]

i see - are there data protection issuies then?

in this bit of code, the top line might be wrong

try
freespace = rs2![TU_SPACE]

then
If rs2.RecordCount <> 0 Then 'Assigns the student to this tutor
rs.Edit
rs!STU_TU_CODE = rs2!TU_CODE
rs.Update

'but now to save the freespace, you probably need an update statement logically like this, rather than via the group by record set

update tutortable set tutorspace = " & freespace & " where tutorid = " & tutorid


freespace = freespace - 1

 

[beckyr]

Hi Gemma, thanks for your reply. Someone suggested the way below before i read your reply. It works but an error comes up when a tutor runs out of space saying the values are prohibited due to the validation rule >0 in the tblSpace. Is there a way to ensure in my code that space doesnt go below 0 and if it does go to the next tutor?

strsql3 = "SELECT TOP 1 tblTutor.TU_CODE " _
                & "FROM tblTutor LEFT JOIN [tblSpace] ON [tblTutor].TU_CODE = [tblSpace].TU_CODE  " _
                & "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_DP_NO, tblSpace.TU_CODE, tblSpace.TU_SPACE " _
                & "HAVING ([TU_CHAMBER_SIZE] > tblSpace.TU_SPACE) AND (tblTutor.TU_DP_NO = 57) "
        
        Set rs2 = db.OpenRecordset(strsql3)
        code = rs2![TU_CODE]
        Set rs4 = db.OpenRecordset("Select TU_SPACE from tblSpace where TU_CODE = " & "'" & code & "'")
       
        If rs2.RecordCount <> 0 Then  'Assigns the student to this tutor
            rs.Edit
            rs!STU_TU_CODE = rs2!TU_CODE
            rs.Update
            rs4.Edit
            rs4!TU_SPACE = rs4!TU_SPACE - 1
            rs4.Update
        End If

 

[gemma-the-husky]

"HAVING ([TU_CHAMBER_SIZE] > tblSpace.TU_SPACE) AND (tblTutor.TU_DP_NO = 57) "

i dont quite understand the "having" clause that selects the tutor

--------
i am not sure what the dp_no = 57 means, but ignore that anyway (department no?)

but the other clause .... here you are selecting tutors with chamber size greater than the space, - this will always be true if space is currently 0, and in general will presumably be true most of the time - so this will ALWAYS select the same tutor wont it (and it will NEVER select a new tutor, where the capacity starts equal to the spaces)

if you want to fill up tutors in order then adding another bit to it

and tblSpace.TU_SPACE > 0 will ensure you only pick tutors with available space

 

[beckyr]

dp_no is a department number. Thanks for your help Gemma that works now, should have figured that last bit out for myself-think i need some sleep!! Thanks again

 

[gemma-the-husky]

on reflection, and just thinking about it, why is there a separate table for spaces (tblspace). Why not just have the available space in the tbltutor table.

this looks like a one to one join

 

[beckyr]

I made a separate table for space cause it was the only way i could figure out how to update the recordset containing space