Results 1 to 8 of 8
  1. #1
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21

    Pass a parameter to a pass through query using a form

    Hi All,

    I have made very good progress in creating a report using a passthrough query as the datasource. Unfortunately to change the parameter for the report I need to go into the passthrough query and manually change a date to get updated values.

    Does anyone have any simple code that I could use to pass a parameter to the passthrough query from an access form?

    Thanks



    IanW

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please step back and describe to readers what you are trying to do in plain English.
    Why is there a pass through query? You haven't told us much of your environment or requirement.

  3. #3
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21
    ok. My apologies. I am using MicroSoft Access 2010. I have written a SQL passthrough query that accesses an Oracle database set of tables through an ODBC connection. The query contains nested select statements. The resulting query returns a summarized data set that I use to populate a report using the Access database report object.

    At this point, in order to change the date parameter in the nested statement I have to edit the passthrough query and rerun.
    I would like to be able to pass a date parameter to one of the nested select statements contained in the passthrough query when I enter the date parameter into a form.

    Does that make more sense?

    Thanks

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps, it's been a while since I worked with Oracle and passthrough queries. However, since the passthrough query has to be in Oracle specific SQL, I would create a string variable in Access that houses the passthrough query string in Oracle dialect. If I needed a parameter, I would allow for it in Access. Get the parameter value and adjust the sql string that will be sent to Oracle and then execute the pass through. Receive the returned data and pass that to the report.

    Does that make sense?

  5. #5
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21
    Yes it does. Thank you.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21
    Orange, I think this is what you mean. I will open the passthrough query in design mode and replace the date I have with the updated date I need and then execute the query. I am in the infancy of VBA code and I am taking small steps at a time so I really do appreciate your help on this.

    At this point I have created some vba code to open the query in edit mode and then to replace the date in the query. The query does open but I can't seem to get it to edit the open query in design mode. Can you give me some insight as to where I am going wrong? See code below. Thanks

    Thanks

    Code:
    Sub ReplaceText()
    Dim strQuery As String
    Dim strSQL As String
    strQuery = "qryMainACCUMTest"
    DoCmd.OpenQuery strQuery, acViewDesign, acEdit
    strSQL = Replace(strSQL, "28-FEB-2015", "31-JAN-2015", 1)
    
    End Sub

  8. #8
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21
    For those interested. He is what I figured out. The parameters you want changed need a constant. e.g [PASS_PARAMETER] . The code below will find query("qryMainACCUMTest")
    and replace the embeded text [PASS_PARAMETER] with the date you require.

    Code:
    Public Sub Mytest()
    Dim qdf As QueryDef
    Dim Posit As String
    Dim dbs As Database
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("qryMainACCUMTest")
        
            qdf.SQL = Replace(qdf.SQL, "[PASS_PARAMETER]", "'28-FEB-2015'")
        End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass parameter to a VBA Module
    By testing2Three in forum Modules
    Replies: 4
    Last Post: 11-13-2014, 02:33 PM
  2. Replies: 13
    Last Post: 05-14-2013, 06:01 PM
  3. Replies: 2
    Last Post: 02-10-2011, 10:44 AM
  4. Pass list as parameter to in operator
    By bliever in forum Queries
    Replies: 5
    Last Post: 11-11-2009, 03:15 AM
  5. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums