Forms :: Report Selection Hierarchy
Jul 25, 2013
I have some requirements whereby there are many reports that are needed to be given to the employer. I had an idea the other day and I wanted to run it by you guys. The reports can be categorized in a hierarchy (like everything else in this world of course):
=> Type
=> SubType
=> Name
Here are some examples of what I have currently:
Types => Finance, Customer, Services
SubTypes => Invoicing (Finance), Proposals (Finance), Customer List (Customer), Recent Customers (Customer), Service Types (Services)
Name => Print Invoice (Invoicing), Print Proposal (Proposals), etc, etc...
What I was thinking of doing is creating a few tables with relationships based on this, then one form from a query (that joins all the tables together) that serves as the report generating form. I would drive the cascading combo boxes by a bunch of hidden fields on the form itself.I think this would be much better than just writing a bunch of SQL statements behind combos on an unbound form. Wouldn't it?
View Replies
ADVERTISEMENT
Dec 6, 2013
I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having a hierarchy, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will be a distinct record.
As fas as data entry is concerned, I would like the user to not have to re-enter the same information over and over when entering multiple species entries at the same sampling station, on the same date and so on up the hierarchy.
View 2 Replies
View Related
Jan 29, 2014
I am struggling to pull out a report based on the combo box selection. I have NOI database and want to pull out a report for a specific customer that how many NOIs are generated by this customer..
I have a separate table of customer and another table of NCRs and the customer information comes from customer table.
I have tried the below statement but gives me an error message:
code for Macro that pulls the report in click event:
[CustomerName]=[forms]![CustName]![combocust1]
do i need to mention the report as well in some where in report ?
View 14 Replies
View Related
Feb 17, 2014
I use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string.
Private Sub FilterDesc_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.ListCarrier.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Carrier"
[Code] ....
View 7 Replies
View Related
Oct 27, 2014
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
Sample of the database has been attached.
View 4 Replies
View Related
Jul 1, 2006
Hello
I dont know if this is possible in MS Access (2003) but this is what I want to do...
I want to create a database, each record has about 8 fields.
Usual basic stuff for a typical DB so far...
BUT! One of the fields is the category info, say "Item 1"
Usage:
Now another 5 records are made and are childen of "Item 1" and called..say "1a" (..b,c,d,e)
Item 1d record has 300 sub nodes to it.. say "Item 1d1" (2 to 300). And so on to eventualy make many records but linked by a huge tree like structure (like Treepad DB).
Finding Records.
Now, to find say "Item 5c22f5" I just want to see 1 drop down list, I then choose "Item 5" from it.
Then a second drop down list appears with..say 8 child nodes of Item 5, I choose "Item 5c", 3rd drop down appears, I choose "Item...22"
etc, etc. till I get to my "Item 5c22f5"
Now, while sellecting each node, some nodes in other brances contained hundreds of items but I never want to see them unless I go through their brances.
Adding new records:
eg: While working on "Item 5c22f5" I decide to add a record which will be "Item 5c22f6", I want to simply add it within the same branch but not be expose to the hundreds of other nodes/records.
Then I also would like to now add a sub of the current and call it "Item 5c22f6a". and also be be confided to the current branch. Or if I just go in 3 brances deep & see 5 levels each etc.
Finally, each record will have a field called "Notes" but I want rich text formating (like in word pad would be fine).
Maybe the ID tagging fields could be 3x on each record to show forward, current & back nodes???
What ever it takes.
I hope I've explain this OK.
I am new to MS Access so if it cant do it, I dont want to spend time learning it for nothing (at least not now).
This (if sugcessful) will be my universal, mind mapping info base. A baby I've been dreaming of for years now. Like Infohander meets TreePad meets Advance Data Managment (ADM) meets FileMaker.
Not availble anywhere in anyform as I know.
Thanks in advance. (sorry about the spelling)
View 3 Replies
View Related
Jun 10, 2014
Any way to obtain all individuals that report to a particular person (directly or indirectly) through a query. A sample of the table is below:
tbl_Hierarchy
Enumber
FName
Mngr_Enumber
E111
John
E000
E222
Mary
E111
E333
Paul
E111
E444
Sarah
E999
E555
Sam
E333
E666
Tom
E111
E777
Karen
E111
E888
Jim
E666
E999
Deb
E666
For example I would like to see everyone who reports through Tom(E666). The result should yield:
E888
Jim
E111
E999
Deb
E666
E444
Sarah
E999
View 3 Replies
View Related
Oct 24, 2013
I am trying to create a named hierarchy based off a table from an Oil & Gas Program ARIES. The code below is as far as I could get:
Code:
SELECT dbo_AC_ECONOMIC.PROPNUM
, dbo_AC_ECONOMIC.SEQUENCE
, dbo_AC_ECONOMIC.SECTION
, dbo_AC_ECONOMIC.QUALIFIER
, dbo_AC_ECONOMIC.KEYWORD
, dbo_AC_ECONOMIC.EXPRESSION
, (SELECT COUNT(*)
[Code] ....
The results are as such:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1
TEST1.......4................2.................TAG ............."..................22................ ..1
TEST1.......4................3.................TAG ............."..................22................ ..2
TEST1.......4................4.................TAG .............SHRINK.........1...................1
TEST1.......5................1.................TAG .............ATX...............5.................. .1
TEST1.......5................2.................TAG .............ATX...............5.................. .2
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1
TEST2.......4................2.................TAG ............."..................22................ ..1
The desired Results:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST...KEYWORD2
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST1.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
TEST1.......4................3.................TAG ............."..................22................ ..3........GTC/GAS_3
TEST1.......4................4.................TAG .............SHRINK........1...................1.. .......SHRINK_1
TEST1.......5................1.................TAG .............ATX..............5................... 1........ATX_1
TEST1.......5................2.................TAG .............ATX..............5................... 1........ATX_1
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST2.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.
BACKGROUND
PROPNUM: UNIQUE ID
SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc.
SEQUENCE: The order of the sytanx in that propnum's section
QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query)
KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it*
EXPRESSION: are the variables that are treated by program according to the keyword
OVERALL GOAL: The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.
View 2 Replies
View Related
Sep 27, 2004
I am trying to create a form where the user can select which group of data they want (records complete, records due, etc) and also how they want the report sorted (project name, due date, etc.)
I created an option group for the record selection part with a variable named myFilter being equal to the required filter (i.e. myFilter = "(([Assessment Details].[Completion Date]) <= Now())")
I then created a combo box for the record sort part with a variable named myOrder being equal to the required sort (i.e. myOrder = " ORDER BY [Project Description].Deadline")
Finally I created a variable named myCriteria which is equal to myFilter & myOrder. Then I try to open the report with DoCmd.OpenReport stDocName, acPreview, , myCriteria.
Now, when I run the form and try to get the report, I get the following error: Syntax error (missing operator) in query expression '((([Assessment Details].[Completion Date]) <= Now()) ORDER BY [Project Description].Deadline)'.
Is there any way to have the user be able to select the sort order and have it pass to the report?
Thanks in advance.
View 1 Replies
View Related
Oct 25, 2005
I have set up a query to give me details of employees names, the business they work for and where they work.
If I run it without criteria I get all of my records, as you would expect.
However, I want to be able to select parameters from combo boxes and then run a specific report of employees by location. I did have this working by accident a while ago but have changed quite a lot since then.
I have set up an unbound form with three combo boxes - Business, Business Unit and Location. Then a button for preview and print report linking to my report template.
I have refernced the controls in the relevant columns of my query as: [Forms]![FormName]![Control].
When I run the query I get nothing back and I don't understand why.
View 9 Replies
View Related
Apr 19, 2005
Hello all.
I would like to place a "Print Report" button on my primary form that allows a Filter By Selection (OR Filter By Form) first, then when selecting the button will view or print my already-created primary report but only for the records that were selected by the filter.
I looked and looked, but am not sure even what to search for in the forum. I know this has to be simple.
Form is called PrimaryForm.
Report is called PrimaryReport.
Thank you.
Tom
View 3 Replies
View Related
Feb 10, 2006
Dear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report.
Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report?
Is this possible?, any help would be much appreciated, thanks.
View 4 Replies
View Related
Jun 30, 2005
Hi guys . I created a form that has 3 combo boxes. Their name are Project Number, year and weekno . It has also a button that on click action supposed to generate a report baced on my combo box criteria and then loads that report for me. could any expert show me an example on how to do this. I have difficulty finding an example in google since i did not exactly what is called this method of generating report in access 2000.Thanks
View 2 Replies
View Related
Jun 16, 2015
I am trying to get a report based on a selection from a combo box. The issue is many fold. Firstly I have been playing around with it so much that I have messed up the syntax and can't remember what I had. Here is what I have at the moment (this is based on a button):
DoCmd.OpenReport "RepClassrooms", acViewPreview, , " Class = '" & Me.cmboRepClass & "'"
The second issue is that it isn't filtering on the combobox - it gives all the data from the database based on all the entries in the combobox but this is probably down to the syntax again.
View 2 Replies
View Related
Oct 19, 2015
I'm having difficulties with building a printing option for specific goods selected on the list.
I have a form which contains a subform. On the main form user is able to select supplier and based on the supplier can choose article that he's interested in. On the subform user gets the list of all the goods that are connected with that specific article.
I want be able now to select specific goods from subform list (using checkbox value) and based on the selection print a separate label for each goods.
View 4 Replies
View Related
Dec 10, 2013
I am using access 2010...and i have a form with a combobox on it...and in that combobox shows a list of employees names. When i currently select the name of the person that i want, it creates their own individual report of their workload.What I want to do is to be able to select that person and it generates their individual report and then attaches it in an email to that individual.
View 9 Replies
View Related
May 9, 2013
I have some code that was written by someone else. I am trying to duplicate this in another database but, can't seem to get it to work. I also don't understand how this code is working as as there is no reference to the combo box in the code behind the print button. But depending on what you select in the combo box the report opens and filters on that selection. The following code is behind the print button. There is a separate combo box, that does not seem to have a connection to the print button. It just has a query behind it for the items to select from.
Public Sub BTNPrint_Project__Job__Numbers_Click()
On Error GoTo Err_BTNPrint_Project__Job__Numbers_Click
Dim stDocName As String
stDocName = "RPTProject Number (Job) Book Year"
DoCmd.OpenReport stDocName, acPreview
Exit_BTNPrint_Project__Job__Numbers_Clic:
Exit Sub
Err_BTNPrint_Project__Job__Numbers_Click:
MsgBox Err.Description
Resume Exit_BTNPrint_Project__Job__Numbers_Clic
View 14 Replies
View Related
Apr 18, 2006
Hi
I need to print a specific form based on the selection made on a combo box.
I have been able to set it up to open the relevant form when the user selects an option from the combo box, but am unable to find a way to be able to print the specific report based on the selection.
I have a command button which prints a report based on the data in my current form, but would like it to also print the specific form based on the combo box selection.
Is this possible, and if so how is it possible?
Thanks in advance,
me
View 5 Replies
View Related
Sep 24, 2013
I am trying to run a report based on a combobox selection. I have three different reports, each for the three different items in the combo box. I just don't know how to code it so when I run the report, it picks up the name in the combo box and opens the correct report. This is what my code looks like for the button that will run the report:
Private Sub Command7_Click()
DoCmd.OpenReport ("Signers Authorized for Check Writing"), acViewPreview, , WhereCondition = [BTrans] = "Check Writing"
DoCmd.OpenReport ("Signers Authorized for Stop Payment"), acViewPreview, , WhereCondition = [BTrans] = "Stop Payments"
DoCmd.OpenReport ("Signers Authorized for Wires"), acViewPreview, , WhereCondition = [BTrans] = "Wires"
End Sub
The problem is, that as soon as I hit the Run Report button, all three reports open up, so it is not reading what is in the combo box. The quotes, "Check Writing" , "Stop Payments" and "Wires" are the actual selections in the combobox and [BTrans] is the name of the combobox.
View 4 Replies
View Related
Nov 29, 2013
I have a report that shows me an employee number that pulls data from the database, once that information is pulled, it shows me the results, I need to look another level down. Is there a way to put in a hyperlink to another report from the original report? example: Employee number 22 jobs worked on: 2214, 2218, 3222 From within this report, I would like to open job 2214 Is this possible?
View 2 Replies
View Related
Oct 31, 2011
I have a query that gives field values like name, age, department, etc for all the staff members in our company. These fields are obtained from many other tables and queries. Now I am planning to generate a report, which would display the results of this query. The catch is I am trying to do it in such a way that I want the user to have the option to select for which staff members he wants to run the report. Example: if I have 10 staff members, the user should be able to select staff3, staff6, staff7 and run the report such that it displays all the field values from the query related ONLY to these 3 staff members.
I could put up some labels and checkboxes for each staff on the form, and then select some checkboxes and get the report to read which checkbox is selected, and then display the concerned results through the report.
View 3 Replies
View Related
Jun 18, 2013
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
View 11 Replies
View Related
Dec 12, 2014
Access 2007
Inside main form f_order
I have a subform f_filter_nycklar
with only one column [Serie nr].
A list of unique serial numbers.
10001B
10002B
10003B
... to
99999B
The subform is based on q_filter_nycklar which in turn is based on tbl_filter_nycklar
I have two unbound combo boxes both based on tbl_filter_nycklar. I want chose starting number [ex 14001B], and end number (ex 14050B) and the subform to filter all numbers from first to last based on that selection.
I later want to copy this selection and past append into another subform on the same main form.
I have tried to set condition in q_filter_nycklar
>=[Forms]![f_filter_nycklar]![F] Or <=[Forms]![f_filter_nycklar]![T]
It does not work
-With condition blank the combo boxes list all available numbers and the subform continuously lists all numbers
-With condition the combo boxes list all available numbers but subform is blank no matter what I chose in the combo boxes (including leaving blank)...
View 4 Replies
View Related
May 8, 2013
I inquired on this original thread [URL] .... to hide/show fields based on two Yes/No dropdowns.
Summary:
1.
If "OtherUnivEmployeesInvolved" = "Yes":
The fields
"OtherUnivEmployeeFullName1"
"OtherUnivEmployeeFullName2"
"OtherUnivEmployeeFullName3" are shown.
If "OtherUnivEmployeesInvolved" = "No": These 3 fields are hidden.
2.
If "OutsideRepresentVendor" = "Yes":
The fields
"OutsideIndividualLastName"
"OutsideIndividualFirstName"
"OutsideIndividualCompanyName"
"OutsideCompanyStreetAddress"
"OutsideCompanyCity"
"OutsideCompanyState"
"OutsideCompanyZip" are shown.
If "OutsideRepresentVendor" = "No": These 7 fields are hidden.
I have this basic validation code when a user hits the save button:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.Tag <> "skip" Then
[Code] .....
Within "1." - If yes is selected, I only need "OtherUnivEmployeeFullName1" required.
Usually skipping FullName2 and FullName3 would be easy using the ctrl.tag "skip" but I am already using the control to show hide these fields so how to do that.
Within "2." - If yes is selected, I need all 7 fields required.
So I need to figure out how to require fields based on those Yes/No selections because right now the form is checking every field regardless of the yes/no selections. I would also need to skip "OtherUnivEmployeeFullName2" and "OtherUnivEmployeeFullName3" everytime.
View 13 Replies
View Related
May 21, 2013
The following code searches for a record and if not found, the user is advised of that and allowed to re-enter the lookup stock number. Obviously, the code is only going to allow valid stock numbers. If the user clicks the 'Home' command button to close the form with an incorrect stock number in the lookup control, it is an endless exercise in advising of the incorrect stock number. I'm thinking of using the 'Home' command button as an escape. How can I tell if the 'Home' button was clicked? I have indicated where I believe it should be put.
Private Sub txtLookupNbr_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtLookupNbr_BeforeUpdate
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim lookup_Nbr As String
lookup_Nbr = StrConv(Me.txtLookupNbr, vbUpperCase)
[Code] .....
View 2 Replies
View Related
Jul 10, 2013
I've been working on this application for a long time, and my combo box always behaved well before, but today I click on the combo box, the list drops down, but I can't select anything. I've also tried typing the item on the list into the combo, which normally works, but I'm not being allowed to type anything there.
View 6 Replies
View Related
