Re: Print Form/Report with Master and one Detail record per page?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Chuck (anonymous_at_discussions.microsoft.com)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 10:28:14 -0800

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

>-----Original Message-----
>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
>
>
>
>.
>

Relevant Pages

  • Re: Need some advice on how to do a form
    ... Master Query), which is crunching all our data on a secuirty-by-secuirty ... security which he wants to find all payments received for. ... the subform needs to add all the records for given ...
    (microsoft.public.access.forms)
  • Re: SQL Statement for limiting the number of detail retrieved in Access 2000?
    ... this cannot be handled in a query. ... INNER JOIN ... How do I limit the number of detail records selected in a Master-Detail set using SQL? ... I want to select all master records for a date, but only the first 3 records for the details. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Checkbox: Can I update another table?
    ... about your database structure, it would be hard to give you too much ... In short, pull the field you want to the query, then to the form. ... add a subform using the second table as a source. ... One is the 'Master' table ...
    (microsoft.public.access.forms)
  • Re: Subform not updating table
    ... So it is not a query. ... that my subform links to has not added the new data. ... but the subform is not updating the second table. ... >>should be linked correctly to Master, ...
    (microsoft.public.access.forms)
  • Re: Master/Detail Form using a Query
    ... in the main form and the "many" side is displayed in the subform. ... only in the query for your subform. ... master table moves from record to record with no proble. ... A problem occurred while Microsoft Office Access was ...
    (microsoft.public.access.formscoding)