Triggering Report from Form in Project

[scking]

I am transitioning from using Jet to SQL Server and a .adp file and T-SQL Stored Procedures. I would like to open a report from the form and refine the scope of the report. One way is to set the RecordSource for the report to select all items and in the Report_Open event set the filter from the controls on the form. All I really would like to be able to do is set the RecordSource dynamically in VBA while it is opening. I believe setting the filter actually processes the full set of data and then only displays the filtered set. Since this is being processed over the WAN I need to minimize information traveling over the network.

I set the parameters using a form so I would not want the user to be prompted again.

Any help would be appreciated.

---------------------
scking@arinc.com
---------------------

 

[teddyatjorundotnet]

I think you're on the right track with setting the "filter" in the Report_Open event. Why not flat out set the record source though?

Essentially you could do something along the lines of:

Dim frm As Form
Dim Src As String
Dim WhrClause As String

Set frm = Forms!yourForm
Src = "SELECT ... YOUR BASIC SELECT STATEMENT "
WhrClause = " WHERE yourTable.YourField = " & frm!YourCriteriaControl.Value

Me.RecordSource = Src & WhrClause

Of course that's the much, much abridged version. You could use any number of techniques to actually build the string. Here's a working example of one I've done:

OldSrc = Me.RecordSource
cmbSort = Forms!paramLossRatios_all!cmbSort.Value
CmbOrder = Forms!paramLossRatios_all!CmbOrder.Value

If CmbOrder = "Ascending" Then
order = " ASC"
Else
order = " DESC"
End If

Select Case cmbSort
Case "Member Name"
sort = "company.company"
Case "Start Date"
sort = "sdate"
Case "Total Losses"
sort = "losses"
Case "Total Premium"
sort = "quote.ann_premium"
Case "Loss Ratio"
sort = "ratio"
End Select

Me.RecordSource = OldSrc & " " & Forms!paramLossRatios_all!cmbYear.Value & &quot AND policy.term_date IS NULL ORDER BY " & sort & order