Filtering A Subform Using A List Box
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 Replies
ADVERTISEMENT
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
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 15, 2013
I have a list box which gets it's data from a query. I would like to filter this query to show only those rows matching a value displayed on the form.
The form displays data from a table and has a text box named PIDLocal.
The query returns a list of PID values and some text SELECT PID, Country, RefCode FROM Query1 The PIDs are integers.
I want the list box to contain only those rows where PID = PIDLocal.
I have tried setting the criteria in the row source query to PID = [Forms]![Show_Details]![PIDLocal], but nothing is returned. If I 'hard-code' a value (eg PID=247) I get the desired result.
View 1 Replies
View Related
Feb 22, 2007
http://allenbrowne.com/ser-19.html
I use the call back function to generate my list of reports in a list box.
I chose to use this method as it seems like an easy way to do what I want, and to a certain extent understand how it works.
What I would really like to be able to do, is have it filter the list of reports.
For example, some reports are for me working behind the scenes, and also so many are subreports that go in to the main report so dont need listing.
I would like to have a report names 'Clients Full Report', 'Clients Medication Report' etc on the list (I dont mind changing report names as I think that will be the key to making it work) So many of my sub reports have client in its name, so if a report could be named '1Clients Main Report' and the code looks for the number 1 and removes the 1 from the list so it displays Clients Main Report on the list.
So its just a way to make the list box only include specific reports based on a number.
Any suggestions?
View 6 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
Mar 24, 2005
how do i filter a form based off what you select in a list box?
thanks
chad
View 7 Replies
View Related
Jul 19, 2013
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]));
View 4 Replies
View Related
Apr 21, 2014
I am using a form in which i am filtering the results in the list box based on the textbox value. I am dynamically switching 3 row sources for the list box.
My Need is that the results produced in the listbox should get filtered again when typing in the second text box i.e based o the country name.
View 1 Replies
View Related
Nov 12, 2014
My database is centered on a main form where users select pieces of information from ComboBoxes (primarily) and submit.
The first part of the form, they must choose from a list of our clients, which is a table with with roughly 5000 rows.
Later on in the form, they must choose a contact person from that client. While Client's may have several contact names (which need to be stored in a table), only one name is necessary for the form portion.
How can I make this Contact Person ComboBox only load names attached to the specific Client selected above?
As it stands right now, the Client List has its own table (along with additional information), and Contact Name is a separate Table with a look up field of Client List.
View 1 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
Dec 2, 2014
I currently have a form that only shows data based on a specific record id. I have placed a list into that form though that has multiple record ID's listed ( It's a log). How do I get a List to Filter to just the Record ID that corresponds to the current form Record.
What I have are as follows:
A form that has client information : Address, phone numbers, Etc.
On that form I have Pages. one of the pages contains a List.
That List however contains information from multiple clients based on Point of Contact ( Call Log)
What I want is a way to narrow the List to only show those that corresponds to the Current Client Record.
Is this possible? If my approach is wrong, which is a better way?
View 1 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
Jan 5, 2014
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.
View 13 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
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
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 3 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 9, 2015
I am using Access 2010. I have a subform(continuous form) that I display as a datasheet and allow filtering. I display the record count by using Me.Recordset.RecordCount in the subform's form_current event. This value updates properly based on the column filtering except when the filtering displays 0 records.
When there are 0 records, the form_current event does not fire and I cannot figure out how to trap when this occurs so that I can display '0 records found'. How I can tell when the column filtering causes 0 records to display in the datasheet? Is there an event that fires when the user filters using the column headings arrow?
View 5 Replies
View Related
Nov 5, 2014
I have not used access since Office 97 so, I see many changes. A while back I used Access to create some minor tracking databases. Now, with 2010 I'd like to modify the database that I am currently using. I have a main form that tracks daily trips or tasking(s). Within the form are two sub forms that track:
1. People assigned to the trip through a combo box; and
2. Itinerary for this trip.
I have been asked to add a Trip justification(s) through a combo box which will have several selections avail that can be chosen and would apply to each trip. These can be added to by double clicking the justification (figured that one out). What I would like the sub form to do for each new trip that is entered is to display only these values as a single selection rather than 12 times (example) if there are 12 trips that have the reason justification as part of the trip/tasking.
I just can't seem to make this work. The personnel one and itinerary one work just fine (previously existing). I just can't seem to get the new one to work. I have checked that the new table is connected through the relationships. In the exact same manner. The new form has the ID (Key) OPID (to link each trip separately) and justification text field.
View 6 Replies
View Related
