Run Report From Subform (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
Since I have been re-writing my database to use tabbed controls rather than the navigation forms I have run into several issues.

One of which is running my reports from the form in my tabbed control

I worked with my form (frm_reportcenter) and put an unbound combobox (cboFacility) to choose a facility and a command button (cmdWaitingOnVis) to open a report (rep_waitingonvis) and it opens filtered to only the facility I choose in the combobox and a status of "Waiting on Visual Inspection".

It works great... standalone... however when I try to do it from the page (ReportCenterForm) in my tabbed control (TabCtl87) it asks for the parameter "Forms!frm_reportcenter!cboFacility".

Here is the query (qry_facilities) for the combobox on the form frm_reportcenter (cboFacility)(bound column 3):
Code:
SELECT DISTINCT tbl_facilities.ID, tbl_facilities.FacilityNumber, tbl_facilities.FacilityName
FROM tbl_facilities
ORDER BY tbl_facilities.FacilityName;

Here is the report query (qry_waitvis):
Code:
SELECT tbl_auditdata.AuditID, tbl_auditdata.Status, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.RecDate, tbl_auditdata.PartProdDate, TotalReceived/10 AS TenPercent, tbl_auditdata.RecEntryDate
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection") AND ((tbl_auditdata.Facility)=[Forms]![frm_reportcenter]![cboFacility]))
ORDER BY tbl_auditdata.RecDate;

Here is the code behind the command button on the frm_reportcenter (cmdWaitingOnVis):
Code:
Private Sub cmdWaitingOnVis_Click()
    DoCmd.OpenReport "rep_waitingonvis", acViewPreview
End Sub

The page name it is on is: ReportCenterForm
The tabbed control is named: TabCtl87

I am missing something in the WHERE clause to make this work from the tab i'm sure... please help!
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 16:31
Joined
Sep 21, 2011
Messages
14,044
Obviously the control name is different?
On issues like these I tend to populate a TempVar and use that/them as the criteria for the query.
That was before I found out about parametised querydefs.?

Here is one such example
https://access-programmers.co.uk/forums/showthread.php?t=300780&highlight=querydef


However as you are using the report to run the query as opposed to running it directly, the Tempvars *might* be an esier option?


HTH
 

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
Obviously the control name is different?
On issues like these I tend to populate a TempVar and use that/them as the criteria for the query.
That was before I found out about parametised querydefs.?

Here is one such example
https://access-programmers.co.uk/forums/showthread.php?t=300780&highlight=querydef


However as you are using the report to run the query as opposed to running it directly, the Tempvars *might* be an esier option?


HTH

Yea... I don't know how to use TempVars. Is it not possible to fix the code I currently have?
 

June7

AWF VIP
Local time
Today, 08:31
Joined
Mar 9, 2014
Messages
5,423
So if the referenced control is now in a subform, that parameter path will not work. The path would have to be through the subform container control. If the container name was like ctrCenters: [Forms]![frm_reportcenter]![ctrCenters]![cboFacility]

I don't use dynamic parameterized queries. I prefer to pass the criteria to the WHERE CONDITION of OpenReport.

If the button and combobox are both on frm_reportcenter:

DoCmd.OpenReport "rep_waitingonvis", acViewPreview, , Me.cboFacility
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:31
Joined
May 21, 2018
Messages
8,463
If the container name was like ctrCenters: [Forms]![frm_reportcenter]![ctrCenters]![cboFacility]
No. You are missing a .Form in there.
[Forms]![frm_reportcenter].[ctrCenters].Form.[cboFacility]
 

June7

AWF VIP
Local time
Today, 08:31
Joined
Mar 9, 2014
Messages
5,423
Yes. Sorry for oversight. Not needed if referencing field.
 

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
So if the referenced control is now in a subform, that parameter path will not work. The path would have to be through the subform container control. If the container name was like ctrCenters: [Forms]![frm_reportcenter]![ctrCenters]![cboFacility]

I don't use dynamic parameterized queries. I prefer to pass the criteria to the WHERE CONDITION of OpenReport.

If the button and combobox are both on frm_reportcenter:

DoCmd.OpenReport "rep_waitingonvis", acViewPreview, , Me.cboFacility

So if I change the line of code for the command button to this:
Code:
DoCmd.OpenReport "rep_waitingonvis", acViewPreview, , Me.cboFacility
It pops up a parameter box displaying the choice I made in the combobox and asks for input. Say I chose "Leesport" from the combobox... I then type in Leesport in the parameter popup and the report opens correctly filtered.

If I don't do that code and changed the line in the WHERE of the query for the report to:
Code:
SELECT tbl_auditdata.AuditID, tbl_auditdata.Status, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.RecDate, tbl_auditdata.PartProdDate, TotalReceived/10 AS TenPercent, tbl_auditdata.RecEntryDate
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection") AND ((tbl_auditdata.Facility)=[Forms]![frm_reportcenter].[TabCtl87].Form.[cboFacility]))
ORDER BY tbl_auditdata.RecDate;

I still get the parameter popup asking for input and displays Forms!frm_reportcenter.TabCtl87.Form.cboFacility and say I made the same choice as above of "Leesport"... I then type in Leesport in the parameter popup and the report opens correctly filtered.

What is causing it to not just open the report filtered without the parameter popups?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 16:31
Joined
Sep 21, 2011
Messages
14,044
It cannot find that control, that is why.
Is the form open at that time?, are the names the same between parameter and control name?
 

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
It cannot find that control, that is why.
Is the form open at that time?, are the names the same between parameter and control name?

Q: Is the form open at that time?
A: Yes

Q: are the names the same between parameter and control name?
A: No

Here is a screenshot of everything:


The main form is called frm_home.. on that main form is the tabbed control named TabCtl87.

One of the pages in the tabbed control is named ReportCenterForm... the subform on that page is named frm_reportcenter.

On frm_reportcenter I have a combobox named cboFacility (bound column 3) using this query to pull up the facility names from tbl_facilities
Code:
SELECT DISTINCT tbl_facilities.ID, tbl_facilities.FacilityNumber, tbl_facilities.FacilityName
FROM tbl_facilities;
And a command button named cmd_WaitingOnVis that opens a report rep_waitingonvis. The query named qry_waitvis for the report is this:
Code:
SELECT tbl_auditdata.AuditID, tbl_auditdata.Status, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.RecDate, tbl_auditdata.PartProdDate, TotalReceived/10 AS TenPercent, tbl_auditdata.RecEntryDate
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection") AND ((tbl_auditdata.Facility)=[Forms]![frm_reportcenter].[TabCtl87].Form.[cboFacility]))
ORDER BY tbl_auditdata.RecDate;
and the code for the command button named cmdWaitingOnVis is this:
Code:
Private Sub cmdWaitingOnVis_Click()
    DoCmd.OpenReport "rep_waitingonvis", acViewPreview
End Sub

I think I may know what the problem is... the data needing to be displayed resides in the table named tbl_auditdata which has a field named Facility which is short text but I am pulling the facility names from the tbl_facilities maybe?

Here is tbl_auditdata that the qry_waitvis is pulling the information from:


Would it be easier to post a copy of the database? It is already stripped down.

I posted it anyway just in case it helps. Login with user1, user1
 

Attachments

  • Capture3.JPG
    Capture3.JPG
    72 KB · Views: 233
  • Capture4.JPG
    Capture4.JPG
    37.9 KB · Views: 223
  • Test.zip
    846.6 KB · Views: 58

Gasman

Enthusiastic Amateur
Local time
Today, 16:31
Joined
Sep 21, 2011
Messages
14,044
Sorry, I only have 2007, and am unable to open frmHome
However I did manage to open frmReport and the query and then used the builder to build the criteria as
Code:
[Forms]![frm_reportcenter]![cboFacility]
when running the report I was asked for date and time, which I keyed in and got a report.

As the frmReport is on frmHome tab control, I am unsure as to where the date and time would be coming from. Regardless I think it is just about getting the control name correct.

Have you tried using the builder to get the control name. I tend to do that when I use forms if I do not use tempvars.

It is not complaining about the content, just that it cannot find the control. Just like when I run frmReport direct it has no idea as to what Date and Time are?
Bad names for fields/controls as well.


I reckon you need frmHome in there as well.
TBH to make it more flexible, and not rely on a form, I'd probably use the Tempvars approach, then it will work from anywhere, once the Tempvar is set.?


I also note you are using the display text as the bound field.? I would normally use the ID of the record and disply the text field. I always bring my data in as

Bound field (ID)
Display Field 1
Display Field 2 (if needed) etc

Sorry I am unable to help further, if I am helping at all. :D
 
Last edited:

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
Sorry, I only have 2007, and am unable to open frmHome
However I did manage to open frmReport and the query and then used the builder to build the criteria as
Code:
[Forms]![frm_reportcenter]![cboFacility]
when running the report I was asked for date and time, which I keyed in and got a report.

As the frmReport is on frmHome tab control, I am unsure as to where the date and time would be coming from. Regardless I think it is just about getting the control name correct.

Have you tried using the builder to get the control name. I tend to do that when I use forms if I do not use tempvars.

It is not complaining about the content, just that it cannot find the control. Just like when I run frmReport direct it has no idea as to what Date and Time are?
Bad names for fields/controls as well.


I reckon you need frmHome in there as well.
TBH to make it more flexible, and not rely on a form, I'd probably use the Tempvars approach, then it will work from anywhere, once the Tempvar is set.?


I also note you are using the display text as the bound field.? I would normally use the ID of the record and disply the text field. I always bring my data in as

Bound field (ID)
Display Field 1
Display Field 2 (if needed) etc

Sorry I am unable to help further, if I am helping at all. :D

I changed to the code you put above and as before... when just opening frm_reportcenter and choosing a facility... when I click the button it properly generates the report but when I open the frm_home (main form) and then click the tab for the report center which the frm_reportcenter is the subform on that page I get the parameter popup:


I am also not sure why you would get a parameter popup for date and time as that is not even in the query for the report

If you can show me an example of how to use the TempVars for this I would appreciate it.
 

Attachments

  • Capture5.JPG
    Capture5.JPG
    43.3 KB · Views: 226

Minty

AWF VIP
Local time
Today, 16:31
Joined
Jul 26, 2013
Messages
10,354
Just to clarify something here - Tabbed pages are simply extensions of the form, they are not SubForms. You refer to controls on tab pages like you would any other control on a form.

That said the expression needed in the query is
Code:
Forms![frm_home]![ReportCenter].Form![cboFacility]
 

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
Thank you Minty... I was just reading something that had me on that same track but you posted it before I got it right.

Thank you everyone for your input and advice.
 

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
Just to clarify something here - Tabbed pages are simply extensions of the form, they are not SubForms. You refer to controls on tab pages like you would any other control on a form.

That said the expression needed in the query is
Code:
Forms![frm_home]![ReportCenter].Form![cboFacility]

My office 2013 must have taken an update or something... this used to work and now it pops up the parameter box again.

Here is the query that worked last week:
Code:
SELECT tbl_auditdata.AuditID, tbl_auditdata.Status, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.RecDate, tbl_auditdata.PartProdDate, TotalReceived/10 AS TenPercent, tbl_auditdata.RecEntryDate
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection")) AND ((tbl_auditdata.Facility)=Forms![frm_home]![ReportCenter].Form![cboFacility] OR Forms![frm_home]![ReportCenter].Form![cboFacility] IS NULL)
ORDER BY tbl_auditdata.RecDate;
 
Last edited:

June7

AWF VIP
Local time
Today, 08:31
Joined
Mar 9, 2014
Messages
5,423
What I did:

1. remove dynamic parameter from query qry_waitvis

2. change code:
DoCmd.OpenReport "rep_waitingonvis", acViewPreview, , "Facility='" & Me.cboFacility & "'" (apologies, due to careless oversight in my earlier post, suggestion did not provide complete WHERE argument)

3. select Arcadia in facility combobox

4. click button to open report

5. just OK through the date and time prompts because I am being lazy (Why these prompts?)

6. report displays one record for Arcadia
 
Last edited:

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
What I did:

1. remove dynamic parameter from query qry_waitvis

2. change code:
DoCmd.OpenReport "rep_waitingonvis", acViewPreview, , "Facility='" & Me.cboFacility & "'" (apologies, due to careless oversight in my earlier post, suggestion did not provide complete WHERE argument)

3. select Arcadia in facility combobox

4. click button to open report

5. just OK through the date and time prompts because I am being lazy (Why these prompts?)

6. report displays one record for Arcadia

I did exactly as you said and it worked except for one detail. The query allowed to generate a report for the facility chosen and a status of waiting on visual inspection or if you left the combobox blank it would generate a report with all facilities that had records with a status of waiting on visual inspection.

Your fix properly displayed the records with the status and the facility chosen but doesn't display anything if the combobox is left blank.

On a side note I have no idea why you are getting date and time prompts... I do not get those as there is no date and time in the query.
 

June7

AWF VIP
Local time
Today, 08:31
Joined
Mar 9, 2014
Messages
5,423
Right, now explore code that conditionally builds WHERE argument depending on whether or not value in combobox. Review http://allenbrowne.com/ser-62.html

Really don't need a different query object for each report when they actually have the same data source.

The correct parameter if you really want that approach:

Forms!frm_home!ReportCenter.Form!cboFacility

Found reason for the Date and Time prompts - missing Excel and Outlook libraries. Likely you are running later version of Access than I have.
 
Last edited:

psyc0tic1

Access Moron
Local time
Today, 11:31
Joined
Jul 10, 2017
Messages
360
Right, now explore code that conditionally builds WHERE argument depending on whether or not value in combobox. Review http://allenbrowne.com/ser-62.html

Really don't need a different query object for each report when they actually have the same data source.

The correct parameter if you really want that approach:

Forms!frm_home!ReportCenter.Form!cboFacility

Found reason for the Date and Time prompts - missing Excel and Outlook libraries. Likely you are running later version of Access than I have.

I had already tried removing the brackets but had the same parameter popup result. Very strange that this worked last week but doesn't this week.

Anyway I changed the whole thing to use 2 comboboxes. One for selecting the facility and the other for selecting the type of report desired.

The only snafoo is that I still cannot leave the facility choice combobox blank to view all records in the report. The reports in the list that do not need to have a facility chosen will not work either.
Picture of the form on tab page index 7:


The query behind the first combobox (cboFacility):
Code:
SELECT DISTINCT tbl_auditdata.Facility
FROM tbl_auditdata
ORDER BY tbl_auditdata.Facility;

The query behind the second combobox (cboReports):
Code:
SELECT [MSysObjects].[Name] 
FROM MsysObjects 
WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764
ORDER BY [MSysObjects].[Name];

The code behind the command button (cmdGenerateReport):
Code:
Private Sub cmdGenerateReport_Click()
   If Not IsNull(cboReports) And cboReports <> "" Then
     DoCmd.OpenReport cboReports, acViewReport, , "Facility='" & Me.cboFacility & "'"
   Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If
   cboReports = ""
End Sub

I could really use some help making this code allow for a null value in the first combobox (cboFacility) so I can generate either complete reports of all relevant records or relevant to a facility... or on the reports that do not require a facility... just generate all records.
 

Attachments

  • Capture.JPG
    Capture.JPG
    17 KB · Views: 183

Gasman

Enthusiastic Amateur
Local time
Today, 16:31
Joined
Sep 21, 2011
Messages
14,044
What I tend to do in a situation like that is add an ALL option with the key of 0 to the combo list, then if key(s) is(are) 0 just run the report, else run the report with filter criteria.?
So the query for the combo would be a union query along the lines of
Code:
Select 0 AS ID, "All" AS Description
UNION
Select ID, Description FROM tblComboSource
ORDER BY ID

HTH
 

Users who are viewing this thread

Top Bottom