Reports :: Setting Subreport Source Object To A Report In A Collection
Apr 6, 2013
I am trying to join a number of reports into one report. I have a generic report which displays a different dataset given the user's choice on a form. I created a collection where I can store multiple instances of this report (called mcolReportInstances) - this works just fine.
I was looking to combine all the reports in the collection into one report. To that end, I have created a report with a number of subreport controls but with no sourceobject. In the On_Open event of this blank report, I am trying to set the source object of the subreport to one of reports in my collection:
I have a series of reports that are built to replicate the order forms of our suppliers. On each I have a text box for Air Freight that I only want visible when the airfreight ID is ticked.
I have this working fine using an if statement on the Load Event of the reports. However when I use the OutputTo command in VBA it seems to bypass the Load Event. Any event that is triggered using OutputTo.
I have also tried setting the .visible in VBA but can't get the coding to work as I am using a variable report name depending on the SupplierName and when I try to use the Reports! nomenclature it will not accept the Report name being a variable (ie. Reports!ReportName![Airfreight].Visible where ReportName is a variable).
I have a main report with one subreport. The main report contains details of a payment being made to a supplier. There is the total amount being paid, and the cheque number being transferred. There is also a field called payref which is made invisible. This is used to link up with the subreport. The amount is the total of several invoices for which payment is being made.
The subreport contains details of each of a number of the invoices being settled. The subreport is linked to the main report via a common field called pay-ref, and the subreport is 'visible'. The subreport prints when placed in the page header section of the main report. However when there is a large number of invoices being shown in the subreport only a certain number appear. It appears that the subreport can only hold a certain number.
When the subreport is put, as it should, in the detail section of the main report, it does not appear at all in the main report. I have no clue why and I have made several reports like this one before.
linking a report and a subreport (chart). I have uploaded my sample DB. I'm trying to chart all the individuals and three fields for each Home. Both report and subreport are using the same query for the record source. My charts are blank "All_Homes_Individual_Totals". Just use date range 05/01/14, 06/24/14 on my Main form page for the data.
My second question is how would you create a chart to pull by each individual with their monthly totals per Home using "qry_Community_ Inclusion_ All_Homes" with a report and subreport (chart) as above. Report would show the individual's totals by month for each home.
I am trying to get my totals from my subreports, and dividing the number by 2 or 4, and put the new number on the main report.
for Operator/Trainer Productivity, I used: =([rptEmployEvaluationOperator subreport].[Report]![OPTotal]+[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal])/2
for Auditor/Trainer Productivity, I used: =([rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor2 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor3 subreport].[Report]![OPTotal]+[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal])/4
These both work if there are values in all totals subreports. When one of them might not have a total, I get an error message.
I have a report that has two sub reports one to report deposits and the other to report payments.
The report prints fine if there are records in both sub reports, however when the payments sub report is blank then the report hangs and won't print to pdf at all.
I want to put a company logo in my report ( object/picture) but when I send the report via w-mail or link it to a word document it disappears. is it not possible for it to stay in the report.
I have an report that uses name paramaters.this is the sql for the report
Code:
PARAMETERS [whatCompany] Text ( 255 ); SELECT tblInvoices.ClientCompany, tblInvoices_Details.Charge, Sum(tblInvoices_Details.Hours) AS SumOfHours, tblInvoices.InvoiceID FROM tblInvoices INNER JOIN tblInvoices_Details ON tblInvoices.InvoiceID = tblInvoices_Details.InvoiceID GROUP BY tblInvoices.ClientCompany, tblInvoices_Details.Charge, tblInvoices.InvoiceID HAVING (((tblInvoices.ClientCompany)=[whatCompany]));
How do I pass the paramaters to the report? I've tried several different ways but can't get it to work
Code: Dim stdocname As String Dim stLink As String stdocname = "RptWithParm" stLink = "ClientCompany = " & "'" & Me.lstCustomer & "'" 'Using the field name doesn't work DoCmd.OpenReport stdocname, acViewReport, , stLink
'When I try to set the value of the paramater that doesn't work either stLink = "[whatCompany] = " & "'" & Me.lstCustomer & "'" 'using the paramater name doesn't work DoCmd.OpenReport stdocname, acViewReport, , stLink
I know I could use the value of the form in the criteria like this
Code: HAVING (((tblInvoices.ClientCompany)=[Forms]![frmTesRptParm]![lstCustomer]));
If I use the list box as the criteria I want to be able to use reports in other than one place, plus there are over 80,000 records and it'll run faster if I set the criteria before the report opens instead of setting a filter after it opens to only show up to about 100.
Everytime i make a report in Access, first thing i do is build a query and then use it as a record source. I try the other way, I go to create report design directly and do the drag and drop of fields.
I have a database that reports activities by region.
Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.
The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.
This works fine, including forcing the query to return zeros when no reports have been submitted for that club.
What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.
I can make the query and report to show the figures now.
I can make the query and report to show the figures X days ago.
What I cannot work out is how to combine the two queries into one report source so that I can get
Club 1 100(50) 75(0) 45(45) Club 2 0(0) 0(0) 0(0) Club 3 20(19) 0(0) 200(50)
etc
If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.
I have a report in access 2007, now i need to ask that i am creating new blank report and just like to to capture value from other report via textbox or any source (you may reccommend), for e.g in Report A i have months and their total amounts now i want to add both these fields in new Report B where i will do the same with other previous reports to create summary of accounts.
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct - SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click() If Right(strsql, 1) <> "'" Then 'check if statement was built Else strsql = strsql & ";" 'add trailing ; to statement MsgBox strsql DoCmd.OpenReport "tblincidents", acViewNormal, , strsql End If End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
How can I set the record source of a report to a saved query through VBA. I am trying to use the same report for a number of uses, all of the info on the report is the same, but the only difference is the query that the information is based on. I have this simple code below, how do I add a record source to it (if it can be done)
I am working on stream lining reports for my application, so that I don't have a huge maze of menus.
I am open to any ideas of how to best set this up. Right now I have a report menu with about 8 command buttons. Some take them to a criteria form for that report, others straight to the report, and others to another menu with a whole other set of buttons for more reports.
I have looked at organizing the "categories" of reports that I have.
I see that I have Candidate reports and Hire reports. This is because this application is for people who apply for a job. Once the information is entered on the entry side, there are a number of things to report on.
One idea is to make a statistics report menu for all of the summary reports. These are counts of candidates under certain groups, like what office they applied to, what departmen, or what date they applied. Then these counts exist for hires as well, and what they were hired to, and what date they started.
I also have detail reports showing the people information as candidates or hires, grouped by these such factors.
If anyone can help me to organize the best method for making these reports available to the user, I would really appreciate it.
One thought would be a form with 2 combo boxes and a preview button.
I am thinking that this could work for the statistic report menu. My database is normalized, so I can't use their names in the combos. I would want to have 1 combo for choosing candidate or hire, as that report that they want. Then the next combo would be the factor to report them on. Office, department, application source, etc
I have summary report for candidates and for hires. parallel reports, but one for (ie) all candidates by the office that they applied to. Then I would have one for all hired candidates (were hired) by the office that they were hired to. Same with department, and many others.
If anyone can help me to either how best to do this process, whether my thought is a good way to go, or if there is a better set up that I should try. I am open to anything here!
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database ' Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset) ' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
I have a report which is opened using a DoCmd.OpenReport. There's a criteria string which filters the main report - this works fine.
There's now a requirement to place a summary subform at the beginning of the report, in the report header. I need that summary to use the same criteria string as the main report.
For the main report's OnLoad I put : Me!Expenditure_By_Type_Subreport.Report.Filter = Me.Filter
But I get the error message:
Error 2101. The setting you entered isn't valid for this property.
I tried it the other way round as well - in the OnOpen of the subreport I tried : Me.Filter = Me.Parent.Filter
And it gives the same error.
When I just a manual Filter change such as : Me.Filter = "Project_ID Is Not Null"
Report has records of errors. There are 3 types of error. In the report footer, I would like a total count (this is working!) and a count by type (can't get this to work).
Error Total =Count[ErrorType] Compliance Total =Sum(IIf([ErrorType]="Compliance",1,0)) Audit Total =Sum(IIf([ErrorType]="Audit",1,0)) Quality Total =Sum(IIf([ErrorType]="Quality",1,0))
I have tried setting groupings on the report but this alters the detail sorting (currently sent by the date field newest to oldest) and provides the count within the detail.
I have used the following script to export a subform to excel but it isn't working for a report.
The report name is spelled correctly etc... but the error message keeps saying error 2465 or if I enable the error handler it says application defined or object defined error.
I don't want to have to export the data to a table first, but if I have to then I guess I will have to.
Code: Sub fFunctionNameExportExcel(ctrl As IRibbonControl) 'print to excel Dim rst As DAO.Recordset Dim ApXL As Object Dim xlWBk As Object Dim xlWSh As Object Dim fld As DAO.Field Const xlCenter As Long = -4108
Using a Microsoft Chart Object 6.0 in an Access 2010 report. It's easy enough to do the basics and that chart responds to data.In my case, I have 12 lines, or columns, being controlled by data. It responds to the data. just fine. What I want to do is control the line weight and colour of each line through VBA.
You can click on the chart itself on the report form, but formatting the line you actually want is almost impossible. Pretending we can, you get the pop-up configuration windows and the TAB "PATTERNS". Under that, you can select "LINE" and then choose the style, color and weight. This is what I need to do in VBA.
Lines like this do not work:Graph_Data.Columns("A").Line.Weight = 5 or... objDataSheet.Columns("A").Line.Weight = 5I tried a variety of versions of that and am pulling my hair out.
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
I have a master/child forms. in master form sale_id,collection notes are placed. and in child form all collection details like collectionnumber,date,etc., child form look like datasheet view.
When i select the record from the list depending on the sale_id all the collection records shown in datasheet view form. If no record is there ok. if more than one records are there in collections of sale_id.i need count of records in collection depending on sale_id and also if i select one row in collection, notes to be shown for that record when i clik on shownotes button.
If more than one record in collection automatically it increment. how many collections for the sale_id. collection number would be increment. How and also show notes depending on collection number and sale_id.
I have a form with a subform. When i click a button on the "main" form i'm able to change the source object of the subform. But the subform now shows the records which are already present i want the subform to start with a new record.
