Report Design with Relational tables

[Guest]

I am trying to create a report using a query that links 2 tables (Master and
Transaction) with custid (one to many). That query works great. I plan to
pass the report sort order in my docmd.openreport command. I will be sorting
by Last Name (one), or Event or Amount (many). I have Labels from the
Master data printing in the header. I have the data from the Master and
Transaction tables printing in the detail section. I also have Labels for
the Transaction fields in the detail.

My problem is the Labels for my Transaction information prints for each
transaction. It should only print when a new Last Name, Event or Amount
changes. I am trying to use the same report for all sorts so I can't
hardcode the sort/grouping with headers and footers. I hope this makes
sense. Any help would be very appreciated.

Thanks, Sarah

 

[Marshall Barton]

I am trying to create a report using a query that links 2 tables (Master and
Transaction) with custid (one to many). That query works great. I plan to
pass the report sort order in my docmd.openreport command. I will be sorting
by Last Name (one), or Event or Amount (many). I have Labels from the
Master data printing in the header. I have the data from the Master and
Transaction tables printing in the detail section. I also have Labels for
the Transaction fields in the detail.

My problem is the Labels for my Transaction information prints for each
transaction. It should only print when a new Last Name, Event or Amount
changes. I am trying to use the same report for all sorts so I can't
hardcode the sort/grouping with headers and footers. I hope this makes
sense. Any help would be very appreciated.

First, you can not specify the sort order of a report using
the OpenReport method. You can use the OpenReport method's
OpenArgs argument to pass information to the report where
the report can then do what's needed.

You need to create a group with group header section for
some field (e.g. LastName) to separate the master data from
the transaction data in the detail section.

You can use the report's Open event to modify the
sort/group level field by using code like:

Me.GroupLevel(0).ControlSource = Me.OpenArgs