Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    74

    Unanswered: Problem with Reports

    I have an AC2003 database that contains a number of defined queries. Those queries are chosen programmatically to fill the .RecordSource of a Report. (Note: The Report is opened utilizing the [WhereCondition] of the OpenReport call)

    If the source query ends up empty, I get a macro "Action Failed" error.

    So I am trying to get rid of the error. I think I have two options:

    1) Detect the macro "Action Failed" error: This is less desirable, unless I can detect that the macro error occured only because the supporting query was empty, and not due to some other failure.

    2) Get the SQL for the selected query: If I can derive the SQL for the selected query, I can modify the WHERE clause so that the final query would match what is feeding the report. This may be stoopidly simple, but I haven't found a recordset property that contains the SQL that feeds the recordset.

    Any ideas or other options?
    AzJazz

    "I'm afraid the holodeck will be society's last invention." - Scott Adams

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    seems a strange way to do it - something in your code already 'knows' which query is going to be used
    but (at least in DAO) you can backtrack to the SQL from the recordset itself - how depends on how you generated the recordset.

    DAO rsets have a 'Name' property.

    if the rset was generated from a saved query e.g.
    set recs = currentdb.openrecordset("mySavedQuery", dbopensnapshot)
    then
    recs.name
    is the name of the saved query.
    armed with that info you can get the querydef.SQL property.

    also, tho not your case:
    if the rset was generated from inline SQL e.g.
    set recs = currentdb.openrecordset("SELECT * FROM tblHere", dbopensnapshot)
    or
    strSQL = "SELECT * FROM tblHere"
    set recs = currentdb.openrecordset(strSQL, dbopensnapshot)

    ...then
    recs.name
    is the query string

    i don't use filters, but
    recs.filter
    might be helpful

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2007
    Posts
    74
    izy - Thanks ... I actually had already tried your first recommendation before submitting my post, but I had a problem with the querydef part (I haven't fooled with querydefs before).

    Not knowing what to do, I experimented by trying to do a:

    Set db = CurrentDb
    Set myQdf = db.CreateQueryDef("", strMySavedQueryName)
    mySql = qdf.SQL


    However, since the value for strMySavedQueryName was something like "qryParentToChild", I got a Run-Time Error #3129: Invalid SQL Statement. The .CreateQueryDef was looking for a SQL "SELECT" style query instead of the name of a stored query.

    Searching on the internet for more info on using QueryDefs in the manner I wanted didn't get me anywhere.

    So if you (or somebody else) can provide a little extra info on using QueryDefs, it would probably fix my problem.
    Last edited by azjazz; 11-24-08 at 14:54.
    AzJazz

    "I'm afraid the holodeck will be society's last invention." - Scott Adams

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    to attack a saved query:

    dim qdef as dao.querydef
    set qdef = currentdb.querydefs("mySavedQuery")
    mySQL = qdef.SQL

    izy
    Last edited by izyrider; 11-25-08 at 03:15.
    currently using SS 2008R2

  5. #5
    Join Date
    May 2007
    Posts
    74
    Thanks, izy - That worked!
    AzJazz

    "I'm afraid the holodeck will be society's last invention." - Scott Adams

  6. #6
    Join Date
    May 2007
    Posts
    74
    Hi!

    I just found the real solution to my problem. I just discovered the "OnNoData" property for the reports. That is what I needed in the first place (though I didn't know what to ask for).

    I knew there had to be a simpler solution than the one I was trying to implement ...

    Thanks again ...
    AzJazz

    "I'm afraid the holodeck will be society's last invention." - Scott Adams

Posting Permissions

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