ISO Report Formating (1 Viewer)

[GWally]

Name is Greg.
I am an intermediate Access user.

What I am trying to do:

I have 2 tables, Parent and Child.
Tables have a relation ship of one -many (Some Parent records have many Child records).

I want to print all Parent records with Child records below the associated Parent.

Looking to format report so that a blank block of space is not under Parent records that have no Child records.

Attached is a PDF showing the format I am looking for (PDF generated with Word).

View attachment Report Exmpl.pdf

When Child records are placed in a second group report filters only Parent records that have Child records. Need to print all Parent records with Child records.

Any time used to help is appreciated.

Thanx
Greg

 

[pbaldy]

It sounds like you need to edit the join in your query. Right click or double click on the join line in design view to edit it. The appropriate selection should be obvious. Then in your report, you'd group on the parent.

 

[GWally]

Appling suggest resulted in the following.
Report output, table and table relationship is shown Below:







Have other suggestions?

Greg

 

[Beetle]

Setting the Cancel argument in the Format event of the report section where the BoatMake control is might solve your issue. Can you show us the design view of your report?

 

[jdraw]

I think you need to include a WHERE clause in the query that forms the recordsource of your report.

Something like (best guess)

1 --WHERE BoatID.MemberID IS NOT NULL

Or

2-- Reverse the Direction of the join line
Get records from Boat and only get members where the Ids are equal.
(There should e no member records in Boat if they don't have a boat.)
Either should work ( I think)

Good luck

 

[GWally]

Report layout as requested.

 

[GWally]

jdraw,
Please keep in mind I am an intermediate user at best. Hence the questions.

Will the "Where" clause mentioned (1 --WHERE BoatID.MemberID IS NOT NULL) print include all parent records and include child records as shown in the example listed in initial thread entry?

Seems like that will eliminate all the parent records that do not have entries in the child table.

 

[GWally]

I do intend to include a page header and footer after report layout issue is resolved.
Would it be a good idea to include that before report layout issue is addressed?

Greg

 

[Beetle]

Try this;
Open the properties for the BoatMake Header section of your report, go to the Event tab and in the On Format event put Cancel=IsNull([BoatMake])

 

[GWally]

Tried Cancel=IsNull([BoatMake]) ... no affect. This makes sense because there are no records that have Null values in BoatInfo table. I also tried to add an entry with Null values in BoatMake and deleted data from from BoatMake in 5th record as well.

Any way to make the condition based on Member.ID <>BoatInfo.ID instead of a Null value?
Not sure where that conditional statement would be in the layout Property Sheet.

 

[GWally]

Sorry ....
Any way to make the condition based on Member.ID <>BoatInfo.MemberID instead of a Null value?

 

[Beetle]

This makes sense because there are no records that have Null values in BoatInfo table.

That may be true, but the report is based on a query that is using an outer join, so the resulting recordset would normally include records that have a null value in the BoatMake field if not all members have boats. Any chance you can upload a small sample db that duplicates your problem so we can see what is happening?

 

[Beetle]

Keep in mind the Format event only fires when you print the report, or view it in Print Preview. It doesn't fire if you are just viewing the report on screen in Report View

 

[GWally]

Was not aware that OnFormat only fired on print.
I still couldn't get it to work.

Mini db included with this post.
Your effort is appreciated here.
Thank You

Greg

View attachment Forumdb.accdb

 

[GWally]

I do appreciate the help.
And the tolerance of those of us that are learning.

Greg

 

[Beetle]

I might not have explained well enough the first time. I am re-attaching your db. If you open the report in design view and go to the properties for the BoatHeader section, then the Event tab, you will see "[Event Procedure]" in the On Format line.

If you click the elipse (...) to the right of this line it will open the code window, where you will see the line of code that triggers the Cancel argument of the On Format event;

Cancel = IsNull([BoatMake])

If you print the report, or view it in Print Preview, you will see that the Boat Header section is supressed for Members that do not have boats.

 

[GWally]

Excellent. Thank you Beetle.
I was literally putting the Cancel phrase in the "OnFormat" box instead of programming it in the private subroutine.

Again thank you for your time, patients and the lesson.
Nicely done.

Greg