CODEWITHSUNDEEP
vbams-accessreport
mad
mad

Reputation: 1047

define query parameter before using it as source for a report

I have

  1. a view VIEW1 with parameter [YEAR] (when I open thin view access asks me for the YEAR).

    select a, b, c from tab where y=[YEAR]

  2. a view VIEW2

    select a, b from VIEW1

  3. a report REPORT1 using VIEW2 as source

  4. a form FORM1 with a field YEAR

Scenario:

Is it possible to manage this scenario via VBA?

Please note I know the way:

select a, b, c from tab where y=[Form1]![YEAR]

Problem is I would like to use VIEW1 as low level datasource from other forms and for other reports.

UPDATE: What I need is:

Private Sub Report_Open(Cancel As Integer)
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Anlage1_ken3")
  qdf.Parameters("[PARAM_YEAR]") = 2016
  qdf.Parameters("[PARAM_FIRMA]") = 1
  Set rst = qdf.OpenRecordset
  Me.RecordSource = rst 'I have an exception here
End Sub

Does anybody know a way to use this recordset as report's RecordSource ?

Upvotes: 0

Views: 308

Answers (1)

Minty
Minty

Reputation: 1626

Use your View1 as parameter query - something like this. 

   Set db = CurrentDb
   Set qdf = db.QueryDefs("View1")

   qdf.Parameters("[Year]") =2017
   Set rst = qdf.OpenRecordset

See this link for a method to use the recordset in the report http://access.mvps.org/access/reports/rpt0014.htm

Apparently you can by doing this

Set Me.Recordset = qdf.OpenRecordset()

Upvotes: 0

Related Questions