Results 1 to 4 of 4

Thread: passthru qry

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: passthru qry

    Hi all, using access 2003 front end sql server 2008 back end. When opening a form with a subform based on a pass thru query, I am getting the following error:

    You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport

    I am using the pass thru because subforms based on regular access query is slow since upsizing to sql server and thought pass thru would be faster. I've search the internet but haven't come accross any solutions. Wondering if anyone has a solution that I may try to get this running.
    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Assign the data source of the subform dynamically:
    Code:
    Private Function OpenSubFormRecordSet()
    
        Dim qdf As DAO.QueryDef
        
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
            .SQL = "SELECT * FROM Tbl_Customers"
            
            ' Child_Tbl_Customers is the name of the CONTROL of type SubForm/Subreport 
            ' for the SubForm, NOT NECESSARILY THE NAME OF THE SUBFORM ITSELF!
            '
            Set Me.Child_Tbl_Customers.Form.Recordset = .OpenRecordset
        End With
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thank you so much! I will give this a try.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •