Software Developer's Resources

How to Call a Stored Procedure in a RecordSource property

This article describes how to setup a RecordSource property to call a stored procedure to select and return a result set for a report.

Assume you created a stored procedure, named ReportSalesMonthly to report on the monthly sales for your organization.

The key is to set the RecordSource like this:

EXEC ReportSalesMonthly 

Now let's look at how to pass parameters and prevent SQL from outputting messages which causes grief.

Passing Parameters

Suppose your stored procedure takes a date parameter. The date specifies the year and month for the report.

How do you pass the date from your form to the report and onto the stored procedure?

Suppose you have a form. The user must enter a date for the report on the form and then clicks on a button to generate the report.

1. Create a textbox for the user to enter date on your form.
2. Open the report in Design View.
3. Right-click the black square icon in the upper left corner of the report form.
4. Select Build Event...
5. Select Code Builder and Click on OK.
6. Access opens a code page and inserts an empty subroutine named Report_Open.

Private Sub Report_Open(Cancel As Integer)

End Sub

When Access opens your report in display mode it calls the Report_Open subroutine before generating the report. We'll set the RecordSource in the subroutine so we can pass parameters.

7. Add statements to the subroutine to get the date from your form.
8. Add statements to the subroutine to format an SQL statement which executes the stored procedure.

The format of the SQL statement might be:

EXEC ReportSalesMonthly '2009/9/5'

9. Set the RecordSource property of the report to the SQL statement.

The Report_Open code might be:

Private Sub Report_Open(Cancel As Integer)
    Dim MyDate As Date
    Dim MyRecordSource As String
    
    MyDate = Forms("Main Form").DateBox.Value
    ' I assume you validated the date on your form, so no need to check it here.
            
    MyRecordSource = "EXEC ReportSalesMnthly $Date"
    MyRecordSource = Replace(MyRecordSource, "$Date", "" + MyDate)
    
    Me.RecordSource = MyRecordSource
    
End Sub

10. Add SET NOCOUNT ON to the beginning of the code in your stored procedure.

This prevents SQL from outputting messages like: "1 row(s) affected". See below for more information.

11. Test by saving and closing the report and reopen the report in Preview-mode.

Set NOCOUNT ON

When SET NOCOUNT is set to OFF SQL outputs messages like this:

1 row(s) affected.

When Access executes the stored procedure (by using the RecordSource in your report) Access reports an error due to the messages output by SQL.

To stop SQL from outputting the messages add the following statement to the beginning of your stored procedure:

SET NOCOUNT ON

Access now executes the stored procedure without error.