Combo Box Subform Filtering Problem
Sep 12, 2006
Hi all
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.
Regards
Jason
View Replies
ADVERTISEMENT
Apr 27, 2013
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.
View 11 Replies
View Related
Jul 1, 2014
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.
View 9 Replies
View Related
May 30, 2013
I have these 3 combo boxes filtering results into a subform.
Code:
Private Sub Combo5_AfterUpdate()
If Len(Nz(Combo5, "")) > 0 Then
FindRFQsubform.Form.Filter = "[RFQ Title] = '" & Combo5 & "'"
FindRFQsubform.Form.FilterOn = True
[code]...
View 1 Replies
View Related
Mar 11, 2012
Let's assume we have 3 tables:
Order_Category (Order_Category_ID, Order_Type_Name) with 2 records:
1, Minor
2, Major
Order_Type (Order_Category_ID, Order_Type) with 4 records:
1, Book
1, Pencil
2, Car
2, House
Orders (Order_Category_ID, Order_Type, value) with 2 records:
1, NULL, NULL
1, NULL, NULL
2, NULL, NULL
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).
View 5 Replies
View Related
Sep 1, 2006
I almost have this form done.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
:confused:
View 1 Replies
View Related
Sep 1, 2006
I have a form that is almost complete
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
View 1 Replies
View Related
Jun 3, 2014
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.
View 14 Replies
View Related
Nov 22, 2006
I have an SQL query that filters a subform based on the selected item in an option frame that runs on the after update event of the option frame.
Forms!fsubPrjPersonnel.RecordSource = SQLText & WClauseWhen I try this out, I get the following error message:
2450 - xxx can't find the form 'fsubPrjPersonnel' referred to in a macro expressions or Visual Basic code.
I then ran the following snippet and found that the form is not recognized as being loaded when it is "open" as a subform
If IsLoaded("fsubPrjPersonnel") Then
MsgBox "form found"
End If
If I open the subform as a form as well as having the main form & subform open, i.e. open a second instance of the subform from the database window, it filters fine.
I have been able to requery the subform so where am I going wrong?:confused:
View 5 Replies
View Related
Mar 31, 2005
I have a sub form to which I want to apply filter buttons.
I have used the wizard to set these up.
The problem I have is that when I try to filter the SubForm the MainForm also filters.
The forms are based on tables and not queries.
Cheers
Gordon
View 1 Replies
View Related
Mar 6, 2006
Yes, Ive searched the forum and Im still stuck on this most basic of concepts - please can someone point me in the right direction?..
I have a Form [frmClient] and a subform [sbfrmCommsList]. The subform links nicely with the main form via [ID]. However I want to add a second filter on the subform selecting only [Type] = 2 rows (from the child table)
This is in the subform:
Private Sub Form_Load()
Me.Form.Filter = "Client=" & Me.Client & " AND Type =2"
Me.Form.FilterOn = True
Me.Form.Requery
End Sub
It still displays all records (Both [Type] 1 and 2)...
thanks..
View 2 Replies
View Related
Sep 13, 2006
i have a form with a subform on it. the subform has 2 columns - one of which is hidden. Assume the other column is called "type".
i was wondering if there is an easy way to 'hide' records in the subform which have "type" = "Car"
thanks
View 3 Replies
View Related
Aug 1, 2005
ok, i've discovered filtering does not work on subforms so i have searched the forum and modified some code to this:
Dim strSQL As String
strSQL = "Select * from tasks where"
strSQL = strSQL & Me!frmSubProjects.Form!Project = 2
Me!frmSubProjects.Form.RecordSource = strSQL
error message is " can't find the field 'frmSubProjects' referred to in your expression. (line3)
Please note a 'mouse over' of Me!frmSubProjects.Form!Project in the above code does show a result (first 'Project' value in subform) - so what am i doing wrong??
thanks in advance,
Paul
View 3 Replies
View Related
Aug 23, 2005
I have a tbl_product with PK_productID as primary key and frm_search with a subForm in it, How do I filter my subForm in a form with a single click button?? well... at first i did it with open new form with a specific data to display.
and from now on i want with a single click in my search form the result will show up in my subform but i don't know how to do it T_T
anyone can help me with this problem??
thnx...
View 1 Replies
View Related
Jan 8, 2006
I've finally figured out how to populate my list box with radio buttons (3 companys listed on the radio buttons, you click one and employees for that particular company populates the list box). Now I'm trying to figure out how to click on an employee in that box and have their contact information populate the subform that holds that info.
After trying unsuccesfully with VB, I considered using a macro that would open the subform where the Last Name = the Last Name clicked in the list box, but I couldn't figure that out (couldn't figure out what the name would be for the selection in the list box) but I would prefer the subform be there permanantly in "add" mode untill a selection is made in the list box.
I'm code ignorant and rely on google and what books I have for reference / copy-past.....any ideas?
View 3 Replies
View Related
Jan 8, 2006
I've finally figured out how to populate my list box with radio buttons (3 companys listed on the radio buttons, you click one and employees for that particular company populates the list box). Now I'm trying to figure out how to click on an employee in that box and have their contact information populate the subform that holds that info.
After trying unsuccesfully with VB, I considered using a macro that would open the subform where the Last Name = the Last Name clicked in the list box, but I couldn't figure that out (couldn't figure out what the name would be for the selection in the list box) but I would prefer the subform be there permanantly in "add" mode untill a selection is made in the list box.
I'm code ignorant and rely on google and what books I have for reference / copy-past.....any ideas?
View 1 Replies
View Related
May 17, 2005
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.
View 3 Replies
View Related
Oct 9, 2006
Hi all,
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?
Thanks alot@@!!
Regard,
alex
View 5 Replies
View Related
Oct 21, 2004
Ok, so I invoke your help once again.
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.
Thanks in advance!
-Jason
View 14 Replies
View Related
Jul 2, 2013
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?
View 2 Replies
View Related
Dec 18, 2011
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
[code]....
View 1 Replies
View Related
Jun 21, 2005
I have this form where there's mention of CompanyID
In a subform, I have the information about all the contacts of this Company. In order to see full detail information for a particular contact (of that particular Company) I have a Combobox with a query.
At least: that's the whole idea. Unfortunately it doesn't work in Access:
SELECT Contact.ContactID, Contact.Name
FROM Contact
WHERE ((Contact.CompanyID)=(Me!frmCompany![ContactID]));
Can any help me? I need to do more of the sorts of queries... and I can't figure it out how to use values from forms and parent-forms.
Any help is appreciated,
Jazz
View 1 Replies
View Related
Jun 5, 2013
I have a username combobox and a number combobox filtering a subform. If there is a userName but no number I want to pull all the records for that userName regardless of number and vice versa with a number and no userName. And if they both have something then both are being used for the filter.
I have been trying iff statements and isnull in query criteria and cant seem to find what I need.
View 1 Replies
View Related
Jul 29, 2014
The main form has fields for Record ID, Position, and Revision. The subform has fields for ID, Position, and Revision. There is also a field for "task". I have over 15 positions and 495 tasks. Each position has a set number (around 15 or so) tasks associated with that position.
I have the Position in the main form and the subform linked so when I select a position on the main form, the subform loads the same position. The question field (drop down) is "fed" from a query which contains all positions and all tasks. What I want the subform to do is when I select a position the drop down ONLY displays the tasks associated with that position.
I tried using an event procedure "On Click" and "After Update" to no avail. The embedded macro was:
Apply Filter Where condition= [tbl_Detail Cost Information]![Position]=[qry_attempt 1]![Position].
I also tried the reverse Where condition =[qry_attempt 1]![Position]=[tbl_Detail Cost Information]![Position]
The result is a fully loaded (all tasks) drop down.
View 4 Replies
View Related
Feb 18, 2006
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()
DoCmd.OpenForm "qrysumcountry subform", , "Country", "Country = [List4]"
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.
View 2 Replies
View Related
Aug 3, 2006
Need some help here....
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.)
Thanks a lot ahead of time!!
ScrmingWhisprs
View 13 Replies
View Related
