Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009

    Unanswered: Parameter querydef as recordsource?

    Hi. I've got a form which uses a passthroguh query as its record source. I need a button which changes this record source to a difference passthrough query with a parameter. The Error I'm getting is a type mismatch on the Recordsource=qdExtData line. I think Ive set up the Querydef ok (usign microsoft's guide) but where are the actual results ?
    Private Sub check_Click()
            Dim db As DAO.Database
            Dim qdExtData As QueryDef
            Dim strSQL As String
            Set db = CurrentDb
            strSQL = "dbo.sp_RMView @Ended=" & 0 'parameter is hard coded for now
            Set qdExtData = db.CreateQueryDef("")
            qdExtData.Connect   = "ODBC;DSN=AtlasODBC;UID=******;PWD=*******"
            qdExtData.SQL = strSQL
            Forms![Rent Monitor 10].Form.RecordSource = qdExtData
            Set db = Nothing
    End Sub

  2. #2
    Join Date
    Sep 2002
    South Wales
    Pass-through query or stored procedure?

    I think the following SQL is referring to a stored procedure?

    strSQL = "dbo.sp_RMView @Ended=" & 0 'parameter is hard coded for now

    <Change strSQL to the actual SQL you need to return your records

    strSQL = "SELECT * FROM YourTable WHERE YourFiled=" & YourParameter & ";"

    (Encapsulte parameter with quotes if a string).

    Forms![Rent Monitor 10].Form.RecordSource = "qdExtData"

    <Change the above to include the quotes

    Then post an update.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Jun 2009
    OK It is a passthrough query that just runs a stored procedure which has parameter values.

    But your Post helped with the quotation marks ! got a working version:
    Dim qdf As QueryDef
        Dim frm As Form
        Dim rstReport As DAO.Recordset
        ' Set database variable to current database.
        Set db = CurrentDb
        Set frm = Forms![Rent Monitor 10]
        'Open QueryDef object.
        Set qdf = db.QueryDefs("RMView")
        qdf.SQL = "EXEC dbo.sp_RMView @Ended=" & 0
        qdf.ReturnsRecords = True
        Set rstReport = qdf.OpenRecordset()
        Me.RecordSource = "RMView"
    This works fine, except it saves the new SQL into the existing pass through query (RMView), and so I have to change it back right after this code. How would I change the above so that it creates a Temporary query which is RMView & some more sql concatenated onto it? I've tried CreateQueryDefs but didn't work :-(

Posting Permissions

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