Use the same subreport with different data? (1 Viewer)

[mafhobb]

Hi. I have a report with a subreport that shows today's check ins. The subreport is really simple, showing just property, guest name and phone number. The source data is a query.

SELECT tblReservations.[ID], tblReservations.[PropertyName], tblReservations.[GuestName], tblReservations.[CheckInDate], tblReservations.CheckOutDate, tblReservations.[CleaningComments], tblReservations.[Status], tblReservations.[PhoneNumber] FROM tblReservations WHERE (((tblReservations.CheckInDate)=Date()) And ((tblReservations.Status)="Active"));

I need more info in this report, such as today's checkouts. Can I just use the same subreport with different query for source, and position it next to the check ins to display this data or do I need to create a different subreport?

tblReservations.[ID], tblReservations.[PropertyName], tblReservations.[GuestName], tblReservations.[CheckInDate], tblReservations.CheckOutDate, tblReservations.[CleaningComments], tblReservations.[Status], tblReservations.[PhoneNumber] FROM tblReservations WHERE (((tblReservations.CheckOutDate)=Date()) And ((tblReservations.Status)="Active"));

mafhobb

 

[arnelgp]

Can I just use the same subreport with different query for source

you experiment and find out for yourself if that is possible.

 

[The_Doc_Man]

The report gathers its data from its underlying query by field name. (And by table name if a JOIN is involved.) As long as the names that the report expects are present in the different source query, it should work OK. If any field is missing, though, forget about it.

 

[Gasman]

Why not have a report that shows everything and hide checkouts when not available?

 

[mafhobb]

Ok. I expected to be able to just change the report source to a different query, but that does not seem to work. Perhaps it is the process that I follow that makes it impossible.
So, I have the main report (rptTodayReport) and the subreport (rptTodayCheckInsSubrpt) near the top side of the report (with the first query as source). This works fine for the check ins today. Then I drag the same subreport again to just below where I have the first subreport, and I expected to be able to click on it and then change the source. Instead I get the message "Source Object=Report.rptTodayCheckInsSubrpt" in a white box, representing the subreport. I expected to be find a "RecordSource," and to be able to change it to a different query (just like the second query on the first post), however I cannot find the recordsource. This process is basically bringing up two identical subreports, one on top of the other.
What am I doing wrong?
The only way that I have figured out how to do this is by copying the subreport, giving it a different name and then changing the recordsource to the query that I want. Finally this new subreport is dragged onto the report. This works, but it is a work around this and it is a mess when I want to use the subreport more than twice or three times. there has to be a better way to do it.

 

[ahmad_rmh]

The best solution is make a form with an unbound combobox and the RecordSource for the combobox, it's your choice.

In that form insert a list box and present what you want and export data to excel or pdf.

On the other hand, if your reports are required grouping and calculated fields then it's better to make separate report for each one.

If you have the same heads or minor changes in heads then make a query and insert combobox in report and filter the records as per the selection in combobox and hide / unhide fields as @Gasman has suggested.

 

[Pat Hartman]

Controls are bound to columns of the form/report's RecordSource. You can only swap different queries/tables when the all the various options contain all the columns that are bound to controls. So, if you have two queries with different sets of fields - a,b,c,d,e and b,c,d,f; they only work if the only bound fields are b, c, and d

Just make a new subreport.