Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2018, 06:07 AM   #1
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Run Report From Subform

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!

__________________
Richard
Access 2013, Windows 7 64 bit

Last edited by psyc0tic1; 08-15-2018 at 07:25 AM.
psyc0tic1 is offline   Reply With Quote
Old 08-15-2018, 06:19 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,574
Thanks: 307
Thanked 401 Times in 386 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Report From Subform

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/for...light=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
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 08-15-2018, 12:17 PM   #3
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Re: Run Report From Subform

Quote:
Originally Posted by Gasman View Post
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/for...light=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?

__________________
Richard
Access 2013, Windows 7 64 bit
psyc0tic1 is offline   Reply With Quote
Old 08-15-2018, 12:32 PM   #4
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 805
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Run Report From Subform

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
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-15-2018 at 12:40 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
psyc0tic1 (08-20-2018)
Old 08-15-2018, 05:20 PM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 567
Thanks: 7
Thanked 131 Times in 129 Posts
MajP will become famous soon enough
Re: Run Report From Subform

Quote:
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]
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
psyc0tic1 (08-20-2018)
Old 08-15-2018, 08:51 PM   #6
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 805
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Run Report From Subform

Yes. Sorry for oversight. Not needed if referencing field.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 08-20-2018, 05:08 AM   #7
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Re: Run Report From Subform

Quote:
Originally Posted by June7 View Post
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?

__________________
Richard
Access 2013, Windows 7 64 bit

Last edited by psyc0tic1; 08-20-2018 at 05:21 AM.
psyc0tic1 is offline   Reply With Quote
Old 08-20-2018, 05:52 AM   #8
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,574
Thanks: 307
Thanked 401 Times in 386 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Report From Subform

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?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 08-20-2018, 07:03 AM   #9
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Re: Run Report From Subform

Quote:
Originally Posted by Gasman View Post
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
Attached Images
File Type: jpg Capture3.JPG (72.0 KB, 89 views)
File Type: jpg Capture4.JPG (37.9 KB, 94 views)
Attached Files
File Type: zip Test.zip (846.6 KB, 12 views)
__________________
Richard
Access 2013, Windows 7 64 bit
psyc0tic1 is offline   Reply With Quote
Old 08-20-2018, 07:29 AM   #10
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,574
Thanks: 307
Thanked 401 Times in 386 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Report From Subform

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 08-20-2018 at 07:34 AM.
Gasman is offline   Reply With Quote
Old 08-20-2018, 07:40 AM   #11
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Re: Run Report From Subform

Quote:
Originally Posted by Gasman View Post
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.
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.
Attached Images
File Type: jpg Capture5.JPG (43.3 KB, 82 views)
__________________
Richard
Access 2013, Windows 7 64 bit
psyc0tic1 is offline   Reply With Quote
Old 08-20-2018, 07:47 AM   #12
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,574
Thanks: 307
Thanked 401 Times in 386 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Report From Subform

I believe you need something along the lines of

Code:
Forms![frmHome]
at the start.?

You need the control name that holds the subform. Sometimes that is the same as the subform name, but not always.?
See post 5 by MajP for syntax, or look at http://www.utteraccess.com/wiki/Refe...ls_On_Subforms
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
psyc0tic1 (08-20-2018)
Old 08-20-2018, 08:40 AM   #13
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,680
Thanks: 138
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Run Report From Subform

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]
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
psyc0tic1 (08-20-2018)
Old 08-20-2018, 08:50 AM   #14
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Re: Run Report From Subform

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.
__________________
Richard
Access 2013, Windows 7 64 bit
psyc0tic1 is offline   Reply With Quote
Old 09-05-2018, 10:01 AM   #15
psyc0tic1
Access Moron
 
Join Date: Jul 2017
Posts: 351
Thanks: 167
Thanked 7 Times in 4 Posts
psyc0tic1 is on a distinguished road
Re: Run Report From Subform

Quote:
Originally Posted by Minty View Post
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;

__________________
Richard
Access 2013, Windows 7 64 bit

Last edited by psyc0tic1; 09-05-2018 at 11:06 AM.
psyc0tic1 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
subform in report PicassoB Reports 2 03-08-2012 10:23 AM
Subform to report? thsoundman Reports 9 09-25-2007 09:21 AM
Report subform jomuir General 8 07-06-2007 05:07 AM
report from subform sadie Reports 2 10-30-2003 08:04 AM
Report/Subform - can this be done? whtz Reports 0 05-18-2001 09:24 AM




All times are GMT -8. The time now is 12:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World