Displaying A Filtered List
Dec 5, 2006
Help !! - Can anyone help. I have had a quick look through the previous threads and can't seem to find anything that may help me. Has anyone got any ideas.
I have a table which has many columns, one of these columns is called actions. On the design of the table, the action column is a Text Datatype and on the lookup tab the row source is a typed in list of actions.
A form has been created from this one table. A drop down box has been created which looks at the action column and so drops down to give us our typed in list of actions.
Users will select the current action from the drop down list.
What I actually want to do is depending on what the current selected action is, that the drop down box only shows a subset / filtered list of actions.
I know how this is designed is not the tidiest/slickest way of doing things, But unfortunately, I have inherited this from someone else who has been running the database this way for over 6 years !!
View Replies
ADVERTISEMENT
Dec 5, 2013
I've attached a link below:
Basically, its a database for an entertainments agency, I've got a form for 'Artists' which shows the performers on the agencies books. I've tabbed the form so one screen shows the artist details and I want the second screen to show the bookings that the specific artist has.
I've linked to the 'bookings' table on the subform successfully, but I can't figure out how to filter it so it just shows the bookings that the specific artist showed has.
i.e.: I want it so that The Deltatones booking tab ONLY shows the deltatones bookings.
Here's the dropbox link : [URL] .....
View 8 Replies
View Related
Jun 8, 2005
I have a form that contains a pretty large list of items and several columns and the list can be sorted by each of these columns. When a user double-clicks on a particular item, another form pops up that lets the user view/modify the item. The problem I'm having is that I still want to be able to use the navigation buttons in that second form, but right now its just saying 1 of 1 (Filtered).
The way I'm applying the sorting is by updating the Rowsource field of the list with an "Order By" statement
Any help would be appreciated
Adam
View 2 Replies
View Related
Oct 9, 2006
Ok access noob here and a first post as well...I have to filter a table that has children and teachers.I made a form with a combo box of teacher names then filteredon the teacher name.in the subform I have to create a matrix of the children thatare assigned to that teacher.it would be something like thisteacher date child name start endthe teacher and child name are in tblmainthe date start and end values have to be in tbldata1so the beginning they want to choose a teacher and a dateso I shifted and put the teacher in a combo box and tied it to nothingbut the subformso combo box teacher and field datelinks to subform are teacher;datecombo box(teacher name)==> Ms. Eve text box(date)==> 01/01/06but they want a filtered list on teacher so the subform would have to beteacher date child name start endMs. Eve 01/01/06 mary lamb Ms. Eve 01/01/06 billy bullMs. Eve 01/01/06 bob jonesthen they could put in start and end for each childI don't see how I can get the child names filtered thru a query (thisI can do) BUT then to be able to edit the fields and put this datainto a new table.IS there a way to do this?a big help for any ideas or pointers as I am lost on this
View 3 Replies
View Related
Apr 17, 2014
I have a form which displays records based on a selection from a list box on the form.
The default record displayed when I load the form is record 1 from the table, this is not an issue when I initially load the form but I also have options to filter the records displayed in the list box using various SQL statements, depending on the options selected.
However, when I apply one ofthese filters to the list box the default record displayed on the form still defaults to the first record in the table, which may not be included in the filtered list box.
Is there a way I can default to the first record listed in the list box rather than the first record in the table.
View 2 Replies
View Related
Mar 21, 2013
I have three linked tables [Regulator], [RuleBook] and [Rules] (each has a corresponding form for data entry).
In the "Rules" form, when you select name of the regulator, the rule books dropdown down is filtered to show only the rulebooks for that regulator. the code I use is:
Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator
Me.[ShortReg] = Me.Regulator.Column(3)
End Sub
Basically, if I select the FSA as the regulator, The Rule Books drop down is filtered to only show the FSA Rule Books. If I pick OFT, I get the OFT list etc. This all works fine.
In the [RuleBook] table, each entry has a yes/no tick box called "Active".
The reason for this being that the regulators change their rule books quite regularly, but from an audit perspective, I can't delete or overwrite the old ones once they are no longer valid.
What I want to do, is further filter the rule book list to those where Active=True so that when I select FSA, I can only choose from their current rule books and not the ones that are no longer relevant.
View 7 Replies
View Related
Dec 15, 2014
I've created a form with two textboxes and a listbox. What I want to do is to show in the listbox the registers filtered by the 2 textboxes. But I would like that the listbox refreshes while typping in the textboxes.It was no problem to do filtering with only one textbox. The rowsource of the listbox is a query in which I set in the criteria Like [Forms]![Form1]![Textbox1].[Text] & "*"Then I write the code for the change event of the Textbox1:
Private Sub Textbox1_Change()
Listbox1.Requery
End Sub
With this I have no problem. The problem is when using the 2 textboxes. I write the same in the criteria, Like [Forms]![Form1]![Textbox2].[Text] & "*", on another field but it doesn't work. I also write the code for the second textbox:
Private Sub Textbox2_Change()
Listbox1.Requery
End Sub
The listbox doesn't filter with both textboxes. When you type in one of them, the listbox filters from it, but when you type in the other textbox, the listbox shows the whole table filtered with the characters of the second textbox.
View 10 Replies
View Related
Mar 8, 2013
I have a table with a list of Students. While the academic year is in progress those students are in an "Active" status. Once the year is finished those students are moved into (hopefully) a "Passed" status or some other status that is not considered "Active".
Throughout the rest of the database I have many forms that refer to the Student Listing. As an example we have a Test so we select the Student performing the test using a Drop Down List and go from there.
I have set search criteria to the Drop Down List via the Combo Box's Query to only display active students and it works fine during the Academic Year. However if I have to review a test from a previous year then the Student Drop Down List no longer displays the name but their ID# (Primary Key) because the Student's status has now changed and no longer meets the query criteria.
Is there a way to use the Filter/Search Criteria to chose from a listing of Active Students but once selected it will display the Student name regardless of their status?
View 2 Replies
View Related
May 26, 2014
I have a table with a huge master list (some hundred thousand) of unique bar code tags, which either have 8 or 12 digits. (Valid characters are "012 345 678 9ACEFHJKLMNPRYXW"). I created a form, which is automatically looking up valid records as the string is entered position by position from left to right. This is working fine and sufficient in 99% of the cases.
Sometimes characters are not readable on the original document, so I need a tool/form, were I can enter only the readable positions of the code and the tag list is then filtered accordingly. For that, I created a separate form with 12 text boxes for each possible position of the bar code and a listbox. This list box should be filtered as the readable characters are entered in their respective positions/textbox.
View 1 Replies
View Related
Oct 16, 2006
Hi,
I am using a list to display a query. The query has a few fields and one of these fields retreives its values using a look up. The problem is that, when I run it, the value of the look up field displays numbers instead of text.
Any help to fix this problem will be very much appreciated.
B
View 3 Replies
View Related
Sep 22, 2013
I have created some tables using CREATE TABLE but they do not appear in the list of tables although they are there. How to make them appear.
View 1 Replies
View Related
Feb 6, 2005
I have several combo boxes and textboxes showing quanity,productname,
size, and price. How can I pass all of this info in a single row that also calculates
the quanity and price. Also, multiple selections can be added, so several items can
can added in other rows. THe ability to cancel each row would be required as well.
Thanks
View 2 Replies
View Related
Sep 30, 2011
I created a multivalue list in my table. It contains a list of names and a checkbox. I designed the form based on this and am able to check off multiple names. When I go back to look at the table, it stores in each name separated by a comma. So far so good.
When I create a report to display the data, it displays it as 1,2,3..etc., instead of the names separated by a comma.
What do I need to do to display the names ?
View 5 Replies
View Related
Aug 8, 2006
i have two tables, tblgroups and tblrecipes
on a frmReview i am displaying the groups in a list box so that user selects a group and sees relevant recipes ( which i am displaying in a subform after quering from lstgroups).
Default value for lstgroups is "[lstGroups].[ItemData](0)" so that it always displays first goup recipes... (otherwise subform dont displays anything when i first open the frmReview)
is it possible (which i am sure it is) that when i first open frmreview, the subform displays all recipes in all groups (whole database), so that when i select one group in lstgroups then it run query and filters the desired recipes. May be we can put "all" in lstgroups as first item so that i can use the default value..
thanks in advacne
View 3 Replies
View Related
Apr 10, 2005
I am working on my DB for work and would like to display all of the report names that are listed in the Reports-Objects of my DB.
I would like to display a Form with two boxes. The left box would display the names of the reports as listed in the Objects-Reports. When a report is highlighted in the left box it would give a description of the report in the right box. When you double click on the report name it would open the report.
Since I am fairly new to Access could you give me an example of what I would like to accomplish? Your assistance is appreciated.
Thanks,
View 6 Replies
View Related
Aug 2, 2005
I would like to know how I can put filter fields on a DAP that when you press a button will then filter a table. Also, does anyone know of any good references on working with DAP, becasue from what it seams like, there isn't any technical information out there.
View 1 Replies
View Related
Sep 27, 2004
Hi everyone, I am new to writing queries in access and I have got stuck with this one.
I have 2 tables containing similar data from 2 different paradigms.
The main columns I am interested in are not unique - labelled ID and Delta Ex (a score). The same ID values can occur more than once in the same table, so I am interested in calculating the average Delta Ex score for each ID value in each table.
I then want to ask what ID values occur in both table 1 and table 2 and then calculate the average score of each common ID values across both tables. i.e. ID 1 score = avg(table1.delta ex, table 2.delta ex).
I then want to rank the top 50 scores.
So far I have the following to find common ID values, with a score above a threshold set by the input string threshold. These are ordered with in descending score value.
SELECT [MODEL 1].[ID], [MODEL 1].[Delta Ex]
FROM [MODEL 1], [MODEL 2]
WHERE ((([MODEL 1].[Delta Ex])>[threshold]) And (([MODEL 2].[Delta Ex])>[threshold]) And (([MODEL 1].[Accession])=[model 2].[accession]))
ORDER BY [MODEL 1].[Delta Expression] DESC;
With a second query I can select the top 50 scores:
SELECT TOP 50 [query 1].ID, [query].[Delta Ex]
FROM [query 1]
ORDER BY [query 1].[Delta Ex] DESC;
However, I can't for the life of me work out how to obtain an avg score for each ID value.
Any help would be very much appreciated.
J
View 2 Replies
View Related
Jan 25, 2006
Hey all, I have been trying to filter my report However It doesnt seem to be working the way I would like it to. I have the ability to filter on my form, so I can search Wood* and then there will be 27 records displayed so I can view each one speratly. however now I would like to have it on a report. I would like to then click the report button, and view those 27 records on my report as the layout that I have provied I can see multiple on a sheet. Any ideas?
View 4 Replies
View Related
Feb 9, 2012
I have two tables, say customers and purchase orders. I am trying to write query that returns a list of customers based on a wild card purchase order search.(FYI, the query is being used in a VB.NET application)
Code:
SELECT Customers.WarehouseID AS Customers_WarehouseID, Customers.Name, PurchaseOrders.PurchaseOrder, PurchaseOrders.WarehouseID AS OBRs_WarehouseID
FROM Customers INNER JOIN OBRs ON Customers.[WarehouseID] = OBRs.[WarehouseID]
WHERE (((OBRs.OBR) Like '" & strPOFilter & "*'));
This query almost works how I'd like it to, except in cases where a single customer has multiple purchase orders that begin the same way. So, for example, if customer "mike" has purchase orders "00554" and "00553", if you search for "00", mike will be returned twice. How can I modify the query to only return the customer once?Is it possible, or should I filter out repeated offenders in my code?
View 2 Replies
View Related
Sep 15, 2005
i've created a form base on a query, and other users will work on it (mainly filter). then i created a report base on the same query...
what i want is a "print" button on the form which will print the report, not all records, but only the filtered records after user applied their filters...
since users will apply different filters each time, it's quite impossible to make all different filters as query...
any idea?
View 12 Replies
View Related
Jan 27, 2006
Hello all,
I have looked through the forum, found some things, but not sure if they help with what I need specifically. I tried looking at the microsoft website, but can't really follow some of the things they have there.
On clicking a form (called Manager), I would like a list of some fields from a query (called queryMgr) to pop up, so that I can put in the criteria as I would if I just used the query, and then submit or run, and the form gives the filtered results. I don't want users to open the query and put in their criteria, I just want them to go to the form directly
When I click the form, I want the following to pop up at the same time so I can fill in the criteria:
Date
Vendor Number
Invoice Number
Check Received (this is a Yes or No field)
and then when I click submit or enter, the form gives me say 5 records that match this criteria.
Thanks a lot!!!
View 1 Replies
View Related
Aug 8, 2006
I'm new to Access and am trying to work out the best way to achieve the following.
I need a way for users to be able to filter the data which is displayed by forms or output in reports. For example, suppose that a user wants a list of all customers in either of two specific towns with a specific date of birth.
Obviously this could be achieved using a query and setting the criteria of the towns column to be:
"Anytown 1" or "Anytown 2"
and setting the criteria of the Date of Birth column to be the specific date required, e.g.
"08/08/2006".
However, so far everything about my database has been very user friendly with forms for data entry and presentation, so I don't really want to force my users to start creating their own queries.
The alternative I'm thinking of is as follows. Provide a query to pull together the required information (from various tables) but without any specific criteria. Then create a form with a field for each of the columns in the query, and allow the user to type their critieria into these. Then, place If statements as the criteria of the query to check if there is anything typed into each box on the form and, where there is, set it as the critieria for the query, then run the query.
With the above in mind, I have two questions.
1) Is there a better, more "standard" way to achieve a system of allowing users to create customised filtered data without requiring them to write queries?
2) In some cases, e.g. like with my specification of a town above, it would be good if the user could select the town they want to filter for from a drop-down combo box, rather than typing it in and risking entering details incorrectly. However, this would obviously allow them to select only one town, and not two as was required in my example. Is there an equivalent to a combo box which would allow multiple selections?
Thanks in advance if anyone can help me - and please remember that I am new to Access so whilst I want the best possible solution, I need a plain English explanation!
Gary
View 4 Replies
View Related
Apr 22, 2008
I have a text column that has some Letter in it, the format is #A-##A where A = Alpha, the last Alpha is also not required. What I want to do is get a list of all the first A's, so like i have 11P-23A, 22G-45B, 33K-67C and I want to get A,G,K is this possible?
View 3 Replies
View Related
Jan 25, 2005
Hi
I want a filtered pop up form to accept new records.
The archive posting that I found suggested that I insert
[MyForm]![LinkedField] as the default value of the linked field.
Any suggestions gratefully accepted.
Steven811
View 5 Replies
View Related
Nov 1, 2005
Ok heres the problem, i seem to have a little bit of a mind block at the moment so help would be greratly appreciated.
Basically there are two tables involved, tblBikes (which contains all information on the bikes) and tblHires (which contains all the information about hired bikes) these are linked with a one to many relationship as each bike can be hired out many times.
I have a button on a form containing bike information which opens the hires form filtered by the bike ID of the record selected in the first form. When I add a record to the hires form i need the bikeID to be automatically input into the field.
View 14 Replies
View Related
Mar 14, 2006
I have a button that opens a second form and shows filtered data based on the selection of a ComboBox from the initial form. That all works nicely, I now wish to sort the Data in the new form by [POID].
The code I'm using looks like this;
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FRM_AWMPonum"
stLinkCriteria = "[ClientID]=" & Me![Combo0] & " AND IsNull(DelDate)"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
What changes do I need to make to the code to sort the data?
View 3 Replies
View Related
