Solved

make a dynaset querydef updateable

Posted on 1998-09-22
3
297 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
  • 2
3 Comments
 
LVL 3

Author Comment

by:SE081398
Comment Utility
Edited text of question
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 30 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now