RecordSource in Report

[tMitch]

Hi

I would like to use 2 queries for one Report rather than having two separate reports or manually changing the RecordSource. I had the RecordSource refer to a combo box in a form for the correct query, but I get an error message saying there is no such thing.

I've searched through the forum and I guess other people have had similar problems, but there didn't seem to be an easy answer. And then there's the additional complication of how to open the form before the report opens...

I don't know how to write code, but is there some (not too complicated) way to do this?

Just wondering....

Thanks.

 

[bjackson]

question? are the 2 querys based on the same tables

is it only the criteria that changes

 

[tMitch]

Yes, the 2 queries are based on the same table, but I am doing different calculations within the queries. The columns stay the same - just the calculations within the Expression Builder are different and refer to different columns in the query.

I suppose the most efficient thing to do is to rewrite the code in the expression builder so that one query fits all, but that seems complicated (for me) and would take me some to figure out.

 

[bjackson]

the only way i know to accomplish what you want
is to have a dynamic query,and that does involve
QBF code(query by form) which is to complicated
to sstep you through

I have done a few things in access along the
way of learning that are not the right way but they
have worked enough for me to carry on until i knew
enough to do them a better way

this is not a good way but it does work providing
all the field names stay the same,
i used really short names just for an example

have 2 command buttons on your form to open
the report,on the click event for each button

Private Sub Command0_Click()
DoCmd.OpenReport "H3", acViewDesign
Reports![h3].RecordSource = "h1"
DoCmd.Close acReport, "h3", acSaveYes
DoCmd.OpenReport "H3", acViewPreview
End Sub

Private Sub Command1_Click()
DoCmd.OpenReport "H3", acViewDesign
Reports![h3].RecordSource = "h2"
DoCmd.Close acReport, "h3", acSaveYes
DoCmd.OpenReport "H3", acViewPreview
End Sub

as i said its not a good way but it does work

hope this helps
if you like you could send me the db and i could
do it properly for you

regards bjackson

 

[tMitch]

Thank you for your comments and help on this - and the offer to work on the db. I will experiment with the form as you suggested. This particular database probably won't be used for much more than its current use, so I will just make my life easier and keep it as is with 2 separate queries and 2 separate reports.

But thanks very much for your time and offer of help!