Obtaining Multiple Report Parameters Via Menu Form Lookup Control
Feb 11, 2015
In our Student Administration database, we have a Student Evaluation Report which prints a 1 page per student report in memo like format. The instructor had previously input rating numbers (4=Best, 1=Worst) for 9 categories for each student via a database form into the Evaluations Table.
Two other elements of the rating are an Attendance based on number of days absent and a Grade Point Average (GPA) calculated from the student's test scores. The instructor then prints and reviews the report with each student.
There is an Evaluations Parameter table which has the following fields:
Field
Description
ID
The autonum key field.
Class
eg., 2015-1
Evaluation Number
A single digit (eg., 1, 2). There may be more than 1 evaluation for each class.
EffectiveDate
The "as of" date of the evaluation.
There are then several other tables that are input to a query that will be the record source for the report:
Table
Purpose/Data
Students
Student Name, Class
Absence
Has a record for each student's absence with date and a 1 or .5 indicating a whole or half day absent.
Test Grades
Has a record with each student's test results with date and score.
Evaluations
Holds the rating score for each of the 9 rating categories.
The Student Evaluation report is launched from an unbound Reports menu form via a button. On the Reports menu form I wish to have controls for the user to select which evaluation to report on. The Absence and Test Grade information needs to include records that are <= the EffectiveDate in the Evaluation Parameter table. The Evaluations records need to match the Class and Evaluation Number in the Evaluations Parameter table.
I would like the user to be able to select the Evaluation Parameter via a combo box vs. specifying the Class, Evaluation Number and Effective Date separately and then have the query record source for the report use those two fields as criteria. But my limited understanding of the Combo box indicates that only 1 field from the lookup query of the Evaluations Parameter table can be stored in the control whereas I need 3 (Class Evaluation Number and EffectiveDate).
How the user can select the desired Evaluation Parameter record on the Reports menu form and use the 3 fields from the selected record as criteria in the report's record source query.
View Replies
ADVERTISEMENT
Sep 17, 2006
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
View 2 Replies
View Related
Oct 21, 2004
I'm pretty new to Access, so if I'm doing this the hard way, that's why.
I work in a quality control position, and I'm trying to set up a single Access database for the QC staff to use instead of everyone having their own seperate Excel workbooks.
The issue I'm running into at the moment is on a report. Each record is graded on four seperate types of criteria, Error Type 1, Error Type 2, Error Type 3 and Error Type 4, all of which need to be reported on seperately. So I have at least four queries set up, all with the same parameters (right now, just review date.) I'm trying to pull through all four queries on the same report, and so far I've had success having the report ask for the parameters only once and then applying it to all four queries. However, I'm running into a problem where Access is now forcing the filters of each individual query on to each of the other three queries, so it's only pulling through records for all four queries that match the criteria of all four. Any record which only matches the criteria of one, two or three of the queries is being left off the report.
Sorry if this sounds confusing. Anyone have an idea as to what I'm doing wrong?
View 3 Replies
View Related
Aug 15, 2006
I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?
Thanks.
View 14 Replies
View Related
Aug 9, 2013
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution
Query 2: Total Distribution
etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
View 1 Replies
View Related
Aug 4, 2014
I have a navigation form that will have 6-8 tabs. We were using about that many databases, but we are finally consolidating them into one. The result of us using so many databases has been the multitude of forms and reports that were necessary for each database prior to merging them together.
The problem: There will be anywhere from 12-20 (text boxes) that the user can use to search anything in our database. What we need to have happen, if possible, is for those search parameters to show up in the header of our report if they have text in them. If the text box is blank, it should not show up in the header of the report.
I have read how to to do the start/end date technique, but I do not know if that would work for what we are doing since the boxes would only show up if they are populated by the user.
View 4 Replies
View Related
Jul 8, 2013
I 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.
View 3 Replies
View Related
Jun 27, 2006
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
View 6 Replies
View Related
Jun 30, 2014
I have a report with a nested Subreport. Main Report>Subreport1>Subreport2, for simplicity.
I have master/child set up for both Subreports, but I would also like to filter the Subreport2 records with a date range parameter.
Is there a way to get the Subreport2 where clause to recognize the Subreport1 control I want to use for the date range?
So far I have tried:
Between Int([Reports]![Processing_Performance_Req_Dataset]![report]![QC_Static_Processing_Subreport]![Collection_Date]) And (Int([Reports]![Processing_Performance_Req_Dataset]![report]![QC_Static_Processing_Subreport]![Collection_Date])+0.9)
Between Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport]![Collection_Date]) And (Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport]![Collection_Date])+0.9)
and
Between Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport].[Report]![Collection_Date]) And (Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport].[Report]![Collection_Date])+0.9)
where Processing_Performance_Req_Dataset = Main Report
QC_Static_Processing_Subreport = Subreport1
and Collection_Date = textbox control on Subreport2.
Is there a syntax error? I have other, single level, Subreports on the main report with a similar data range parameter that work just fine. Or does this type of parameter input not work on second level Subreports? Every time I run the main report it asks me for a parameter but doesn't accept my input, and Subreport2 doesn't load any records.
View 4 Replies
View Related
Feb 22, 2006
hi guys, im doing a uni project in access for a small business and the problem i have is that i am making a booking form, and on there needs to be
owner details,
animal details, and
booking details.
these all need to be entered when making a new booking, the problem i have is that i can only seem to be able to use one control source (from the owner table) how do i use multiple control sources on that one form? :confused:
Jez
View 3 Replies
View Related
Dec 2, 2014
I have a bar pivot chart made in a form this load fine, I have the below VBA code in place to control the bar color depending on what the bar show.
Set frms = Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0)
Dim i As Long
With frms
For i = 0 To Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection.Count - 1
With .SeriesCollection(i).Name
Select Case Forms![OtherCases created by Indv by Month
[code].....
When I then open the Report this is not working at all each bar just get a random color regardless what the bar show.how to convert the above to fit a Pivot chart form a form that is displayed on a report.
View 1 Replies
View Related
Jul 26, 2005
I am trying to strengthen an already developed database at my work. They had a form with five different text boxes, each one that would run a seperate query on the same table, and the results would open on a different page. I am trying to combine all these text search parameters from the form to a single query and have the result come up on the form. I have done every search I can thing of from a forum search, a google search, and just reading through query forum posts for about a day; but I still cannot tell what is wrong. If someone could look at my code, and see if I have a mistake, or if I'm even in the right direction. :confused:
SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE ((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25 & "*") And (FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37 & "*") And (FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text32 & "*") And (FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0 & "*") And (FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text36 & "*"));
The FLIGHTS is the main table, [KNOWN BURN TABLES] is the form that the parameters are entered. I want users to be able to enter any fields they want and leave others blank. Currently all I get is a blank query. Sorry if the answer seems obvious; I tried avoiding posting till I knew that I couldn't figure it out on my own.
Thanks.
-Brian
View 8 Replies
View Related
Mar 30, 2005
I often use forms to select a parameter & date range for a subsequent report. Where I have chosen to use multiple combo boxes (two in this case)to select more than one parameter I have run into a problem. The report opens ok the first time but if I close it (report) and change my selection then the report refuses to open. Should I just requery the two combo boxes, is there something else or can't it be fixed?
View 1 Replies
View Related
Oct 26, 2004
I've created a database where I have all the information for people like name, address, etc. Then there's a list box to choose what events they will be attending. I want to be able to run a report and see all the information for the people who are attending any specific event. Is there a way to create a form where I can click a button then it brings up the screen to choose the parameters of my report, then generates the report? Thanks!
View 4 Replies
View Related
Apr 20, 2007
I am trying to figure what code to use. What i am trying to accomplish is when the use clicks to open a report i want a form to open and make them choose from a combo box a "training activity". then the report will generate info for that activity only.
Any thoughts??
Thanks
Megan
View 7 Replies
View Related
Jan 4, 2006
I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...
strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"
Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.
SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;
Your help will be greatly appericated.
View 3 Replies
View Related
Apr 22, 2008
I'm trying to setup a parameter that has a multiple choice so to speak. Rather then the user being able to enter whatever data they want.. I would like them to have a choice between two items.
Is it possible? if so.. how do I do it?
Thanks!
View 14 Replies
View Related
Aug 9, 2013
Is there any way to use a lookup field as the control in a QBF as opposed to a text box?
View 10 Replies
View Related
Aug 25, 2004
I have a field in a table called reasons. Then I have a list of possible reasons in a drop down list for my form. I want to create a report that allows the user to select from a drop down of these reasons and do a query for only those records with that specific reason. I know you can ask that info be typed in by using brackets in the query but instead of typing the reason I want to be able to select from the drop down list to produce the report. Can this be done?
View 1 Replies
View Related
May 10, 2013
I'm trying to set the control source for a control on my report that describes the number of portions that goes into a box. Sometimes there is a specific number of portions per box and sometimes it's a range. When the number of units is specific, then it is entered into the MasterCaseMinUnits Field. If there is a range of units possible than the min is entered into MasterCaseMinUnits and the max is entered into the MasterCaseMaxUnits.
I want my report to look at the mastercasemaxunits, if it is blank it will only display what is in the MasterCaseMinUnits field. If there is a value in the masterCasemaxUnits field, then it will display the masterCaseMinUnits & " - " & MasterCaseMaxUnits. this is the code I'm Using:
Code:
=IIf(Nz([MasterCasemaxUnits],[MasterCaseMinUnits])=[MasterCaseMinUnits],[MasterCaseMinUnits],[MasterCaseMinUnits] & " - " & [MasterCaseMaxUnits])
When I run my report, the control displays: #Type!
View 2 Replies
View Related
Dec 7, 2005
Hi All,
Long time no see y'all...
Question: is it possible to obtain a list with all the Access based System or User DSN entries on a system? First I don't know how to get such a list at all, and second I don't want to bother the users with non-Access entries - they're confused enough as it is.
Any help appreciated & regards,
Michiel
View 1 Replies
View Related
Feb 17, 2006
I have several reports that open from a Form. All but one of the reports have menu bars on them...one report does not. I've looked at just about everything and I can't figure out what's causing that. Actually, I really don't need the Menu bar on the Reports, so I would like to remove the others.
I've attached a copy of a cut-down version of the file so that you can see what I mean. The file will open up to the Reports. There are two reports listed. The "New Releases" does not have a Menu Bar and is what I want. The "All Movies" has the Menu Bar which I want removed. Can anyone tell me why one has it and the other does not?
Thanks!
View 3 Replies
View Related
Oct 31, 2005
I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.
SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));
View 1 Replies
View Related
Jan 11, 2005
I created a report using wizard and altered the parameters to what I need on my report and saved it as a new autoformat. The autoformat did not retain the parameter adjustments I made...only the basic format...no size changes...grouping I added ...a seperator line between the groups and the bound items I deleted that I did not want were still there. All I want to do is to be able to use the report format I created numerous times as it is without me having to go in and make adjustments with each query I pull a report from. There are several different query's with different tables involved. To sum it up one report format to use multiple times....Help Please!
View 5 Replies
View Related
Jan 7, 2005
I would like to run a report that uses a stored procedure with parameters. Is there a way I can pass the parameters from the report to the stored procedure? I am NOT running it from a form.
I want to call the report from VBA code and pass it the parameters that are necessary to run the stored procedure. Any ideas?
View 4 Replies
View Related
May 13, 2013
I have an SQL statement which is dynamically built on a form.
It can be truncated to read
[Colour] = 'Blue'
Or it can be left as a complete statement - SELECT * from tblOrders WHERE [Colour] = 'Blue'
The statements work when I open a form.
Is there a simple way to open a report (or query) with these parameters?
View 6 Replies
View Related