Printing Current Record on Form with Subform within Subform

[Gary Childress]

I have a form with a subform within a subform. When I pull up a record on the main form it pulls up all the info regarding an event (health fair). The main form has all the event info such as day, time, place, etc. The first subform has each day the event will run. Within that subform is another subform containing each volunteer who will be working the booth for that particular day, the volunteer's name, phone, hours volunteering, etc.



I created a report with two subreports to show all the info on an event, the days it will run and the volunteers who will be working the booth. As it stands now when I print the report it prints all the records on the form. I'd like to have a button where I can print only the current record. I've seen similar questions asked and answered on forums but I can't get any of the various code samples I've seen so far to work. I'm thinking it may be because of the subforms or else maybe I'm not typing in a form name in the right place or something. Or maybe I need to incorporate the subform names in the codes somewhere?

Thank you for any assistance.

[June7]

Where is the 'current record' - the main form, subform, subsubform? What criteria do you want to filter report on?

Why hasn't code worked, what happens - error message, wrong results, nothing? What code have you tried? Where did you put code?

DoCmd.OpenReport "rptEvent", , , "EventID=" & Me.EventID

[Gary Childress]

Thank you for the reply. My apologies. The current record is on the main form but the subform and subsubform also have info which appear on the report.

I just tried the code you provided and it worked fine. Although, instead of printing I should have said print preview. I'd like to preview the report before printing. What would I type to get the report to preview instead of print?

Thank you.

[June7]

Should get familiar with the arguments of OpenForm and OpenReport methods. https://msdn.microsoft.com/en-us/lib...ffice.15).aspx
You want acViewPreview.

[Gary Childress]

OK. I created a command button. On "click" I used the following line to preview the current record on a report called "Event Detail Report".

DoCmd.OpenReport "Event Detail Report", acViewPreview, , "ID=" & Me.ID

So far so good.

Only one major hurdle seems to remain and I think this database will be ready for use. The hurdle is that when I print a report it only shows records which have information in the relevant tables which the subforms are based on.

For example:

Let's say I want to print a list of all health fair events the organization attended in 2014. I open up the form "Switchboard", and choose the relevant filter fields to open a form called "Main Events Form". "Main Events Form" opens and shows a list of events attended in 2014. "Main Events Form" gets its data from a query called "Main Events Table Query" which queries data from a table called "Main Events Table".

Within "Main Events Form" is a subform called "Event Days Table Form" which contains all the days that the event was held on. Event Days Table Form" gets it's data from the table "Event Days Table". Within "Event Days Table Form" is another subform called "Event Volunteers Table Form" which lists all the volunteers who worked an event for that particular day. "Event Volunteers Table Form" gets it's data from the table "Event Volunteers Table".

So here's the hitch. If I open a record in "Main Events Form" which has no information in the subforms, and try to preview or print "Event Detail Report" the report will be blank.

Let's say I want to print an event report on an event called "Bob's Health Fair". Let's say we did not attend "Bob's Health Fair". All the basic information on the health fair exists in the table "Main Events Table" such as date, sponsor, location, setup time, etc. But since we didn't attend the health fair there is no information in the "Event Volunteers Table". Therefore the report showing "Bob's Health Fair" comes out completely blank, even though there is info in the "Main Events Table" on it.

What I want to do is have the report print the basic event information from the table "Main Events Table" even though the fields from "Event Volunteers Table" in the report will be blank. Is there a way to set things up to do that? I've attached a copy of my database below with some bogus sample information entered if that will help.

Thank you.

[June7]

How can I replicate the no data result? What event should I choose? There is no "Bob's Health Fair" event.

However, I think the issue is the INNER JOINs of the report RecordSource. Try:

SELECT [Main Events Table Query].*, [Event Days Table].Day, [Event Days Table].[Start Time] AS [Event Days Table_Start Time], [Event Days Table].[End Time] AS [Event Days Table_End Time], [Event Volunteers Table].[Volunteer Name], [Event Volunteers Table].Phone, [Event Volunteers Table].[Alternate Phone], [Event Volunteers Table].Email, [Event Volunteers Table].[Attendance Confirmed], [Event Volunteers Table].[Start Time] AS [Event Volunteers Table_Start Time], [Event Volunteers Table].[End Time] AS [Event Volunteers Table_End Time], [Main Events Table Query].ID AS [Main Events Table Query_ID], [Event Days Table].ID AS [Event Days Table_ID]
FROM ([Main Events Table Query] LEFT JOIN [Event Days Table] ON [Main Events Table Query].[ID] = [Event Days Table].[Event ID]) LEFT JOIN [Event Volunteers Table] ON [Event Days Table].[ID] = [Event Volunteers Table].[DayID];

[Gary Childress]

Sorry, I'm such a dolt. I stumbled upon the answer to my question yesterday afternoon. I created a report with subreports inside and that seems to have solved the problem I was having.

Thanks.

[June7]

Yes, report/subreport is an option. However, I think just changing the JOIN type in query would also resolve the issue.