Report linked to subform in a subform

[jubu]

I have an Access 2003 database with several tables: tblCustomers stores
customer information with primary key the CustomerID field. This is linked
to tblEnrollments in a one-many relationship, and includes a ClassID field
(and several other fields). The combination of the ClassID and the
CustomerID are the primary keys for this table. A third table in my dilemna
is tblPayments which is linked to tblEnrollments via the ClassID and the
CustomerID. The primary key in tblPayments is the PaymentID field. (The
Enrollments table is also linked to a class table which lists the various
classes available.) I have created a main form, frmCustomers which has tab
controls on it. The first tab control contains customer information. The
second tab control contains a subform called sfrmEnrollments based on
tblEnrollments. This subform is set up as a single form and contains a
subform of its own called sfrmPayments based on tblPayments. All of the
subforms are correctly linked via the Master/Child links so that when I am
looking at a specific customer I can see just the classes they have enrolled
for and then I can apply/view the payments for that enrollment.

Here's my question: I would like to be able to print a receipt for a
specific payment. I created a button on the payment subform that will bring
up the receipt report, however, I am having difficulty with the criteria in
the query for the receipt report. Since this payment subform is actuallly a
sub-subform and is on a tab control, is there a different way of entering my
criteria? In my query for the receipts, under the PaymentID field, my
criteria was entered as
[forms]![frmCustomers]![sfrmEnrollments]![sfrmPayments]![PaymentID] But
this isn't working. How do I go about getting it to realize that the field
PaymentID is on a subform (sfrmPayments) which is on a subform
(sfrmEnrollments) which is on a tabcontrol of a mainform (frmCustomers)?

Thank you in advance for any advice

jubu

 

[Allen Browne]

The tab control is not part of the reference, but you need to include the
".Form" bit to refer to the form in the subform control:
[Forms]![frmCustomers]![sfrmEnrollments].Form![sfrmPayments].Form![PaymentID]

If that still fails, open the main form in design view, and check the Name
of the subform control. It can have a different name than its SourceObject
(the name of the form loaded into it.)

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

An alternative approach would be to leave the criteria out of the query, and
use the WhereCondition of OpenReport instead. Example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
If the button is actually on the inner subform, the code will just use:
strWhere = "[PaymentID] = " & Me.[PaymentID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access 2003 database with several tables: tblCustomers stores
customer information with primary key the CustomerID field. This is
linked
to tblEnrollments in a one-many relationship, and includes a ClassID field
(and several other fields). The combination of the ClassID and the
CustomerID are the primary keys for this table. A third table in my
dilemna
is tblPayments which is linked to tblEnrollments via the ClassID and the
CustomerID. The primary key in tblPayments is the PaymentID field. (The
Enrollments table is also linked to a class table which lists the various
classes available.) I have created a main form, frmCustomers which has
tab
controls on it. The first tab control contains customer information. The
second tab control contains a subform called sfrmEnrollments based on
tblEnrollments. This subform is set up as a single form and contains a
subform of its own called sfrmPayments based on tblPayments. All of the
subforms are correctly linked via the Master/Child links so that when I am
looking at a specific customer I can see just the classes they have
enrolled
for and then I can apply/view the payments for that enrollment.

Here's my question: I would like to be able to print a receipt for a
specific payment. I created a button on the payment subform that will
bring
up the receipt report, however, I am having difficulty with the criteria
in
the query for the receipt report. Since this payment subform is actuallly
a
sub-subform and is on a tab control, is there a different way of entering
my
criteria? In my query for the receipts, under the PaymentID field, my
criteria was entered as
[forms]![frmCustomers]![sfrmEnrollments]![sfrmPayments]![PaymentID]
But
this isn't working. How do I go about getting it to realize that the
field
PaymentID is on a subform (sfrmPayments) which is on a subform
(sfrmEnrollments) which is on a tabcontrol of a mainform (frmCustomers)?

Thank you in advance for any advice

jubu

 

[jubu]

Thank you - that is exactly what I needed! Worked great!
--
jubu

The tab control is not part of the reference, but you need to include the
".Form" bit to refer to the form in the subform control:
[Forms]![frmCustomers]![sfrmEnrollments].Form![sfrmPayments].Form![PaymentID]

If that still fails, open the main form in design view, and check the Name
of the subform control. It can have a different name than its SourceObject
(the name of the form loaded into it.)

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

An alternative approach would be to leave the criteria out of the query, and
use the WhereCondition of OpenReport instead. Example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
If the button is actually on the inner subform, the code will just use:
strWhere = "[PaymentID] = " & Me.[PaymentID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access 2003 database with several tables: tblCustomers stores
customer information with primary key the CustomerID field. This is
linked
to tblEnrollments in a one-many relationship, and includes a ClassID field
(and several other fields). The combination of the ClassID and the
CustomerID are the primary keys for this table. A third table in my
dilemna
is tblPayments which is linked to tblEnrollments via the ClassID and the
CustomerID. The primary key in tblPayments is the PaymentID field. (The
Enrollments table is also linked to a class table which lists the various
classes available.) I have created a main form, frmCustomers which has
tab
controls on it. The first tab control contains customer information. The
second tab control contains a subform called sfrmEnrollments based on
tblEnrollments. This subform is set up as a single form and contains a
subform of its own called sfrmPayments based on tblPayments. All of the
subforms are correctly linked via the Master/Child links so that when I am
looking at a specific customer I can see just the classes they have
enrolled
for and then I can apply/view the payments for that enrollment.

Here's my question: I would like to be able to print a receipt for a
specific payment. I created a button on the payment subform that will
bring
up the receipt report, however, I am having difficulty with the criteria
in
the query for the receipt report. Since this payment subform is actuallly
a
sub-subform and is on a tab control, is there a different way of entering
my
criteria? In my query for the receipts, under the PaymentID field, my
criteria was entered as
[forms]![frmCustomers]![sfrmEnrollments]![sfrmPayments]![PaymentID]
But
this isn't working. How do I go about getting it to realize that the
field
PaymentID is on a subform (sfrmPayments) which is on a subform
(sfrmEnrollments) which is on a tabcontrol of a mainform (frmCustomers)?

Thank you in advance for any advice

jubu