Solved

make a dynaset querydef updateable

Posted on 1998-09-22
3
307 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

756 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