CODEWITHSUNDEEP
sqlms-accessparametersvba
Wouter Goossens
Wouter Goossens

Reputation: 121

MS Access - Parameters from report in VBA

If you open a report based on a query with some parameters (e.g. StartDate and EndDate), I know that I can used those parameters in the report as [Reports]![ReportName]![StartDate]. But now I would like to use these parameters in a VBA function, but using the same expression doesn't work for me:

MsgBox [Reports]![Test]![StartDate]

This gives me the error:

Run-time error '2465': Microsoft Access can't find the field 'StartDate' referred to in your expression.

Upvotes: 2

Views: 2288

Answers (3)

June7
June7

Reputation: 21370

And it never will find it.

I never use dynamic parameterized queries. I use controls on a form to select filter criteria and refer to the controls in the WHERE argument of OpenReport command (same for OpenForm).

DoCmd.OpenReport "Test", , , "[StartDate] = #" & Me.tbxStart & "#"
MsgBox = Me.tbxStart

Or if you really prefer dynamic parameters - the report query parameters can refer to the form controls.

VBA code will not find the parameter but it can find textbox in report that references the parameter and pull the parameter value that way.

Upvotes: 1

Biju John
Biju John

Reputation: 1

You can do it by using a public variable and changing sql of record source dynamically.

You can store parameter value in public variable before calling the report.

First declare a varaible in a module eg, Public filtDate as Date

Secondly, assign the parameter value to filtDate before running the report

Then, change sql of records source in the report_open event of report

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM Test WHERE [StartDate] = #" & filtDate & "#"
MsgBox  filtDate
End Sub

Here, the sql of recordsource is dynamically set and parameter value can be used directly.

Upvotes: 0

Josh2566
Josh2566

Reputation: 38

If you have a report or form that has a query as its Record Source, you can bind the fields of the query--like StartDate--to controls on the report or form, and get the value(s) of that field in a VBA function by referencing the control. For instance, suppose you have a report named DateReport. If the Record Source of DateReport contains a StartDate field, then you can create a text box on the report--we'll call it StartDateBox--and set its Control Source to "StartDate". Then in VBA, you can get the value of this field for the current record with Report_DateReport.StartDateBox. There may be another way to reference Record Source field values of a form or report without binding them to an control, but I don't have MS Access available right now to test this.

Upvotes: 0

Related Questions