pass criteria from subroutine to a report

[Harold Good]

Hi,

I'm new at learning VBA so I don't have all my terminology down. I have a
report that I want to be able to access from two separate forms. I have it
set up and working now on the first form so that the command button click
event runs a subroutine to ask the user to input FiscalYear. This is then
written to an invisible text box on form 1. The report's criteria then goes
to [forms].[frm_Reports].[FiscalYear] to know which year to report on.

But I'd now like to access the report from a second form. Is there some way
to use the similar code from the above example, but instead of writiing this
fiscalyear information to a form (which won't be open in this second case),
put it in public memory and then have the report criteria access this
FiscalYear info from memory? In my code I've tried defining "Public
intFiscalyear As Integer", putting it at top of code page. In my report I've
put [intFiscalYear] into my report criteria, also tried prefacing it with an
equal sign, but neither way will work.

Is there a way to do this or will I always need to copy the results of the
parameter query onto a form, and then for the report criteria, go to that
form for that data?

Thanks,
Harold

 

[Marshall Barton]

I'm new at learning VBA so I don't have all my terminology down. I have a
report that I want to be able to access from two separate forms. I have it
set up and working now on the first form so that the command button click
event runs a subroutine to ask the user to input FiscalYear. This is then
written to an invisible text box on form 1. The report's criteria then goes
to [forms].[frm_Reports].[FiscalYear] to know which year to report on.

But I'd now like to access the report from a second form. Is there some way
to use the similar code from the above example, but instead of writiing this
fiscalyear information to a form (which won't be open in this second case),
put it in public memory and then have the report criteria access this
FiscalYear info from memory? In my code I've tried defining "Public
intFiscalyear As Integer", putting it at top of code page. In my report I've
put [intFiscalYear] into my report criteria, also tried prefacing it with an
equal sign, but neither way will work.

Is there a way to do this or will I always need to copy the results of the
parameter query onto a form, and then for the report criteria, go to that
form for that data?

A better way is to use the OpenReport method's
WhereCondition argument. This way you can remove the
criteria from the query and not worry about to put the
value.

Your open report button's code could be something like:

Dim stDoc As String
Dim stWhere As string
Dim strFY As String
strFY = InputBox( . . . )
strDoc = "name of report"
stWhere = "FiscalYear = " & Me.strFY
DoCmd.OpenReport stDoc, acViewPreview , ,stWhere