Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Answered: Report stored procedures and input parameters

    I have recently converted a series of ADP format files into Accdb 2013 databases.

    Some of the reports use a stored procedure as a recordsource. This has not been a problem before because I can use a passthrough with return records set to true to get the same results. However some of these reports also use the input parameter property on the report (ADP specific). This has throwna wrench in my spoke of quick conversions LOL

    Is there a property in the 2013 version that will allow me to use that property or am i going to have to create specific passthroughs for the criteria or even call the stored procedures from ADO and pass in the parameter?

    Any thoughts would be greatly appreciated.
    Dale Houston, TX

  2. Best Answer
    Posted by Sinndho

    "
    Quote Originally Posted by axsprog View Post
    If I use the querydef method andparticularly the querydef.sql method can I say something like
    querydef.sql = querydef.sql & mySTrwhereclause and after the report loads and closes only the original querydef.sql string persists?
    I do not want the where clause to besaved in the passthrough
    Unfortunately not: When you change the SQL property of a Querydef that points to an existing Query in Access, the change(s) will be recorded in the query.

    What you can do is:
    1. Instanciate the QueryDef:
    Code:
    Dim qdf as DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("MyExistingQuery")
    2. Save the current (original) SQL property of the Query:
    Code:
    Dim strSQL as String
    strSQL = qdf.SQL
    3. Change the SQL property of the Query:
    Code:
    qdf.SQL = qdf.SQL & & mySTrwhereclause
    4. Do whatever you want with the modified query (e.g. open a Report based on it):
    Code:
    DoCmd.OpenReport "MyReportBasedOntheQuery"
    5. Restore the original value of the SQL property:
    Code:
    qdf.SQL = strSQL
    
    ' Clean up
    '
    qdf.Close
    Set qdf = Nothing
    "


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    have you tried the parameter property of the querydef....

    Code:
    Public Sub btnReport_Click()
    Dim sSql As String, sWhere As String
    Dim qdf As querydef
    
    Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too
    
    
        'BUILD the query from the 'where'
    Set qdf = currentdb.querydefs(kQRY)
    qdf.paramter(1) = "6/1/15"
    qdf.paramter(2) = "6/2/15"
    set rst = qdf.openrecordset()
    
    SET qdf = nothing
    End Sub

  4. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    If I use the querydef method andparticularly the querydef.sql method can I say something like

    querydef.sql = querydef.sql & mySTrwhereclause and after the report loads and closes only the original querydef.sql string persists?

    I do not want the where clause to besaved in the passthrough
    Dale Houston, TX

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by axsprog View Post
    If I use the querydef method andparticularly the querydef.sql method can I say something like
    querydef.sql = querydef.sql & mySTrwhereclause and after the report loads and closes only the original querydef.sql string persists?
    I do not want the where clause to besaved in the passthrough
    Unfortunately not: When you change the SQL property of a Querydef that points to an existing Query in Access, the change(s) will be recorded in the query.

    What you can do is:
    1. Instanciate the QueryDef:
    Code:
    Dim qdf as DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("MyExistingQuery")
    2. Save the current (original) SQL property of the Query:
    Code:
    Dim strSQL as String
    strSQL = qdf.SQL
    3. Change the SQL property of the Query:
    Code:
    qdf.SQL = qdf.SQL & & mySTrwhereclause
    4. Do whatever you want with the modified query (e.g. open a Report based on it):
    Code:
    DoCmd.OpenReport "MyReportBasedOntheQuery"
    5. Restore the original value of the SQL property:
    Code:
    qdf.SQL = strSQL
    
    ' Clean up
    '
    qdf.Close
    Set qdf = Nothing
    Have a nice day!

Posting Permissions

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