Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Unanswered: Need help putting a query into a recordset

    Can someone show me how to execute an Access query into a recordset? I'm having some difficutly and can't find the answer online.

    Below is my attempt:

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("App_Daily_sls_LY_Fnl")


    This is giving me the error "Invalid operation."

    Thanks for your help!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What kind of a query is it? An action query (Delete, Update, Append, Make Table) or a SELECT.

    If it is an action query then you want to use something like

    DoCmd.OpenQuery "App_Daily_sls_LY_Fnl"

  3. #3
    Join Date
    Sep 2003
    Posts
    16

    re

    It is a SELECT query

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Can you open the query without an error? Because I don't see anything wrong with you code. When you get the error, what line does it highlight when you debug it?

  5. #5
    Join Date
    Sep 2003
    Posts
    16

    re

    Yes the query runs fine in access.


    Below is the line that throws the error:
    Set rst = dbs.OpenRecordset("App_Daily_sls_LY_Fnl")


    The query is:

    SELECT SALES_DAILY_SLS_HST.STORE_CD, tbl01Days.t01CaDt, tbl01Days_1.t01Year, GL_ACCT_PER.[SEQ#], IIf(Mid([FB_CD],3,2)=Mid(tbl01Days.t01Year,3,2),[SLS_AMT],0) AS SaTD, IIf(Mid([FB_CD],3,2)=Mid((tbl01Days.t01Year-1),3,2),[SLS_AMT],0) AS SaLD, SALES_DAILY_SLS_HST.DT, SALES_DAILY_SLS_HST.DT AS DTLY
    FROM GL_ACCT_PER, SALES_DAILY_SLS_HST INNER JOIN (tbl01Days AS tbl01Days_1 INNER JOIN tbl01Days ON tbl01Days_1.t01DaYr = tbl01Days.t01DaYr) ON SALES_DAILY_SLS_HST.DT = tbl01Days_1.t01CaDt
    WHERE (((tbl01Days.t01CaDt)<DateAdd("d",1,[Forms]![frm25Sale]![dbc25Date])) AND ((tbl01Days_1.t01Year)=(select t01year from tbl01days where t01cadt = Forms![frm25Sale]![dbc25Date])-1) AND ((SALES_DAILY_SLS_HST.DT) Between [GL_ACCT_PER].[BEG_DT] And [GL_ACCT_PER].[END_DT]) AND ((GL_ACCT_PER.FB_CD)="FY" & Mid((select t01year-1 from tbl01days where t01cadt = Forms![frm25Sale]![dbc25Date]),3,2)) AND ((tbl01Days.t01Year)=(select t01year from tbl01days where t01cadt =Forms![frm25Sale]![dbc25Date])));

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think the problem is that the default recordset type is a Dynaset, which allows you to modify the data in the query, and I bet this query is not updateable. So change your line:

    Set rst = dbs.OpenRecordset("App_Daily_sls_LY_Fnl")

    To:
    Set rst = dbs.OpenRecordset("App_Daily_sls_LY_Fnl", dbOpenSnapShot)

    And see if that works.

  7. #7
    Join Date
    Sep 2003
    Posts
    16

    re

    It's giving me a "DAO.Database Invalid Operation" error

Posting Permissions

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