Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    37

    Unanswered: Cannot update object

    Hello,

    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

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Space is a calculated field so it would not be updateable (actually the whole rs2 would not be updateable, since it contains a GROUP BY clause). One way around it would be to set a variable to the initial value of space and increment it down every time you assign someone.
    Paul

  3. #3
    Join Date
    Jan 2008
    Posts
    37
    Thats a good idea and works fine for the first tutor but i am unsure as to how i go about assigning the freespace variable to the second member of the rs2 dataset. It highlights rs2.MoveNext and "says no current record", i know this makes sense cause im not doing anything with rs2 in the loop but how do i go about doing this?

    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] 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 [tblTutor].TU_DP_NO = 57 ", dbOpenDynaset)
    freespace = rs2("space")


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

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

    Else
    rs2.MoveNext
    End If
    End If
    Loop

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What do the 2 recordsets represent? A detailed description of what you're trying to accomplish may help.
    Paul

  5. #5
    Join Date
    Jan 2008
    Posts
    37
    rs: represents all incoming students doing a certain course, all of whom need to be allocated tutors
    rs2: represents all possible tutors for these students (all within department number 57), its also gets these tutors free space for new students by subtracting the number of students they already have from their chamber size

    I am then trying to go through the students allocating them to tutors as long as that tutor still has free space. If that tutor no longer has free space, move to the next tutor who does and so on

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It sounds like you may be running out of tutors before you run out of students. Is that possible? If not, can you post a sample db?
    Paul

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i have a couple of scheduling/allocating apps that do pretty much what you are doing. since this stuff comes up from time to time i put a very simple demo in the 'Bank
    the point of the demo is to avoid looping through zillions of recordsets and arrays.

    since i had to base the demo on something, i used a scenario very close to the one you are playing with.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jan 2008
    Posts
    37
    Thank you very much izyrider for that demo, very similar scenario expect the tutors have students already in their chambers apart from the incoming ones. It gave me an idea of how to go about my problem, using some code similar to yours. Im trying the below but it's highlighting count and saying sub or function not defined - any ideas please?

    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, dbOpenSnapshot)

    With rs
    Do While Not .EOF

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

    Debug.Print strsql3

    db.Execute strsql3

    .MoveNext

    Loop

    End With

  9. #9
    Join Date
    Jan 2008
    Posts
    37
    Sorry also meant to mention when i structure the SELECT line like this:

    "SELECT TOP 1 tblTutor.TU_CODE, [TU_CHAMBER_SIZE] - (Count([STU_TU_CODE]) AS space " _

    i can an error "number of query values and destination fields are not the same. Im presuming this is because im not inserting space annywhere but how do i go about creating space without inserting it?

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry for the delayed reply - was away.
    is this still a problem?

    you have a missing )
    (Count([STU_TU_CODE])) AS space

    and a funny situation at the end with
    AND (tblTutor.TU_DP_NO = 57) )")
    ...probably an attempt to rectify the parenthesis count

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jan 2008
    Posts
    37
    sorry for the late reply, i did solve this problem by going about it a slightly different way

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •