Filter Query Combobox Options
May 11, 2015
I created a form that has about 8 different filter options that can be used to filter a query. I used the filter by form table to set it all up and I can get each of them to work individually as well as 2 work together... but as soon as I added the information for the 3rd, I stopped getting results... in fact nothing happens. Even with the code for all 3 and selecting just 1 option, nothing happens any more. I have
cbopersonnel that is supposed to look up values in 4 different personnel columns
cboshift that looks up all shift work
cboworkdef that looks up what the job was (just a title)
Having just 2 in the filter by form works great, but adding 3 screws everything up. And I'm not talking about selecting all three (I know that would limit the output more) but I mean, with all 3 setup and selecting shift as "nights"... nothing gets filtered anymore. I have cbopersonnel on "look for" tab and everything else is on the next "or" tab. I tried to set it up on individual tabs but access combined it. I also tried adding cboshift to the "or" tab with everything else and access sent it back to "look for". All of the cbo references are under their respective search area... so in the table, the shift column has the cboshift lookup value. Is it just because I'm not using quotes around the cbo output? That doesn't make sense to me though. Anyway below is the lookup value for the cboshift.
[Forms]![Log Book Lookup]![cboshift]
View Replies
ADVERTISEMENT
Jul 31, 2014
I'm trying to use a cascading combo box to filter the options that can be entered into a second combo box.
In frmBabies, a Mode of Delivery is selected from 8 options. 4 of these are "normal" deliveries and 4 are caesarean sections.
A second combo box selects the Indication for Operative Delivery. The table tblIndOpDel contains options for normal as well as caesareans. I would like to filter the indications so that the user cannot enter an indication for normal delivery for a caesarean section.
Both tblIndOpDel and tblDelMode have a foreign key to tblDelModeCat, which categories deliveries as normal or caesarean.
I'm stuck on how to proceed from here. Sample database attached.
View 2 Replies
View Related
May 14, 2014
I have a ComboBox which looks up the records in a query. The query contains the names of current employees. The table upon which the query is based lists Employees and their start date and finish date. The Query is based on finish date being Null (ie they're still working here).
In the main form which details tasks, I would like the ComboBox to just list current employees (which it does as it's based on the query) but when an employee leaves, their name is deleted from all previous records.
Is there a way to keep the ComboBox options limited to current staff, while also keeping the names of previous employees in the form?
I checked the table which the form is based on and it still has the values, so I'd like to show in the form too as that's what people will be working on.
View 1 Replies
View Related
Apr 22, 2005
I have 2 comboboxes, the first one is called "activity", whereby I have 3 options to choose from, and the second is called "level".
When I click an "activity", for example Drawings, I want the "level" combobox to list a unique set of options for that category. and if I click on a different "activity" for example Planning, I want the "level" combobox to show a completely different set of options.
If anyone can give me any help on how to do this then I would be very grateful!!
Thanks
Angela :eek:
View 2 Replies
View Related
Jul 23, 2014
I am using Select Case to filter a form on open and it works fine when I have this:
Code:
Case Is = 2
Forms![Open Opportunities List].Form.Filter = "[Employee] = 4"
Forms![Open Opportunities List].Form.FilterOn = True
But if I want the form to open filtering multiple employee records, like this:
Code:
Case Is = 6
Forms![Open Opportunities List].Form.Filter = "[Employee] = 2 & " And 9 & " And 10 & " And 11
Forms![Open Opportunities List].Form.FilterOn = True
I get a type mismatch error and I am stumped as to why and how to overcome.
View 14 Replies
View Related
Jul 12, 2005
OK, Im new to access, and have a database of 210,000 people.
I have to fit a lot of data printing sideways. Also in a certain format...
First thing...
There is a field that may contain RKS with other info or RKS alone. These must all be red along with the addresses, but thats it. But when I change the font color it changes the whole database. Thats it.
Other thing... I want to do a sort of exclude filter? We need to do some printing... again with the rsk, but we want to exclude anything that has rks in a specific field.
The last thing is print options/page setup...
I want to condense it as much as possible from left to right so al the needed information can be printed.
Here is how we want it printed...
__________________________________________________ ______________
Street here, house # | then other information in approated columns here
customer #, name here |
--------------------------------------------------------------------------
the problem is, street address, house number, customer number, first and last name are all in different columns.
is there anyway i can print it this way?
also the column names need to be extended downwards? some of the names are too long
like |This is column a| but we want
|this is |
|column a|
so it isnt as wide. and I can fit more columns on the page.
Thanks a lot for your help, I only have 2 hours to get this.
Martin Seidl
Oh yeah, if it helps, Im stuck with access in german :(
View 7 Replies
View Related
Dec 31, 2014
In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.
My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.
View 3 Replies
View Related
Sep 1, 2005
I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:
ID No, Ref No, Start Date, Comp date, Price etc.
I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.
What is the easiest way to do this?
Any help would be greatly appreciated.
View 4 Replies
View Related
Jan 11, 2006
I'm building a filter using comboboxes and I'm having trouble with two of them. One is to filter by records that are completed or by records that are not completed. The other combobox is to filter by records that have comments or by records that have no comments.
Filter7
Row Source Type: Value List
Row Source: Completed;[tblMainData.Status]='Complete';Not Completed;[tblMainData.Status]<>'Complete'
Column Count: 2
Bound Column: 1
Filter8
Row Source Type: Value List
Row Source: Comments;[tblMainData.Comments] Is Not Null;No Comments;[tblMainData.Comments] Is Null
Column Count: 2
Bound Column: 1
The VBA Code is:
==================
If Me(Filter7) <> "" Then
strWhere = strWhere & " " & Me.Filter7.Column(1) & " " & " And "
Debug.Print strWhere
End If
If Me(Filter8) <> "" Then
strWhere = strWhere & Me.Filter8.Column(1) & " And "
Debug.Print strWhere
End If
==================
I'm getting an error on building the strWhere variable. Can I use filter criteria statements in a combobox column?
Thanks,
PC
View 1 Replies
View Related
Mar 2, 2005
Hi there
This im sure is a easy question but I am just trying to learn access so I am having a few problems.
I have made a form that contains 2 comboboxs and a listbox.
How can I use the 2 combobox to filter a table and to place the information into the listbox.
Is it possible to use either 1 of the combo boxes or both to get the filtered result.
Any suggestions would be great!!
TIA!
View 2 Replies
View Related
Feb 19, 2006
Hi everyone
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
Thanks for your patience.
Juan
View 7 Replies
View Related
Aug 17, 2006
I have three comboboxes on a form – Fabric, Degree, Color.
The row source type for each is Table/Query. The row source is a Select Distinct query. I’m using a query so the list will populate itself. The choices the user makes in the comboboxes are used to filter called forms.
Would there be a way to include the word “ALL” in a drop down list in addition to the data generated by my query. If the user chooses “ALL”, the called page will not be filtered on that particular choice. I just created a record with “ALL” in those particular fields. That seems to work, but I think there might be another way.
Thanks
View 1 Replies
View Related
Aug 18, 2006
I am running into a problem with just one form in my database. I was getting the error 'Can't build a link between unbound forms'. After searching here and elsewhere the solution is/might be to type the linked fields into the child and master proprty fields. However when I do that I still can't get this form to work. Would anybody please take a look at the file attached - I trimmed it down to two tables with a few records and a form/subform.
I want to make a selection in the combo box and have the filtered results displayed on the form below - simple and I have done the same thing in the database elsewhere but I just can't see why it won't work here.
Thanks
Steve
View 3 Replies
View Related
Jan 14, 2015
I am using Access 2010, Windows 7
I have an unbound form with a combo box and a bound subform on it.
I am using the combo box to get a value to filter (and display) records in the subform.
My code is:
Private Sub cboSessions_AfterUpdate()
On Error GoTo cboSessions_AfterUpdate_Err
Me.SSubform.Visible = True 'subform was initially invisible
Me.SSubform.Form.FilterOn = True
Me.SSubform.Form.Filter = "[LinkID]=" & Me.cboSessions
Me.SSubform.Form.Requery
cboSessions_AfterUpdate_Exit:
Exit Sub
cboSessions_AfterUpdate_Err:
MsgBox Err.Description, vbCritical, "PROGRAM ERROR"
Resume cboSessions_AfterUpdate_Exit
End Sub
The subform is initially invisible to stop it showing all records.
This works fine AFTER the first selection(!) - the first Update of the Combo Box just doesn't filter the subform??
I have tried putting code in the main form's On Load Event - allocating a value to the Combo Box and running cboSessions_AfterUpdate but that does not work either.
View 1 Replies
View Related
Jan 14, 2014
I do review SQL sometimes to check what's going on. Access has been a steep learning curve, but my understanding of queries and relationships improved a lot recently.Some of the finer points of forms are just giving me frustration. My form related knowledge probably lags behind my data/table/relationship/query related knowledge. Its probably simple, but as you know these things are not too intuitive at first.
I have a query which (built on another query and some tables in turn) delivers exactly the data I want. I want that data to come up in my form (or datasheet subform actually) BUT, I want to be able to filter for PERSON.ID. I want the user to be able to use a combo-box to do that filtering.
OK then, main form, with unbound combo-box at the top. I assume it should be unbound because I don't want any data edited based on selections which are supposed to cause temporary filtering only (correct me if I'm wrong). For the Row Source I've used a qryWholeName. This query creates a WholeName for each person, and includes their PERSON.ID as well.
[URL]....I cannot see the example DB they have attached in the thread. Finally, my database is likely to be accessed by several people at once. Different users will want to be selecting and filtering for different people simultaneously.
View 3 Replies
View Related
May 30, 2012
I want to create a combo box filter - easy enough usually
However - the field I want to use is a foreign key field which, while it displays a meaninful description - it stores the id rather than the text
I want to create a combobox filter, but when I do all I see is the id's - which are meaningless to the user.
Is there a way to get these foreign key filters to display a meaningful text description??
View 5 Replies
View Related
Jan 9, 2006
I have a main form with an unbound combo box. I also have a subform showing records in datasheet view.on the after_update event of the combobox I want to filter the subform. Here is my code (note frmqrysubmittals is the subform and the recordsource for the subform is initially the RS1 portion of code):Dim RS1 As StringDim RS2 As StringDim RS3 As StringRS1 = "SELECT tblSubmittals.SubmittalID, tblSubmittals.Format, tblSubmittals.[Specification Section], tblContractorInfo.Contractor, tblSubmittals.[Project Number], tblSubmittals.Description FROM tblContractorInfo INNER JOIN tblSubmittals ON tblContractorInfo.ContractorID = tblSubmittals.ContractorID"RS2 = " WHERE (((tblContractorInfo.Contractor)=" & Chr$(34) & Me.cboContractor.Value & Chr$(34) & "));"rs3 = RS1 & RS2Me.frmqrySubmittals.Form.RecordSource = rs3me.frmqrySubmittals.form.requeryWhen I update the cbo box the subform doesn't display any records. When I go to design view and look at the record source it displays the RS1 portion (the original RS), but all of the terms are placed in brackets []. Note, the subform and contorl are both named frmqrySubmittals.I've been at this for days. What am I doing wrong?Thank you everyone for your help. This group has been a great resource over the past few months as I stuble through learning Access.
View 6 Replies
View Related
Aug 5, 2015
I am currently having trouble filtering my subform by a different column than the bound column set in properties.
the comobobox shows the ID for the last email sent, with the combobox drop down showing the name and date of email when dropped down. I have tried 2 things and neither work...
1) Calling the filter on the combo-box column itself:
Code:
Dim myDate as string
myDate = [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]
DateFilt = " AND" & myDate & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
I have used similar code on another form, but I can't get the myDate variable to get to value of the 6th column in the dropdown (Date Of Email). an Easier way to look at this would be:
Code:
DateFilt = " AND [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]" & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
2) Inside the subform, I have set up a seperate field that reads the result of the dropdown box column(5) and shows it. For example the Email with ID 22 has a date of 4/8/15, so any record with last sent email being 22 has a record that says 4/8/15. I am trying to use this value to filter between, but am unable of passing the value to my filter.
The textbox is called "Email Date" and the Control source is "=[Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column](5)"
It shows the correct data, but does not allow me to filter by this field, when the apply filter button is pressed it asks me for the parameter value of the LastEmail Field.
Code:
DateFilt = " AND" & " [EmailDate] BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
I use a number of set variables for my filters, so my filter ends up looking like
Code:
.Filter = IDFilt + EmailFilt+ DateFilt
where all but IDFilt begin with AND. I use this on a number of other forms so I am sure this is not the issue!
View 3 Replies
View Related
Aug 21, 2013
I have a report, rptAllCSCS which is based on a query qryCSCS2...
One of the fields in qryCSCS2 is Status and each record is either "Current" or "Not Current"...
My report is being viewed via a navigation form, so one of the tabs says CSCS and when clicked the user can see the report...
I have added a button in the report header which when clicked opens the report in print preview so the user can print the report. (Done with a macro in the On Click of the button.)
I would like to add a combo box cmbStatus which has the values "Current" and "All" in the report header. (Current will show only when the Status field = Current and All shows all records so Current and Not Current together). This will act as a filter for the user to see the corresponding records and they can then press the print button or just view on screen.
I haven't worked with filters before except when you specify the criteria in the query and point it to a control on a form which then opens the report... As this report is already open I'm having trouble, as well as specifying the "path" when something is in a navigation form being a bit tricky...
View 6 Replies
View Related
Aug 18, 2015
I made a subform based of a query that I put in a main form. The subform contains a date field, where a full date is inserted (example 24.12.2015.). What I am doing is making a form where the subform would be filtered by a combobox by months. This may be wrong (correct me if it is), but for values in the combobox I placed number from 1 to 12 (as months). This combobox would then only show records from that month. I used a Change function in VBA on the combobox but I keep getting an error.
This is the VBA on the combobox where I change the record source of the subform. I probably made the WHERE part wrong but dont know how to fix it.
Combobox name is cboMonth.
Field where the full date is, is DateOut.
Subform is called subCustomer.
Code:
Private Sub cboMonth_Change()
Dim SQL As String
SQL = "SELECT qryCustomers.SID, qryCustomers.KID, qryCustomers.MMT, " _
& "qryCustomers.Owner, qryCustomers.User, qryCustomers.Policy, " _
& "qryCustomers.DateOut " _
& "FROM qryCustomers " _
& "WHERE Month([DateOut]) LIKE '*" & Me.cboMonth.Text & "*'"
Me.subCustomer.Form.RecordSource = SQL
Me.subCustomer.Form.Requery
End Sub
View 4 Replies
View Related
Mar 21, 2013
I have a table which has a "Start" and "End" fields, and i made a query based on this fields which continues to look up the next points until the last end points. I wanted to filter the last end points on the query table that was related on the combobox value.
View 3 Replies
View Related
Mar 3, 2014
I have almost completed creating a continuous form which I want users to be able to filter though the records based on to/from date, badgenum and a response string.
I then want to have a button, btnSelect, that will select the remaining records and open a report based off only the remaining records.
1.) Everything on the continuous form works except when trying to filter a combo box, cboBadge, field name "BadgeNum". BadgeNum is data type "short text". I have modified the code below from the following Allen Browne link. It keeps throwing a debug on me.Filter = strWhere at the bottom of the btnFilter sub.
2.) As far as the btnSelect and printing remaining records, I guess I'm not sure where to start.. I currently have another print button that will print individual records only which works fine.
[URL] ....
Code:
Private Sub btnFilter_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a JET query string.
[Code] ....
View 4 Replies
View Related
Sep 16, 2014
I have a subform in datasheet view for tracking calls from clients. The Client Name is a combination of the first and last name. I want to create a text box that allows me to start typing in the client name and it will filter for only those Clients that match. Currently, I have the following code (below) for the textbox, however when I start typing in a client name all the clients disappear from the table. The client name is in a combo box so I don't know if that is making a difference.
Private Sub Text3_Change()
Dim strFilter As String
Me.Refresh
[Code].....
View 3 Replies
View Related
Jun 30, 2015
I have a database that is used to generate quotations. I have a form that I want to use to Re-Quote something. By Re-Quote, I mean I want the employee to be able to go in and search for the Part desired for Re-Quoting and create a new quotation with all of the information from that part and have the ability to change any of the information as needed. The Form is unbound, and has tab control with three tabs. I would like to search by three different categories "QuotationNumber", "Customer", Or "PartNumber". How do I have the combobox filter the list box below based on what is being typed in the combobox?
View 6 Replies
View Related
Aug 9, 2006
I would like to put a query into my form so that I can look up a part number, order number, or both, and then open the form for editing. I set up the queries but can't get them into the form. How would I go about doing this? Thanks for helping out
View 2 Replies
View Related
Aug 9, 2006
I would like to put a query into a form so that I can look up a part number, order number, or both, and then open the form for editing. I set up the queries but can't get them into the form. How would I go about doing this? Thanks for helping.
View 3 Replies
View Related
