Re: Print Form/Report with Master and one Detail record per page?
From: Chuck (anonymous_at_discussions.microsoft.com)
Date: 02/16/04
- Next message: Eric G: "More efficient code?"
- Previous message: Graham Mandeno: "Re: Referring to textbox on Tabbed Control"
- In reply to: Rod Scoullar: "Re: Print Form/Report with Master and one Detail record per page?"
- Next in thread: Paul Falla: "Print Form/Report with Master and one Detail record per page?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 16 Feb 2004 15:23:21 -0800
Thanks 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
MasterTable.MasterTableKey =
DetailTable.MasterTableKey
WHERE DetailTable.Primarykey.Skey = MasterTable.Skey and
DetailTable.Primarykey.Srt = 1
This would be in my case:
SELECT * FROM tblSWChngNotice INNER JOIN tblSCNdata ON
tblSWChngNotice.Skey = tblSCNdata.Skey
WHERE tblSCNdata.Skey = tblSWChngNotice.Skey and
tblSCNdata.Srt = 1
Now that I've re-thought this, with your help, I think it
should work as you suggest with creating a query with just
the selected records to be printed.
I have to give it some thought on how to make it an
automatic printout using a form. I will probably need to
create a report and print each record on a different sheet
to make it automatic.
I will check the SQL format and try to implement this (by
the end of this week) and see if it works. Or I may use
the Query Designer to create the query.
Thanks again Rod.
Chuck
>-----Original Message-----
>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
>
>
>.
>
- Next message: Eric G: "More efficient code?"
- Previous message: Graham Mandeno: "Re: Referring to textbox on Tabbed Control"
- In reply to: Rod Scoullar: "Re: Print Form/Report with Master and one Detail record per page?"
- Next in thread: Paul Falla: "Print Form/Report with Master and one Detail record per page?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
