I have a form that is bound to Table1 and I am using the value from a Combo box to filter the records. The Combo Box is populated with values from a different table.
The SetFilter macro is triggered using the AfterUpdate event. When the macro fires, Access asks for a parameter, so I know it's not getting what it needs to complete the macro action. When I provide a value, the macro works fine and returns the appropriate subset of records. I think the problem might be in the WHERE clause of the SetFilter statement; here is what I have.
[ComboBox value on Form]=[Table1]![Field]
Could it possibly be anything to do with using a different table to source the Combo box?
I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:
Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
The subform is called Stock Subform witha combo box called ProductID with the following:
Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName; Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate() On Error GoTo Err_ProductID_AfterUpdate Dim strFilter As String ' Evaluate filter before it's passed to DLookup function. strFilter = "ProductID = " & Me!ProductID
[code]..
The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.
I am trying to select a value from one combo box and on the basis of this selection the other combo box show only those values which have link to the value I have selected.
I have a combo box on a form which has a row source type of Value List in which I've manually typed in 2 entries: Owned, Leased.
I have a query which filters on the results of the combobox, and works fine for when I select either option Owned or Leased.
Is there an easy way to filter all (both Owned & Leased)? I've tried leaving the combobox blank, and tried entering a * in the value list, but they both return no results.
Code:
SELECT Assets.* FROM Assets WHERE (((Assets.Possession)=[Forms]![Home]![Combo56]));
In my subform, I have a field named Container Type (I know you don't need to tell me about Reserve names for Access, but I can't change the names) where when you choose one of the values (a combo box), the values for a second field (Container Code) changes (another combo box) based on the first selection. Then, based on the selection from the second field five other fields are automatically filled in.
I have a form that filter records off of combo, I want the user to run reports based off of the filter selected in the combo box.Here's the code I've been toying around with:
Code:
Private Sub ReQuote_Click() Dim strCustomer As String Dim Filter As String If IsNull(Me![cboFilter]) Then Exit Sub
[code]....
I ripped it off of another thread on here but my needs are slightly different so I couldn't quite get it to do what I wanted.
Im having trouble filtering a combobox on a form. I have two Comboboxes, one is called (FleaTickWorming) depending on what is elected in this cobo, will deside on what is available in the other combo which is called (Product). The form is a continuous form. The filtering works, in as far as it changes the list each time, but it also deletes the item that I have chosen on previous records if the list is different. My code is as follows:
Code: Private Sub FleaTickWorming_BeforeUpdate(Cancel As Integer) If Me.FleaTickWorming.Column(0) = 1 Then Me.Product.RowSource = "qry_FleaProducts" ElseIf Me.FleaTickWorming.Column(0) = 2 Then
I am using master/child form for data entry for packings and the details for this packings (i.e. the products inside a packing is entered in subform and packing master is entered in main form ).
In the detail subform i use a productId field which has look up from 1000 products , so i want to filter that particular combo box based on different types from product master ( from which it choses the products).
How many ways are there to filter that combo box based on different types ( which i have in product master as type,design,material etc).One way of which is combo boxes on main form.
I have three comboboxes and I want to have the first one filter the second one and the filtered second one filter the third one.
Filtering the first one on the second one works, but when I whant to add a third one to it, it just shows blank spaces on the third one.
What I did to the first and the second combobox :
1: I pressed the second combobox. 2: Went to rowsource. 3: filled criteria in on the value i whant to connect the first combox to the second combobox ( [Forms]![Thetableimin].[1stcombobox]. 4. requery the second combobox in programcode on the first combobox.
What I did to the second and the third combobox:
1: I pressed the third combobox. 2: Went to rowsource. 3: filled criteria in on the value i whant to connect the second combobox to the third combobox ( [Forms]![Thetableimin].[2ndcombobox] 4. requery the third combobox in programcode on the second combobox.
How do I make a third combobox that uses the filtered values of the second combobox to filter the third combobox.
I am using this code to filter a List Box based on a Date range and a Combo Box selection:
Code: Private Sub Combo139_AfterUpdate() Dim StrgSQL As String StrgSQL = "SELECT [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _ "WHERE [Date of request] BETWEEN #" & CDate(Me.txtStartDate) & _ "# AND #" & CDate(Me.txtEndDate) & "#;" StrgSQL = StrgSQL & " WHERE Sub_Job = Combo139" Me.SearchResults.RowSource = StrgSQL Me.SearchResults.Requery End Sub
However, It is not working. when I click the Combo box the List Box comes up blank.
I want to create a Multiple Items form presenting Orders table with two Combo Boxes:
1. A combo box to select Order_Category_ID. 2. A combo box to select Order_Type. When 1 (Minor) is chosen in the first combo box it should show Book and Pencil, when 2 (Major) is chosen it should show Car and House.
Examples in the Internet show how to do it on a 'single row' forms using the RowSource property. I tried to use a query like:
SELECT Order_Type FROM Order_Type INNER JOIN Orders ON Order_Type.Order_Category_ID = Orders.Order_Category_ID WHERE Order_Category_ID = [comboBoxOrderCategoryID]
But it sets same values for all records in the Multiple Items form and it should return different values in each rows based on value in the first combo box (Order_Category_ID).
Aaaargh! It's a couple of years since I got my hands dirty in Access and I seem to have forgotten more than I thought! :( I have a simple database which includes tables STOPS (stopID, stop name, routeID, Cost band), JOURNEY(CustID, routeID, stopID), and ROUTES (routeID, Route name, Bus co). I have a main form with the customer number on linking to a sub-form on which the user will select the route from a combo box and then the stop from a combo box. All works fine but I want to filter the stop combo box to show only stops on the route already chosen. I've done similar before but I have tried all sorts and failed to get anything to work. Any help appreciated.
My database is like 1 Training course with many of the employee. My Training course are using the reference no. as the ID of my training.
Now I have a form that have a reference no. as my first combo box and all the employee name are in my second combo box to let the user select and add employee to the training.
So now what i need to do is how to filter out the employee that had already been added to the specify training? For example now user want to select a training 'A' from combo box with only 10 employees (out of 50) are involve in the training.
And now when user select a employee and click save then the employee name will be disappear from the combo box when the second time user want to select another name. So do someone know how to do the filtering of the existing employee in the training?
Here's the situation: I have a list of Crew Leaders. Each crew leader has the option of being the leader of up to three different types of crews, which is determined by checkboxes. In a separate form, I would like to do the following:
The first combo box contains a list of the three types of crews. Once an option is selected, I would like only the Crew Leaders' names corresponding to that selection to show up in a second combo box. However, they are not ONLY part of this selection, necessarily. They may also be a part of a different selection, as they may be the leaders of one, two, or all three types of crews. The third combo box I think I can figure out (the first box filters different services that the crew can provide, but it is irrespective of the name of the Crew Leader, so the services are crew TYPE specific, not CREW specific).
I've done it where the first box filters the second box, but only when the selection in the first determines an exclusive set in the second. What I'm trying to do is have the first box create a non-exclusive set in the second box.
Ex: Crew Type: A Crew Leader (Selection pool): Joe, Bob, Jim, Jerry, Phil, Mark
Crew Type: B Crew Leader (Selection pool): John, Bob, Jim, Mike, Barry, Dan, Mark
Crew Type: C Crew Leader (Selection pool): Rob, Jack, Jim, Joe, John, Mark
Anyone have any ideas? So far you guys have been amazing helps.
how I can modify this code to make it filter results in a combo box by what the user types in to search for.
Private Sub txtSearch_AfterUpdate() With Me.RecordsetClone .FindFirst "[FirstName]=""" & Me.txtSearch & """ OR [Lastname]=""" & Me.txtSearch & """" If .NoMatch Then Beep
Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.
I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.
First Approach: I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria. This is the code I used: __________________________________________________ _______________ Private Sub Preview_Click()
End Sub __________________________________________________ _______________ But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?
Second Approach: On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.
This is the code I followed: __________________________________________________ _______________ Private Sub btnClear_Click() Dim intIndex As Integer Me.cmbCountry = 0
End Sub
Private Sub btnsearch_Click() Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter
Me.frmqrybyCountry1.Requery
End Sub
Private Function BuildFilter() As Variant Dim varWhere As Variant
varWhere = Null ' Main filter
'Check if there is a filter to return... If IsNull(varWhere) Then varWhere = "" Else varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter If Right(varWhere, 5) = " AND " Then varWhere = Left(varWhere, Len(varWhere) - 5) End If End If
BuildFilter = varWhere
End Function __________________________________________________ _______________ After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.
Please help me, I would really want to figure this problem out. Thank you so much.
I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list.
Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean.
(I have attached a word doc. that shows the table relationships.)
I have been using some code from this site to filter my forms and subforms via a selection of combo boxes. However, when I try to filter by either date or callid, I get runtime error 2001 and the debugger drops in at the first forms!filter line. This seems to work perfectly well for the other 3 combos that I use. Is it to do with the type of fields I am trying to filter with or am I missing something integral to the code? Each combo is named Filter1, Filter2, etc and the name of the field to filter by is placed in the tag line for the corresponding combo box.
Private Sub Command13_Click()
Dim strSQL As String, intCounter As Integer ' Build SQL String. For intCounter = 1 To 5 If Me("Filter" & intCounter) <> "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _ & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _ " And " End If Next
If strSQL <> "" Then ' Strip Last " And ". strSQL = Left(strSQL, (Len(strSQL) - 5)) ' Set the Filter property. forms!frmoutershell.frminnershell.Form.frminnerinn ershell.Form.Filter = strSQL forms!frmoutershell.frminnershell.Form.frminnerinn ershell.Form.FilterOn = True End If forms!frmoutershell.frminnershell.Form.lblfilter.C aption = strSQL End Sub
Any help would be appreciated as I am getting quite frustrated with it only working on certain parts of the form.
I have a form that we are creating to issue project numbers. We have about 80 different clients. I have set up a combo box for the client name, where we can choose the client from the drop down menu. We want the form to filter according to the client name that you choose. For example, if you pick Lawrence as the client name, the next combo box is the project name and I want it to only show the project names that have been assigned to Lawrence in the past. Can someone please help me on how to do this and if it is even possible at all? Thank you so much for your help!! I have been wrestling with this for about a week and a half now.
Anyone help I have 2 combo boxes cd group and cd name when I select CD group the records only associated yo that cd name display with that group.
Anyway, also the name of songs form works too,. So then now when I go from record to record the records display right with the songs and not with the cd group and name when I select Mormon Tabernacle Chior and name God Bless Ammerica it disaplys for all the records any help.
Please help. My inability to achieve this is very frustrating.
I have a tabular form based on a query, which returns a large number of records. I need users to be able to filter the records to display data relating to what they have entered in a number of combo boxes.
For example I have fields showing Product ID, Manufacturer ID, Purchase Order number and due date. Each of these fields has a corresponding unbound combo box where users can select values that appear in the table. I need to be able to filter the form based on what is in these combo boxes. I.e. Filler for a product ID to show all purchase orders numbers and due dates relating to that product ID or filter for a manufacturer ID and due date to show all Product IDs and purchase orders relating to that a manufacturer and date.
Ideally this would be achieved by pressing a command button.
The query, table and combo boxes are all built and work fine but I am unable to get the filter to work, I am using Access 97 and am not very familiar with visual basic. Any help would be appreciated.
I have a sub form that I enter date range in 2 text box's txtstartdate and txtenddate this is filtering field "date raised" and I have combo box call cboclient this is filtering field "client name" this code is behind a command button after hitting the command button it open's the report and it show's all records with in the date range and with client name enter in cboclient works great. problem is I have now added another cboclient2 to filter client name twice so now I enter a date range in txtstartdate and txtenddate put a client name in cboclient and a client name in cbocleint2 hit command button it open's the report but doesn't apply the date range to cboclient2 it just show's all records with that client name
Code: Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long
I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to the Else statement. I've highlighted the if statement that keeps failing in red.
Code: Private Sub btn_Search_Click() 'create variables to store the combo box values Dim str_Country As String Dim str_Vendor As String Dim str_Survey As String
I thought I was in the home stretch of my project, everything worked great when I was messing with 200 records. Now that I'm messing with 2000+ records, things are very slow. So a search form redesign!
I have a form (frmSearch) that has a subform (frmSubSearch) embedded in it. The frmSubSearch is a datasheet only that is just pulling its info from a query (qrySearch). qrySearch has about 8 columns of data in it.
On the main form I have a combo box that is feeding its list from the qrySearch using a SELECT DISTINCT statement. So a user selects an item in the combo box and my After_Update fires. This sets a filter on frmSubSearch. I have three of these combo boxes that can add to the filter and they work great so far.
But I want to have the combo boxes filter themselves based on whats left on frmSubSearch. So if a user selects something in the 2nd combo box, the sub form filters and updates, but then I want the other two combo boxes to only have valid selections, and not something selectable that would wind up giving me a blank sub form result.
I have tried using .Requery in various ways, but its not working. I have also been looking into the idea of Cascading Comboboxes, but these don't seem to quite fit what I'm trying to do.
I am creating a query that should filter records of events based on multiple fields. The filters should work with any combination of field criteria, but only two of the four field are working properly, as follows:
1. a text box for searching with event name (free text) - this is working; 2. a combo box to filter events by country name - this is working; 3. a combo box to filter events by event's keyword (category) - this is NOT working; 4. a combo box to filter events by year - this is NOT working
