Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

if it is a SubReport we are talking, you can still filter the subreport using the Parent Report's OpenArgs.

the OP needs to Copy/Paste the Original pass-through query to another pt query name(ex: zzRptQuery1)

on the Open Event of the Subform:

Private Sub Report_Open(Cancel As Integer)

    Const ALTERNATIVE_SOURCE As String = "zzRptQuery1"

    Dim strWhere As String

    Dim strSQL As String

    Dim qd As DAO.QueryDef

    Dim db As DAO.Database

    strWhere = Nz(Reports!TableReport.OpenArgs, "")

    If Len(strWhere) > 0 Then

        Set db = CurrentDb

        strSQL = db.QueryDefs("theOriginalPassThroughQueryNameHere").SQL

        strSQL = Replace(strSQL, ";", "")

        strSQL = strSQL & " WHERE " & strWhere

        'On Error Resume Next

        'DoCmd.DeleteObject acQuery, ALTERNATIVE_SOURCE

        'Set qd = db.CreateQueryDef(ALTERNATIVE_SOURCE, strSQL)

        'db.QueryDefs.Append qd

        Set qd = db.QueryDefs(ALTERNATIVE_SOURCE)

        qd.SQL = strSQL

        Set qd = Nothing

        Set db = Nothing

        Me.RecordSource = ALTERNATIVE_SOURCE

    End If

End Sub

--

Arnelito G. Puzon

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115713) | Reply To Group | Reply To Sender | Mute This Topic | New Topic

Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_