Solved

make a dynaset querydef updateable

Posted on 1998-09-22
3
310 Views
Last Modified: 2010-08-05
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
Comment
Question by:SE081398
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Author Comment

by:SE081398
ID: 1963216
Edited text of question
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 30 total points
ID: 1963217
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
 
LVL 3

Author Comment

by:SE081398
ID: 1963218
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question