Developer Forum

Is there a way to print, on a single page, master and
detail data when multiple detail records can occur for
each master record? This needs to be done automatically
without user response.

I have a form with master/detail record data. There can be
many detail records for each master record. Detail and
master data are on separate tables. Both form and report
used subform/subreport respectively. I have been unable to
print the form with just a single detail record data per
page when there are multiple detail records.

I tried using a report, but it also failed. It printed all
detail records on multiple pages along with a header and
trailer on first and last page respectively.

I created a partial solution using a filtered form printed
from a different print menu form, but unfortunately Access
generated a prompt for the user to type in, thus defeating
the desired automatic print action. Also it caused an
annoying flash on the screen as the form to be printed
reloaded for each new detail record.

Any suggestions on how to do this will be appreciated.
I am using Access 2000.

Thanks.
Chuck Harmon

Chuck,

I'm not certain that I have understood you properly, but it seems that you
are wanting to print the data related to each detail record, including the
related data from the master record, on a separate page.

If this is the case using a subform is probably the wrong approach.

You would be better to create a query containing all the required fields
from both master and detail tables. This could be doe using a SQL join
statement or if you aren't that familiar with SQL, using the query design
screen.

Each row of this query would then contain the data from the detail record
along with the data from the related master record.

Using this query you can design your report using the fields from the query
and you can force a separate page for each record.

Rod Scoullar

Dear Chuck

It sounds like your parent and child link between your
master and sub-form is not set correctly. Both form and
subform must contain the same unique record identifier -
the same obviously gooes for report and sub report. Hence
your master to child link would show as MasterTable!
MasterId to DetailTable!MasterID

Hope this helps

Paul

be
to
all
printed
Access
defeating

Thanks Paul.

You are right, the master and detail do not link up
correctly one-to-one. For the purpose the forms were
designed for, that was not the case, and the subform
worked correctly showing all detail records for any master
record. But for what the user now wants I do need to
change the links between form and subform to be one to one
or try another approach.

Thanks again
Chuck

Thanks Rod for the reply.

You understood correctly.

For the purpose the forms were designed for, the subform
worked correctly showing all detail records for any master
record. But for what the user now wants I think I need to
change the links between form and subform to be one to
one, or eliminate the subform.

The approach you suggest I haven't tried, but I did
eliminate the subform and was able to print master and
detail on one sheet of paper but with the problems
indicated in my original post. I did not have a query
combining the master and detail records however and did it
the hard way with a query for the detail records and
inserting the master data into unbound fields. I read the
recordset clone in sequence in the Form_Load event and
filtered the form on one key plus one field in the second
key. The form was the printed from another form by the
Docmd.SelectObject and the print statement. Now that I am
writing this I realize that I didn't test that thoroughly
because I believe I could have printed out more records
that desired due to possible duplicate data in one of the
keys.

I believe your way is better but don't know if I can
create such a query without having a prompt (which I am
trying to avoid). This is because the detail records are
such that key data for any individual detail record can
vary and unless you are looking at the table or reading it
record-by-record in sequence, unknown data is in one of
the two keys for the record (4 fields make up the second
key). The first key links to the master record, the second
key makes the record unique(along with the first key).

I cannot combine the two keys into one because part of the
data for the second key is not known when the detail
record is created.

If there is a way to create a query, using code, by
reading a recordset then I could probably create the query
you suggest. This may be possible, but I have to see if I
can find any examples anywhere.

Thanks again,
Chuck


it seems that you
record, including the
approach.
required fields
using a SQL join
the query design
the detail record
fields from the query

Chuck,

Sounds rather complex and I don't understand all the details but:

The JOIN query should still work because the link between the master and
detail records only depend on the common value of the master key in both
tables.

A join such as

SELECT * FROM MasterTable INNER JOIN DetailsTable ON
MasterTable.MasterTableKey = DetailTable.MasterTableKey

Should give you all data from the details table along with all data from the
corresponding master table.

Adding a WHERE clause to the above should allow you to select the relevant
records in the same way as you select them now. I suspect it is the way you
select them now that I'm not understanding

You could try explaining it again using smaller words and I might finally
get it.

Rod Scoullar

hanks Rod for the info. I think I understand the inner
join from your example. I hope I haven't confused you too
much. It is complicated to explain. I'll try again in
more detail.

I have a detail table holding records associated with the
master records. Initially it had no primary key.
Everything worked fine. Master/detail data was all printed
on one sheet even in the case of many detail records. Then
the user wanted to print master/detail data for specific
detail records on individual sheets of paper (for
documentation). Another change to the detail table
required adding three additional fields to allow the user
to enter data where one of the key fields allowed
duplicate data. This required making those fields into a
Primarykey to allow a detail record to be uniquely
identified. I initialized all required Primarykey fields
as needed by hand. Records where the Srt field in the
Primarykey = 1 are the detail records that need to be
printed.

My Detail table has a numeric key (Skey) which is the same
as the MasterTable, but it is Not THE primary key. The
Detail table has an additional key, a new Primary key,
involving four fields, including Skey. The detail record I
want printed out, along with the corresponding master
record data is the matching detail record where the Skey
field in the Primarykey = the MasterTable Skey field and
Srt field in the Primarykey =1. I want to print all such
records, if more than one. The way I figured out to do it
(although with problems) was to:

1) Select form (detail records bound to DetailTable query)
using the DoCmd.SelectObject
2) In Form_Load using recordsetclone, read a detail
record, in order (using movenext), where the MasterTable
key = the DetailTable key. An args value tells how many
movenext's to get to the next unique record.
3) Access prompts for the ProgramName (I have been unable
to eliminate the prompt)
4) The form is then filtered on that detail record after
entering the specified prompt value.
5) I exit the form (its still showing on the screen)
6) Using DoCmd.PrintOut , 1, 1. I print the form, then
unselect the form.
Note: I need the PrintOut, 1, 1 in case the user
cancels the prompt, to prevent printing all detail
records data on multiple sheets.
7) I loop back to select and open the form, and read the
next record and repeat the process, each time selecting
the next detail record for the selected master record.

There can be from 1 to many detail records for any master
record.

The user can view/change/add/delete detail records at any
time prior to printing. Now, because there is a multi-
field Primarykey, the appropriate Primarykey fields are
changed/initialized by code, using recordsetclone, at the
time of any change or creation of a new detail record.
This is done to manipulate the records to appear in a
specific meaningful order regardless of the user's actions
of creating or changing any detail records.

To print, the user should click a print key to print all
records that need to be printed.
If my code is correct, then specific, detail records (for
that master record) are the records I want to print (see
below).

I believe in order to follow your suggestion of using a
SQL INNER JOIN the needed SQL clause to select all
DetailTable Primarykey records to be printed would be:

SELECT * FROM MasterTable INNER JOIN DetailsTable ON
Maste