If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report linked to subform in a subform
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 |
#2
|
|||
|
|||
Report linked to subform in a subform
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jubu" wrote in message ... 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 |
#3
|
|||
|
|||
Report linked to subform in a subform
Thank you - that is exactly what I needed! Worked great!
-- jubu "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jubu" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|