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

hi Arnel,

nice to see you here.

Its nice to see all the messages in a thread, but the default for many is not to include previous messages. Duane uploaded an image showing what you need to select:

https://groups.io/g/MSAccessProfessionals/photofromactivity?id=2977001

kind regards,
crystal

On 7/18/2020 11:12 PM, Arnelito Puzon wrote:

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