Referring to subreport control Results

In a report, with a subreport, I have created a few controls that refer to values on the subreport. I now want to use those values in other calculations on the main report. First I named each of the controls that are referencing the subreport. Then I tried to use those values to calculate a new value on the main report: =Sum([Total Net]+[Ref Change Net]) The Ref Change Net is the refered control. The report is not recognizing any of these fields. What should I do to use the refered values?


I have this

A report name Etat2 and a report named Etat3

Etat3 is a subreport in Etat2.

Both reports have their own query and what I'm trying to do is that one of the condition in the query of Etat3 is related to a control (a textbox) in Etat2.

I've tried referring like I do with form

WHERE Maitre=[Reports]![Etat2].[Etat3].Reports!txt1;

But it doesn,t seem to work since it always ask me to enter the value of that string each time I start the reports.

Does somebody know if there's another way to do it?

I have a report and on that report is a subreport.
The report record source is a query that references field DocID but is not on the report's on the subreport, and the filter is created from the code below from mfilter.....

the subreport record source query references both the DocID and the InspectNum fields and has both fields on the subreport to print.

In my code I create a filter to only print certain records of the report.
Here's the code behind the print button on the form.....

Dim stDocName As String
Dim mfilter As String
Dim ctl As Control
Dim varItm As Variant, intI As Integer
stDocName = "rptDeficiencies"

For Each varItm In ctl.ItemsSelected
mfilter = "DocID = '" & Me.DeficiencyRpt.Column(0, varItm) & "'" & " and InspectNum = " & Me.DeficiencyRpt.Column(1, varItm)
DoCmd.OpenReport stDocName, acPreview, , mfilter
Next varItm

Here's my problem. When I run this code with the mfilter equaling only DocID , everythign works great...which by the way is a text field that's why there's quotes in the code.

When I add the InspectNum which is a long integer....I get a pop up box that says "Enter Parameter Value" InspectNum

Do I need the mfilter on the subreport or on the report? And if so, how do I get it to the subreport thru code? Or do I need to reference the subreport in the mfilter clause?

Any help would be greatly appreciated since I'm stumped.


i have a subreport in a report and im trying to get the results of the subreport in my report. The problem is that i want to get the sub total that are calculated via the loop of Sorting and Grouping. So i want the result of 3 sums that are defined in the same control (text Box). I can get the first result but i dont know how to access the second and third result.

can anybody help ?

Thx in advance


Hi, everybody.

Well, I think what i will ask you is something that I'm looking for an answer, but i didnt' find it till now. And I know this is not a new question but...

I have a form that includes some text boxes and combo boxes. The user fills these controls to build a report based on that group of criteria. on this form there is a button where i'm building a VBA code on the click_button command.

The report has one subreport. They are perfectly linked and when the user chooses a textbox or a combox refering to main report, the filter works ok.
The problem is with the controls that try to filter the subreport.

The reports don't have a query.

And this is the code i'm building:

Private Sub botrel_Click()
On Error GoTo Err_botrel_Click

Dim stDocName As String
Dim strWhere As String
Dim lngLen As Long

' this one works perfectly (it refers to the main report)
If Not IsNull(Me.ProNum) Then
strWhere = strWhere & "([ProNum] Like ""*" & Me.ProNum & "*"") AND "
End If

' This one is my problem:
' I verified all the names of the reports, the are correct!!!

If Not IsNull(Me.imovnat) Then
strWhere = strWhere & "(Reports![Rel_Pro - teste]![Tbl_Hip subrelatório].Report![HipNatBem]= """ & Me.imovnat & """) AND "
End If

' I know that on the final condition i'm adding criteria for the main report
' and for the subreport and i think this is the point!!!

lngLen = Len(strWhere) - 5
If lngLen

I have a Report linked with a SubReport. I would like to cancel viewing this Report if my SubReport Has No Data.
i tried this code but the report just shows up. Any Ideas?

	Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Trapper
If Reports![qryRepCurOrgProjsSR].Report.HasData = 0 Then
Cancel = True
End If
MsgBox Err.Description
Exit Sub
End Sub

Ok i got an error message. Yoy entered an expression that has an invalid Reference to the hasData property.

Ive tried using a text bpx on my main report and putting a control source as


Then in my Report Open Event i put the code below. But it fails me still.

	If trim(Me.MyText) = "Nothing" Then
MsgBox "The Report Is Cancelling....."
Cancel = True
Exit Sub
End If

Hi All,

I need some help please. See attached file.

Basically, we use the database to produce quotes for ramp kits. Each quote will have a number of different kits which make up the ramp. When the quote is accepted, its status is changed to ordered and I need to extract data regarding stock commitment for all accepted quotes

So Firstly I have the Table 'Quotes' which stores the client info, discounts, quote no, status etc.

Then I have 'Quotes Line Items METAL' which stores each item line from the quote linked to the 'Quotes' Table via QuoteNo and Autonumber.

Finally I have the table 'KITSMETALtbl' which stores the bill of materials (all parts required to make up the kit) and is linked to Quotes Line Items METAL table via ItemID.

The StockCommitment report then pulls out the top line info from the Quotes table. This has a subreport (StockCommitmentKITOverview) from Quotes Line Items METAL listing all the KITS on the quote. A further subreport (StockCommitmentBreakdown) then lists the required parts for each quote item line.

I am trying to sum up the breakdown of all these parts so we can assess all the stock that is committed.

Spent hours trying to work out how to sum these up. I wondered if there is a way using VBA or by changing the structure. Help much appreciated.

Cheers Attached Files (160.0 KB, 2 views) Reply With Quote 09-21-2011, 01:22 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,129 I give up trying to figure out aggregate calcs by references to report/subreport/subsubreport objects and controls. Do another subreport on the main form for just aggregate calcs.

Here is alternative. Doesn't use subreports. Change main report RecordSource to be a join of all three tables.
SELECT KITSMETALtbl.*, [Quotes Line Items METAL].*, Quotes.*
FROM KITSMETALtbl RIGHT JOIN ([Quotes Line Items METAL] INNER JOIN Quotes ON [Quotes Line Items METAL].QuoteNo = Quotes.Autonumber) ON KITSMETALtbl.ID = [Quotes Line Items METAL].ItemID
WHERE ((Not (KITSMETALtbl.ID) Is Null))
ORDER BY Quotes.Client, KITSMETALtbl.ID;

Then use report Grouping/Sorting with summary calcs in footers. Makes the aggregate calcs easier although having some frustration with sorting.

I have a main form called Main, and then a subform with a control of ctrYearlySubForm. In the subform I have a combo box, Yearcmb, in which you can choose a year dependent on the name in the main form. I have created a query that I want to use the year from the combo box to be criteria to select data. I have tried using [Forms]![Main].[ctrYearlySubForm].[Form].[Yearcmb] in the criteria portion but that doesnt work. I am having troubles figuring out why that isnt working and even if you can do what im trying to do. Any suggestion will be grealty appreciated!

Also, on a side question, on the report I have I have query that selects information but when the informaiton is not available the subreport from the query does not show up. I would like it to show that always because it has a tag describing the information, for example location. I was wondering how I could show that all the time. I was going to just take the tag outside the subreport but some of the information when it isnt filled shifts the formatting which I do not want.

Another question I have is when I used an update query to import data, the numbers instead of 15.45 imported at 15. I was wondering why they rounded the numbers?

I have created a MS Access 2007 report that includes 6 subreports (subtotals) within it. I was able to use a named field "RowNum" with control source as =1, running sum = Over All and refer "RowNum+1" = RowNumT as subtotal row number (works perfect) for the first subreport1.

However, when referring to subreport1!RowNumT to begin displaying row number is subreport2 I am getting a calculation error.

My goal is to have number 1 to 58 displaying on left side of the report and columns A to P at the top for reference points or guide to the report.

Can this be done having 6 subreports within one report?

I would greatly appreciate if someone can take a look at my attached access database (2003 version).

My main form has a list box with names. By selecting the name I then click on the resecarchcategories subform to enter data for that person. After entering a first record and want to add another one by using 'add another' this button breaks into debugging mode. There is something wrong either with my 'add another' button or my 'next' button functionality.

I would greatly appreciate your reply.
Debbie Attached Files (338.3 KB, 4 views) Reply With Quote 01-10-2011, 01:31 PM #2 ConneXionLost Simulacrum Windows XP Access 2003 Join Date Jan 2010 Location Victoria, Canada Posts 291 Hi,

A typical arrangement for a subform is to be embedded into a form using the "Subform/Subreport" control. You don't appear to be doing this. Instead, you have a textbox you're trying to coerce into acting as a subform with VBA. Additionally, your Record Source for frmResearchCategory is a query with two tables.

If you were to return to the "typical" approach, you might be able to take advantage of the Master/Child relationship inherent in a form/subform, instead of trying to recreate it with VBA.


I have a subreport in the details section of the main report. This means that for every record in the main report, a subreport is shown. I have calculated a sum in the sub report by placing a control in the footer section of the sub report, and have placed a new control on the main form, refering to the calculated sum field in the sub report. Now I want to sum all the calculated sum controls from the sub forms, to get the emtire sum. How can I do this?

Thanks in advance

I have read through old posts regarding referencing calculated fields in subreports using either a direct reference (=reports.[reportName].[CalcFieldName]) or a Hasdata reference, but I find no solution to my present dilemma. My problem, I think, is that I want the unbound control that is referencing the subreport calculation to appear in the Page Footer of the main report. Either a direct reference or a HasData function will work on this if the unbound control appears in the group footer or detail section. Is there something in A2k that prevents this procedure from being possible within a Page Footer, or is there something I am missing? I have also tried placing the unbound control in the group footer, making it invisible, and creating another unbound control to reference that control for placement in the page footer...nothing has worked.

Ultimately, I'm trying to create footnotes to records in the report...I've been working on this off and on for weeks, and found no solution - any advice is much appreciated. If I need to supply more specific info, lmk.

I need to conditionally format the value of a text control on my main report based upon a value returned in my subreport.

On my main report I have a text box with the following in the Control Source property:
=IIf(Reports!r_Index_Info_Sub!txtTabCount>0,"Index es","")

The control Reports!r_Index_Info_Sub!txtTabCount is returning a valid numeric value.

The control on my main report contains #Name?.

How can I properly reference the value in Reports!r_Index_Info_Sub!txtTabCount?

I've got several reports that contain subreports. Yesterday while some coworkers were exporting my reports to PDF using Adobe Professional, something went amiss with a handful of reports/subreports.

I've got 3 reports/subreports that at some point during their exporting, seem to have been phantom-deleted. Meaning, on the Modified/Created portion of the detail list view next to the report it shows 12:00:00 AM. When you click one of these reports, it gives the error:

The report name 'reportname' you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.

These are the 3 subreports to the main report. When you click the main report, it returns the error:

17 errors occurred when you loaded the form or report. You loaded a form or report that has controls or properties that Microsoft Office Access doesn't recognize and will ignore.

When you click OK, it doesn't do anything. I'm unable to open the report to design or preview or anything.

This is the first time i've seen anything like this. Being that my database utilizes many reports (80) for performance reporting for several employees, I'd like to find the root cause of this problem to make sure it doesnt happen on a larger scale and really cause some havoc.

Thanks for any help.

I am writing because I need help implementing the instructions below. Look further down this post and you'll see my interpretation of the instructions as well as the code I am trying to utilize.

Quote: Put two subform controls on one unbound form.
The first subform control should contain the form for
tblTable1 and the second subform control should contain the
form for tblTable2.
The name of the subform control is not necessarily the same
as the name of the form in the subform control. Select the
subform control, open its properties sheet, and examine the
Name property. Ensure you get the names of the subform
controls in the constants at the top of the code.

You need a primary key field in each table. I used
Autonumber fields with the field names ID1 and ID2.

I used SQL statements as the recordsources of the forms for
tables 1 and 2. Its easy to adjust SQL statements to select
just one record in subform control 1 and three records in
subform control 2.

Below is the code I used in the module behind the unbound
Copy and paste the code into an unbound form.
Adjust the constants at the top to reflect your solution.

My Interpretation
Here is the steps I took to implement the instructions above, however I seem to have made an error as something is not working properly. (Assume I already have tblTable1 and tblTable2, both of which have Autonumber fields ID1 and ID2)

Forms / Form Wizard / tblTable1 / OK / Tablefield1 / > / Next / Columnar / Next / Standard / Next / what title do you want for your form? SubFrmCtrlTable1 / Finish
Forms / Form Wizard / tblTable2 / OK / Tablefield2 / > / Next / Columnar / Next / Standard / Next / what title do you want for your form? SubFrmCtrlTable2 / Finish
Forms / New / Design view / OK / toolbox / subform subreport / subform wizard / SubFrmCtrlTable1 / next / what name would you like for your subform? mstrcSFrm1 / Finish
Forms / New / Design view / OK / toolbox / subform subreport / subform wizard / SubFrmCtrlTable2 / next / what name would you like for your subform? mstrcSFrm2 / Finish
mstrcSFrm1 / properties / record source / mstrcSQL1 / close properties
mstrcSFrm2 / properties / record source / mstrcSQL2 / close properties
command button / command button wizard / cancel / command button properties / Event / on click / code builder / paste in the code below.

Option Compare Database 
Option Explicit 

' Requires reference to Microsoft DAO 
' (In VBA editor, Tools > References > Microsoft DAO.) 

' Assumes: 
' Main form is unbound. 
' Main form contains 2 subform controls. 


' Store names of subform controls 
' containing 1st and 2nd subforms: 
Private Const mstrcSFrm1 As String = "SubFrmCtrlTable1" 
Private Const mstrcSFrm2 As String = "SubFrmCtrlTable2" 

' Store RecordSource SQL statements 
' for 1st and 2nd subforms: 
Private Const mstrcSQL1 As String = "SELECT tblTable1.* " _ 
& "FROM tblTable1;" 
Private Const mstrcSQL2 As String = "SELECT tblTable2.* " _ 
& "FROM tblTable2;" 

' Store names of primary key fields 
' for 1st and 2nd tables: 
Private Const mstrcPKFieldName1 As String = "ID1" 
Private Const mstrcPKFieldName2 As String = "ID2" 

Private Sub cmdSelect_Click() 

Dim objSF1 As Access.SubForm 
Dim objRS1 As DAO.Recordset 

Dim objSF2 As Access.SubForm 
Dim objRS2 As DAO.Recordset 

Dim strFirstBookMark1 As String 
Dim strFirstBookMark2 As String 

' To store primary keys: 
Dim lngPK1 As Long 
Dim lngPK2a As Long 
Dim lngPK2b As Long 
Dim lngPK2c As Long 

Dim lngRecordCount1 As Long 
Dim lngRecordCount2 As Long 

Dim lngRecord1 As Long 
Dim lngRecord2a As Long 
Dim lngRecord2b As Long 
Dim lngRecord2c As Long 

Dim strSQL As String 

On Error GoTo Error_cmdSelect_Click 


Set objSF1 = Me.Controls(mstrcSFrm1) 
objSF1.Form.RecordSource = mstrcSQL1 
Set objRS1 = objSF1.Form.Recordset 
With objRS1 
If .RecordCount > 0 Then 

' After the form is opened, the first 
' record may not be the current record, 
' therefore: 
strFirstBookMark1 = .Bookmark 

' Get accurate record count: 
lngRecordCount1 = .RecordCount 

' Calculate and move to random 
' record number: 
lngRecord1 = Int(lngRecordCount1 * Rnd) 
.Move lngRecord1, strFirstBookMark1 

' Get primary key of current record: 
lngPK1 = .Fields(mstrcPKFieldName1).Value 

' Show only the current record: 
strSQL = Left(mstrcSQL1, Len(mstrcSQL1) - 1) 
strSQL = strSQL _ 
& " WHERE " & mstrcPKFieldName1 _ 
& "=" & lngPK1 & ";" 
objSF1.Form.RecordSource = strSQL 

MsgBox "Cannot move in main table. " _ 
& "No Records." 
End If 
End With 


Set objSF2 = Me.Controls(mstrcSFrm2) 
objSF2.Form.RecordSource = mstrcSQL2 
Set objRS2 = objSF2.Form.Recordset 
With objRS2 
If .RecordCount > 0 Then 

' After the form is opened, the first 
' record may not be the current record, 
' therefore: 
strFirstBookMark2 = .Bookmark 

' Get accurate record count: 
lngRecordCount2 = .RecordCount 

' Calculate and move to 1st random 
' record number: 
lngRecord2a = Int(lngRecordCount2 * Rnd) 
.Move lngRecord2a, strFirstBookMark2 

' Get primary key of current record: 
lngPK2a = .Fields(mstrcPKFieldName2).Value 

' Calculate and move to 2nd random 
' record number: 
lngRecord2b = Int(lngRecordCount2 * Rnd) 
.Move lngRecord2b, strFirstBookMark2 

' Get primary key of current record: 
lngPK2b = .Fields(mstrcPKFieldName2).Value 

' Calculate and move to 3rd random 
' record number: 
lngRecord2c = Int(lngRecordCount2 * Rnd) 
.Move lngRecord2c, strFirstBookMark2 

' Get primary key of current record: 
lngPK2c = .Fields(mstrcPKFieldName2).Value 

' Show only the current record: 
strSQL = Left(mstrcSQL2, Len(mstrcSQL2) - 1) 
strSQL = strSQL _ 
& " WHERE " & mstrcPKFieldName2 _ 
& " IN (" & lngPK2a _ 
& ", " & lngPK2b _ 
& ", " & lngPK2c & ");" 
objSF2.Form.RecordSource = strSQL 

MsgBox "Cannot move in sub-table. " _ 
& "No Records." 
End If 
End With 


Set objRS2 = Nothing 
Set objSF2 = Nothing 

Set objRS1 = Nothing 
Set objSF1 = Nothing 
Exit Sub 


MsgBox "Error No: " & Err.Number _ 
& vbNewLine _ 
& Err.Description, _ 
vbOKOnly + vbExclamation, _ 
"Error Information" 

Resume Exit_cmdSelect_Click 

End Sub 

Private Sub cmdShowAll_Click() 

Dim objSF1 As Access.SubForm 
Dim objSF2 As Access.SubForm 

Set objSF1 = Me.Controls(mstrcSFrm1) 
Set objSF2 = Me.Controls(mstrcSFrm2) 

objSF1.Form.RecordSource = mstrcSQL1 
objSF2.Form.RecordSource = mstrcSQL2 


Set objSF1 = Nothing 
Set objSF2 = Nothing 
Exit Sub 


MsgBox "Error No: " & Err.Number _ 
& vbNewLine _ 
& Err.Description, _ 
vbOKOnly + vbExclamation, _ 
"Error Information" 
Resume Exit_cmdShowAll_Click: 

End Sub 

Private Sub Form_Load() 

Dim objSF1 As Access.SubForm 
Dim objSF2 As Access.SubForm 

' Put SQL statements into RecordSource 
' property of forms in SubForm controls. 

Set objSF1 = Me.Controls(mstrcSFrm1) 
objSF1.Form.RecordSource = mstrcSQL1 

Set objSF2 = Me.Controls(mstrcSFrm2) 
objSF2.Form.RecordSource = mstrcSQL2 


Set objSF2 = Nothing 
Set objSF1 = Nothing 
Exit Sub 


MsgBox "Error No: " & Err.Number _ 
& vbNewLine _ 
& "Error Description:" & vbNewLine _ 
& Err.Description, vbOKOnly + vbExclamation, _ 
"Error Information" 
Resume Exit_Form_Load 

End Sub 

Private Sub Form_Open(Cancel As Integer) 


End Sub

I'm trying to reference a field in a subform to assist a query I'm writing on the form to populate a list box.

The help info is not very helpful in assisting me.
taken from the Access 97 help files:

To refer to a subform or subreport
Refer to the subform or subreport control on the form or report that contains the subform or subreport, then use the Form or Report property of the control to refer to the actual subform or subreport.

· Type the identifier for the form that contains the subform, followed by the name of its subform control, the . (dot) operator, and the Form property. For example, the following identifier refers to the Orders Subform subform on the Orders form:

Forms![Orders]![Orders Subform].Form


I would assume that to reference a field within that subform, you simply add on the field name. This does not work.

Any ideas?


I have something stange (to me) going on.

I have a report that contains a subreport. The report source is a query. Two of the controls, both are derived by combining other fields, are group headers on the report. IDquoteAll is a group header and SailTypeAll is a subgroup header. The subreport is linked as child=IDquote;SailType to parent=IDquoteAll;SailTypeAll.

When I open the report in Print Preview, all is perfect.

When I open it in Report View, I get the message "Microsoft can't find the field IDquote referred to in your expression". I click ok then get the same message for SailType. The report displays correctly, but the messages keep coming.

Using A2007 with W7. any help appreciated.

I created a subReport called "Other Status:" In it I have a field called Weigh_Net and in the
SubReport Footer I have a Text box called TEXT6 with =SUM([Weigh_Net]/2000) - that works.

Then in the Main Report Footer I have a textbox with:
=Sum([Reports]![MJs Report]![Other Status:]![Text6])

but I keep getting 0 as a results.

I remembered that Pat has said not to reference the Control but to sum the formula so I also

=Sum([Reports]![MJs Report]![Other Status:]![Weigh_Net])

in a textbox in the Report Footer of the Main report but I still get 0

What am I doing wrong?

Hi everyone

I have a database that records purchase orders. The orders can be raised in one of 3 currencies - Sterling, Euros & US Dollars. The underlying field has been set to a general number and NOT currency as I have a seperate field (combo box) that denotes the currency.

Now a user can run a report that list all the orders and their currency values against a job that they choose and this works fine. The report itself has no sub reports and is grouped on 'Currency_Of_Order'. What I would like to do is take it one stage further and get the report to actually convert the currencies into their sterling equivalent. What I don't want to have is subreports for each of the different currencies so I'm thinking of having a calculated control in the underlying query which will convert the currency. This has posed 2 problems which is making my head spin.

1) Can I use a nested IIf function so as to test the currency? What I mean is if it's 'Euros' Then convert, if it's US Dollars then convert. If it's neither of those then it's obviously sterling

2) I want the user to be able to overide the the default conversion rate which is stored in a table. I'm using an input box which will store the value in a public variable. The question is how do I reference this public variable in my calculation in the query?

Any help much appreciated

hey, here is my setup

i have a GENERAL table with medical patients basic information.
then i have multiple other tables (medications, visits, diagnosis, referring doctors), all linked by HistNum.

i have a form that opens a report filtered to a single patient and is going to display all of these tables. there is the main information on top, and then a subreport for the reffering doctor (this is all i have so far)

in the subreports properties, i have the Master/Child links set correctly by HistNum but nothing appears.. however, if i change it to ID, which is an irrelevant field in both tables, information does appear..

also, in an earlier thread where i ask about how to eliminate the whitespace left behind from cells of the table that are missing, i was told to "set the height of the controls to a negligible height and the can grow to yes", then the missing lines should be eliminated. for this, do all my fields i want displayed have to be part of 1 big text box, or multiple? (right now i use multiple)

what does this person mean by a negligible height?

heres a screenshot of the report

Not finding an answer? Try a Google search.