Queries and Reports (1 Viewer)

[clauses]

I have a query used as the record source for a report. This report is called from a form, the query using a field from the form as a parameter. I am now developing another report that is called from another form that would use the same information as a subreport and would like to use the existing query as a record source. Is there a way for me to use set up the query to run using the value from either form as a parameter.

 

[Cosmos75]

The answer to this could prove useful!

Good Question!!

 

[TessB]

If you really want to... but

You COULD do that.... by just referencing the second form in the criteria of the query with an OR statement. BUT, that would possibly foul up your reports if

#1 Both forms are not opened
#2 The forms return different criteria, and on your first report, you only want to see records that would be filtered from the first form.

I would suggest finding another way.

Respectfully,
Tess

 

[TessB]

An alternate suggestion?

Just thinking out loud here, but you could use a global variable to store the value of the forms which would change as either "Submit" button is clicked couldn't you? And then use that as the parameter?

 

[Cosmos75]

One form with two buttons?

Just curious, is there anyway to use just one form to choose your parameters and call the report that you want to generate from a one to two buttons (one for each report)?

 

[TessB]

Of course!

Yes, of course, but what I usually do is maintain a table with a list of available reports, make a drop down box out of that and stick it on a form and when I press the submit button, I code it to reference the selection in the drop down box to tell it which report I want it to open.

 

[Cosmos75]

TessB,

Would that be the code that would be used for the button but in the afterupdate part for your combobox? [For many reports, I a more partial to a ListBox]

I am not too familiar with VBA for MS Access other than what MS Access generates automatically. How do you populate you combobox with the names are the reports? How do you reference the current selected reporti n your open report code?

 

[RichMorrison]

You wrote
<<
Is there a way for me to use set up the query to run using the value from either form as a parameter.
>>

See Access Help for "Property Procedures".

RichM

 

[TessB]

Dim stReportName As String,
stReportName = cboReports
DoCmd.OpenReport "Rpt_" & stReportName, acViewPreview

 

[Cosmos75]

Thanks

TessB, THANKS!

I hope clauses finds a solution to his/her problem in this thread somewhere!

 

[Cosmos75]

Is it possible to run reports through a combo box?
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30505