Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

make a dynaset querydef updateable

I need to update a field that is in a querydef.  I have 2 queries.
query 2 is based on query 1. The problem is an error occurs "recordset is read only"
I have looked up the dynaset updateable property and is says that it can be updated.
the first query can be edited but the second can't.  how can I get this second query to be updateable?.  Are compounded queries read only??? is it because it is based on "dates" and the update property can only update the field of its original table.?
 
 icdpSQL = "SELECT tblSlideDetail.ToSchedule, tblOrders.ProjectName,..... " & " between #" &       DATE1 & "# and #" & DATE2 & "#))ORDER BY tblSlideDetail.CompletionDate;"

iccycleSQL = "SELECT [Dates].ProjectName, count([Dates].ItemID) as countofitemid,             [Dates].Available, [Dates].ToCycle FROM [Dates] GROUP BY [Dates].ProjectName,             [Dates].ItemID, [Dates].Available, [Dates].ToCycle;"

    Set icdpQD = dbs.CreateQueryDef("Dates", icdpSQL)
        With icdpQD
            Set icdpRS = icdpQD.OpenRecordset(dbOpenDynaset)
        End With
    Set iccycleQD = dbs.CreateQueryDef("Cycles", iccycleSQL)
        With iccycleQD
            Set iccycleRS = iccycleQD.OpenRecordset(dbOpenDynaset)
        End With
       
        With iccycleRS
           iccycleRS.MoveFirst
            Do Until iccycleRS.EOF
                If iccycleRS!countofitemid > (iccycleRS!Available * 2) Then
                    itemover = iccycleRS!countofitemid - (iccycleRS!Available * 2)
                    totaldays = totaldays + itemover
                    increasedays = totaldays / numberofdays
                   
                End If
             
               If iccycleRS!countofitemid > iccycleRS!Available Then
             '      this will set the cycle field for the mould that needs cycling
                   .Edit
                   !ToCycle.Value = True
                   .Update
                   
                End If
                .MoveNext
            Loop          
0
SE081398
Asked:
SE081398
  • 2
1 Solution
 
SE081398Author Commented:
Edited text of question
0
 
Victor SpiridonovCommented:
In your second query you have GROUP BY clause,this makes your query not updatable.Query can't be updated if it contains aggregate functions.
0
 
SE081398Author Commented:
Thanks.  I've now had to create a 3rd recordset that is primarily the same as the first recordset icdpRS, except a few less fields and a where clause that includes a unique identifier of each record in the icdpRS.  in short, a modification was made and it works.
thanks.  the code would have been more efficient if I could have used the agregate query,
but oh well it works.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now