VBA - Pass value from form to report for group/sorting options (1 Viewer)

[Nyanko]

Hi,

I'm using a form to return searched values in a subform and then have the ability to generate a report.

Adapted from this awesome code : http://www.access-programmers.co.uk/forums/showthread.php?t=99353

I have this working perfectly, however I would like to give my users the ability to group and sort the report. To generate the report I'm using the below code :

Private Sub btn_Report_Click()
    Dim stDocName As String
    
    'Send the current selection to a report
        If BuildFilter = "" Then
        Me.frm_CommissionSearch.Form.RecordSource = "SELECT * FROM Qry_CommissionSearch " & BuildFilter
    Else
        Me.frm_CommissionSearch.Form.RecordSource = "SELECT * FROM Qry_CommissionSearch WHERE " & BuildFilter
    End If

    stDocName = "Rpt_CommissionSearch"
    DoCmd.OpenReport stDocName, acPreview, WhereCondition:=BuildFilter
End Sub

and I have seen on this site how to group a report on the fly with vba :
http://www.techrepublic.com/article/how-to-group-an-access-report-on-the-fly/

Private Sub Report_Open(Cancel As Integer)
    Me.GroupLevel(0).ControlSource = Forms!criteria
End Sub

My question is how do I phrase the DoCmd to pass the criteria to the report ?

 

[cyanidem]

Use OpenArgs parameter to pass criteria string on DoCmd.OpenReport:

DoCmd.OpenReport stDocName, acPreview, WhereCondition:=BuildFilter OpenArgs:="something you want to pass"

and then read it in Report_Open event sub:

Dim CriteriaString as txt
CriteriaString=Me.OpenArgs

 

[arnelgp]

pass the grouping on the seventh argument of the docmd.openreport (OpenArgs). then on the report's load event you evaluate the passed argument:

Private Sub Report_Load()
Me.GroupLevel(0).ControlSource = Me.OpenArgs
End Sub