I have a report with several subreports that occassionally grow. I also have several fields that don't grow. If my subreport grows, it moves any other fields down as well. These are fields that are below the subreport, but not beneath it (e.g. on a lower line, but to the left).
Is there any way to keep the other fields stationary?
Thanks!
I have a production report with 4 one to many sub reports (material reworked, reclassified, rejected, and issued). Each of those sub reports has a sum field totaling up the material reworked, rejected, etc for that lot.
I need a calculated field that subtracts the totals in each sub report from the reported amount produced in the main report.
Should I do this with a calculated field on the report. If so how would I reference the summed fields in the sub reports?
Or should I put a summed field of the amount reworked, rejected, etc in the query the report is based on and make a calculated field off that.?
hey there
i have a main PatientForm that contains patient information from multiple tables:
[GENERAL] values
Subform - [RefMD] values (referring doctor)
Subform - [Drugs] values (prescribed meds)
Subform - [Diagnosis] values (medical diagnosis)
Subform - [Encounters] values (visits to the doctor)
what i have is a form (image attached) and i need to be able to select any combination of parameters (including state, zipcode from GENERAL, i.e. referring doctor last name from RefMD, Drugname from Drugs, Diagnosisname from Diagnosis, and VisitType from Encounters) and filter PatientForm where all the selected parameters are true..
does that make sense?
all tables are linked using the field HistNum
how do i code this? i am pretty lost right now
I am trying to open a report (with subreports) from a form. When the report opens, I want the form to hide, which most of the time does. Sometimes, though, it won't hide when the report opens. So, I even created an error handler, and moved that part of the code to SubExit section, but the problem still occasionally occurs. Here is my code:
Code:
Dim strWhere As String
Dim strDate As String
Dim strDist As String
On Error GoTo ErrHandler
strDate = "=DateSerial(" & Year(Me.txtAsOfDate) & "," & Month(Me.txtAsOfDate) & "," & Day(Me.txtAsOfDate) & ")"
strWhere = "YearEnd = " & Year(Me.txtAsOfDate) - 1
[Code]...
In my database, I have a report called "rptEventsbyEmployee" and in that a subreport called "sbrptEventsbyEmployee". The master/child link is the employee name. Everything is fine until you get to page 3 where the subreport shows twice.
View 5 Replies View RelatedI have a database (Access 2007) that handles a large number of consultants. Each consultant has a number of skills, has worked in a number of countries, and speak several languages. As contracts come in, I wish to generate a report based on the skill set, language and country/countries that best suits the contract; thereby identifying a group of most suitable consultants. I have designed a report with three sub reports for skills, languages and countries. Each sub report is linked to the main report through Link Master/Child fields using the ConsultantID. That's all fine; I get a report showing all consultants listing all their qualities.
Now, I want to be able to filter the sub reports on the three aspects: Skills; Countries; and Languages.
In the past, for other applications, and with the unfiltered form in preview I have used a popup form with unbound Combo Boxes to generate a string to use in the reports filter. I think I got that technique from Allen Browne, and it has really served me well.
Now, however, I can't use this technique since I can't set the filter for the sub reports. I thought that I might be able to use the Link Master Field with text boxes on the main report, but haven't managed to figure that out.
I am relatively new to Access. I am having difficulty summing up totals and bringing them into the main report. To be more specific I have two sup reports called WOExpenseTotalSubreport and WOServiceTotalsubreport. These two subreports are based off of queries that gather the description, price and qty of either services or expenses and then produces a "total" for me on the query.
When I bring these two queries into a report as sub reports I am able to Sum the "total" field in the subreport footer which works fine. What I would like to do is add those two totals together for a grand total on the main report. The two summed fields are called ExpensesSum and ServicesSum. It seems most things I have tried do not produce an expression error but the result is either #error or #name? Here are a few different scenarios I have tried both on the main report details section as well as the footer section.
=[Reports]![qryWOExpenseTotal subreport]![ExpensesSum]+[Reports]![qryWOServiceTotal subreport]![ServicesSum]
=Sum([qryWOExpenseTotal]![Total]) + Sum([qryWOServiceTotal]![Total])
=[qryWOExpenseTotal subreport].[Report].[ExpensesSum] + [ qryWOServiceTotal subreport].[Report].[ServicesSum]
I have three reports that I have used as the subreports for one master report. The master report shows the three subreports on a page.
When one or more of the subreports contains no data the master report still shows the empty subreport. I would like subreports with no data to be left off the master report if this is possible.
For each of the individual subreports I have used the On No Data event to display a message and then not open the report if there is no data. This works fine for the individual subreports but when I open the master report the "empty" subreport is still displayed.
Is there a way to leave the subreport off the master report when there is no data in the subreport?
I have a main report with 2 subreports. My main report has a header section, the 2 subreports are in the detail section of my main report. How can i prevent my report from splitting up my header and my subreports. (Header section of my main report is a company and the subreports are the detail of the company.. i don't want my compnay name on the bottom of a page and then the detail on the next page.. it does this sometimes. thanks!
View 5 Replies View RelatedI am working on a report that will have 16 sub-reports, one right below the other. Because of the complex nature of the calculations in the groupings it seemed easier to "build" the report using vba rather than using the report's built-in grouping ability. The issue I am running into is that there seems to be no simple way to control the height of the sub-reports. I had hoped that setting "can grow" to yes would change the height of the sub reports and move all subsequent sub reports down (Allas)
As an alternative I thought too that I might be able to set the height of the sub-report control, which would make for a tricky but not impossible bit of programming, but (Again allas) I can't seem to find a reasonable way to determine the required height of the sub-report's control.
OK...I need a bit of help here. I have put everything together for my report, including 4 subreports, but am having a few problems.
I have been scowering the forums here and just can't seem to get these figured out...hoping you can help.
PDF of Report results attached.
Report overview: This is a grouped report and each subreport matches up to the PositionCode field. That works just fine and is pulling the correct data.
Subreports: Each subreport is noted by the headers in grey. The Composite $ Match Info and STRATEGY Salary Range subreports will always show. The remaining two subreports may not have data to show.
Problems:
1. After the 1st page of each report, it is printing out a blank page.
2. The Employees in Position subreport may or may not print out on another page(s). I would like the subreport header to also print out on any additional pages as a header (see page 3 of the PDF). The header is currently a report header. I have tried changing the header of the subreport to a page report to no avail. I want to at least have this show on the 1st page and continue on to others as the report for each position could be 3 or 4 pages, due to the number of employees in each report. I'm at a loss.
3. If one or both of the last two subreports have no data, I would like to show the headers, but nothing else (I can or cannot have a field that says "No Data"...no preference). I have read about HasData in these forums, but I truly am a novice at report writing, so I can't seem to make sense of it. I would need someone to truly give it to me in elementary speak...step by step.
Thanks so much for any help you can offer.
Melane
I have a main report that has a patient ID criteria before it opens (Click the report and it will ask for a patient ID, you enter the ID and then the report generates for that particular patient). This main report is generated off of a query. I want to be able to add a subreport to this main report to list the therapists that had contact with this particular patient. I'm able to create the subreport (off the same query as the main report), but when I run the main report with it's newly attached subreport it asks for the Patient ID over and over and over again until the report finally comes up. The report has two pages (the subreport is on the second page) so when I click the directional arrow to go to the second page, once again, it starts asking for the patient ID over and over again. If I keep entering the Patient ID number over and over and over again it will generate the correct report with the correct information. My question is, is there a way to open the main report, respond to the criteria (Patient ID) once and then the report generates without having to enter the Patient ID over and over and over again.
Thanks for any help.
I'am working with tables of loan associates, customers, companies and States versus numbers and amounts in my report and I want to create a main report with a subreport within it which shows the number of customers and their respective state that a loan associate has either written a new loan or serviced that customer and a total for each loan associate? Is there a report template out there that could address this for a complete report?
View 1 Replies View RelatedI have a report that contains 3 subreports with a calculated total text box in each. I am trying to calculate the total of these text boxes from the subreports in the main report but I keep getting an error message (#Name?). Is it possible to calculate calculated sums from subreports in a main report?
View 1 Replies View RelatedI need to create a report based on how a user creates construction components of which there can be any number, e.g.
Component 1
Component 2
Component 3
...
Component n
Each component has different data.
A report is needed to show the above components. The issue I am having is this - the user can select components to be repeated as follows:
Component 1
Component 2
Component 3
Component 2
Component 3
Component 2
I have created subreports for the components but when I run the report (no master/child relationships defined for any of the subreports) I get:
Component 1
Component 2
Component 2
Component 2
Component 3
Component 3
So I created a master report (in design time) and added subreports using VBA and now I get the components in the correct sequence, but how do I get the data to show up correctly because at run time the same query runs for each subreport component and I end up with the above sequence.
Defining the subreport query at design time won't work because then the subreport for a particular component will show the same data.
I need each subreport to recognize which data to show at run time - but I am not sure how to accomplish this.
I'm trying to stay away from duplicating subreports in the case where the same components are selected.
I've narrowed my problem to a subreport that prints multiple copies of the report based on the number of items generated in the subreport.
i.e. If I have an instructor registered to teach three classes in the subreport, the main report will print three time (identical). An instructor with two classes will print twice (identical).
No button...just good old "print" how? why? where?
I have created a report that includes 4 subreports. The subreports are displaying survey results from 4 different survey channels. New survey data is added to the tables monthly so the subreports will grow in size. Is there a way to have the subreports automatically resize and push down the subreport below? Currently what is happening is a subreport will grow and the additional data disappears underneath the next subreport. Is there a better way to create a report that displays information from multiple reports that will change in size?
View 1 Replies View RelatedI have a report with an embedded subreport. It's set up in two columns, ordered across then down.
My problem is when column 1, row 1 section is taller than column 2, row 1 section. It creates a lot of extra white space as I fill in the rest of the report. Is there a way to eliminate that space or am I stuck?
I have an application with a form where 20 subforms are used to add data to the same table. The 20 subforms are used to divide the data being added into categories ( think of a grocery store with categories of "Meat", "cereal', vegitables etc)
I use the same subform (with different names) and change the record source on the formOpen event to change the subforms properties. A portion of the code is shown below.
tempRecordSource = "SELECT [tSkills Assessment].Skill, [tSkills Assessment].Scale, [tSkills Assessment].Comments, [tSkills Assessment].AreaOfFocus, [tSkills Assessment].SubSectionTitle " & _
"From [tSkills Assessment] WHERE (([tSkills Assessment].SubSectionTitle)=" & Chr(34) & tempSubSectionTitle & Chr(34) & " and ([tSkills Assessment].SkillsAssessmentID)=" & [SkillsAssessmentID] & ") ORDER BY [skillOrder];"
Me(tempSubFormName).Form.RecordSource = tempRecordSource
I use an array to feed the code (which is in a loop) the variables needed to assign the correct properties to all the subforms.
And it works great. Yes I know I could have created 20 different subforms, but in an attempt to keep my application simple I am trying to have less objects.
The problem is in my output (the Report).
When I try to do the same thing on subreports it doesn't work. I figured the logic should be the same but the code that corresponds to the form code "Me(tempSubFormName).Form.RecordSource = ..." doesn't work. Either the logic is wrong or I am not using the correct event???
I have tried every combination I could find on the internet and $300 worth of Access manuals but no luck. Any ideas?????
I am trying to create a two-page report. I used a document entitled "Sophisticated Reports: Multiple Columns and Subreports" by Rich Gorvin to get where I am now. I created two (2) separate tabular subreports representing each page then placed them into a master report using a common group header as suggested in the paper. While the two (2) separate subreports print out in the desired tabular format, they do not do so in the master report document. The first page prints out just fine but the second page prints out each line of the table separately.
I am attaching a word document of the form I'm trying to re-create in Access.
I'm trying to build a command button to parse each line into a new record of my table [TBLRECORDS].I am able to parse a single line using this code but don't know how to insert the data into the table and loop through for all lines in my [memFile] control
On a form I have a text control [memFile].I populate it by pasting text from the body of an email into it and it is stored in a tables memo field [TBLFILES].[TEXTFILE].Each email can have countless rows of data in this format
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7756*33*1D1PMB*20140418
H E74 Warranty end not prior to Serv start for XRC Status: M RecordId=897*7916*68*A1C558*20140418
Each line of data will always begin with "H " and each line of text will always have the same character count..For each line in me.memFile I want to create a new table record and populate this way
[TBLRECORDS][TXTTYPE] = strType
[TBLRECORDS][TXTCODE] = strCode
[code]....
I am creating a report that has the variable address as usual.
Name
Address1
Address2
City, State zipcode
If there is no value for address2, is it possible to shift the locatoin of city state zip up into the Address 2 location? I would like to shift the lable up with code, but can't find a way to do it.
I am using an unbound data source so i have full control over the variable data.
thank you
Hello,
I have a big table like this:
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian
How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
Thanks so much!
Jenny.
I've just created a form and I'm trying to move and size the fields but they seem to be grouped together and whatever I do to one happens to all of them. I select one field and just that field has the orange outline but when I move it they all move.
View 5 Replies View Related[URL] .....
In the Lesson tutorial shown above I am clicking on tools and adding existing fields. I get a lot of output label and text box pairs for each field that I add.
However, they are all on right below the other. That is no problem just move them. No way.
Whenever, I move it a label and test box for say Order Status, I get ... nothing. It stays where it is.
They (label and text box) do not move together. So how do I get them to move together.
Hi,
I have a mdb base (BD1) wich have no tables, the tables are in BD2 and are linked to BD1.
But BD1 grows, sometime just 20 or 30 bytes but sometimes more than 300k in one time. What Access stores ?
Is it possible to limit this ?
BD1 can be ran from many user and is stored on the server (not local) it is shared so i think i can't use option : compact data base when quit.
What are the best ways to prevent this ?
Thanks in advance.
VINCENT