Hi everyone,
I am using the following code adapted from Allen Browne: - 'Purpose: This module illustrates how to create a search form, _
-
where the user can enter as many or few criteria as they wish, _
-
and results are shown one per line.
-
'Note: Only records matching ALL of the criteria are returned.
-
'Author: Allen Browne (allen@allenbrowne.com), June 2006.
-
Option Compare Database
-
Option Explicit
-
-
Private Sub cmdFilter_Click()
-
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
-
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
-
we remove the trailing " AND " at the end.
-
' 2. The date range works like this: _
-
Both dates = only dates between (both inclusive. _
-
Start date only = all dates from this one onwards; _
-
End date only = all dates up to (and including this one).
-
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.
-
-
'***********************************************************************
-
'Look at each search box, and build up the criteria string from the non-blank ones.
-
'***********************************************************************
-
'Text field example. Use quotes around the value in the string.
-
' If Not IsNull(Me.txtDepartment) Then
-
' strWhere = strWhere & "([Department] = """ & Me.txtDepartment & """) AND "
-
' End If
-
-
'Another text field example. Use Like to find anywhere in the field.
-
If Not IsNull(Me.txtRaisedBy) Then
-
strWhere = strWhere & "([Raised By] Like ""*" & Me.txtRaisedBy & "*"") AND "
-
End If
-
-
'Number field example. Do not add the extra quotes.
-
If Not IsNull(Me.txtNCCID) Then
-
strWhere = strWhere & "([NCC ID Number] = " & Me.txtNCCID & ") AND "
-
End If
-
-
'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
-
' If Me.txtDepartmemt = -1 Then
-
' strWhere = strWhere & "([Department] = True) AND "
-
' ElseIf Me.txtDepartment = 0 Then
-
' strWhere = strWhere & "([Department] = False) AND "
-
' End If
-
-
'Date field example. Use the format string to add the # delimiters and get the right international format.
-
If Not IsNull(Me.txtDate) Then
-
strWhere = strWhere & "([Date] >= " & Format(Me.txtDate, conJetDate) & ") AND "
-
End If
-
-
'Another date field example. Use "less than the next day" since this field has times as well as dates.
-
'If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
-
' strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
-
' End If
-
-
'***********************************************************************
-
'Chop off the trailing " AND ", and use the string as the form's Filter.
-
'***********************************************************************
-
'See if the string has more than 5 characters (a trailng " AND ") to remove.
-
lngLen = Len(strWhere) - 5
-
If lngLen <= 0 Then 'Nah: there was nothing in the string.
-
MsgBox "No criteria", vbInformation, "Nothing to do."
-
Else 'Yep: there is something there, so remove the " AND " at the end.
-
strWhere = Left$(strWhere, lngLen)
-
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
-
'Debug.Print strWhere
-
-
'Finally, apply the string as the form's Filter.
-
Me.Filter = strWhere
-
Me.FilterOn = True
-
End If
-
End Sub
-
-
Private Sub cmdReset_Click()
-
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
-
Dim ctl As Control
-
-
'Clear all the controls in the Form Header section.
-
For Each ctl In Me.Section(acHeader).Controls
-
Select Case ctl.ControlType
-
Case acTextBox, acComboBox
-
ctl.Value = Null
-
Case acCheckBox
-
ctl.Value = False
-
End Select
-
Next
-
-
'Remove the form's filter.
-
Me.FilterOn = False
-
End Sub
-
-
Private Sub Form_BeforeInsert(Cancel As Integer)
-
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
-
'We prevent new records by cancelling the form's BeforeInsert event instead.
-
'The problems are explained at http://allenbrowne.com/bug-06.html
-
Cancel = True
-
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
-
End Sub
-
-
Private Sub Form_Open(Cancel As Integer)
-
'Remove the single quote from these lines if you want to initially show no records.
-
Me.Filter = "(False)"
-
Me.FilterOn = True
-
End Sub
What I am trying to get my head around is how would I code within the filter script to be able to filter based on multiple/single or no selections within a list box I have created.
The list box contains "Department s", so their names. If I select Blading and Projects, I want the filter to only display records for those two departments.
As you can see in the code I have commented out a couple of attempts with no luck and I dont want to be going around in circles for too long without any help.
I have named the list box lstDepartment.
Thanks for any help,
Chris
8 4519 Megalog 378
Recognized Expert Contributor @munkee
What's the name of your list box control? Is it bound to a numeric value, or the department text value (are you using a single column, or multiple columns)? What we'll need to do is loop through all the selections and build a big OR string out of them.
Megalog 378
Recognized Expert Contributor
This should work in the script by Allen Browne, place it anywhere in the function where it is building the strWhere string. Make sure the lisbox is set to Extended type (for muliple selections). Replace "lstDeptFil ter" & "[Department]" with your list control name and your Department field name.
First column is bound to a text value - 'Listbox example.
-
If Me.lstDeptFilter.ItemsSelected.Count > 0 Then
-
Dim itm As Variant
-
Dim strList As String
-
For Each itm In Me.lstDeptFilter.ItemsSelected
-
If strList = "" Then
-
strList = "([Department] = '" & Me.lstDeptFilter.Column(0, itm) & "')"
-
Else
-
strList = strList & " OR " & "([Department] = '" & Me.lstDeptFilter.Column(0, itm) & "')"
-
End If
-
Next
-
strWhere = strWhere & "(" & strList & ") AND "
-
End If
First Column is bound to a numeric value: - 'Listbox example.
-
If Me.lstDeptFilter.ItemsSelected.Count > 0 Then
-
Dim itm As Variant
-
Dim strList As String
-
For Each itm In Me.lstDeptFilter.ItemsSelected
-
If strList = "" Then
-
strList = "([Department] = " & Me.lstDeptFilter.Column(0, itm) & ")"
-
Else
-
strList = strList & " OR " & "([Department] = " & Me.lstDeptFilter.Column(0, itm) & ")"
-
End If
-
Next
-
strWhere = strWhere & "(" & strList & ") AND "
-
End If
Thank you for the code Megalog I will try and implement it at work on Monday.
Whilst you are viewing, I have also been trying to clear selections from the list whenever I press the reset button which is situated in the code at this point: Private Sub cmdReset_Click( ).
What is the convention you use to clear a list box? is it acList or ListBox for my case?
Megalog 378
Recognized Expert Contributor
Updated Reset Code: - Private Sub cmdReset_Click()
-
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
-
Dim ctl As Control
-
-
'Clear all the controls in the Form Header section.
-
For Each ctl In Me.Section(acHeader).Controls
-
Select Case ctl.ControlType
-
Case acTextBox, acComboBox
-
ctl.Value = Null
-
Case acCheckBox
-
ctl.Value = False
-
Case acListBox
-
call ClearList(ctl.Name)
-
End Select
-
Next
-
-
'Remove the form's filter.
-
Me.FilterOn = False
-
End Sub
Place this function anywhere, it will clear the listbox when called from the Reset code. - Public Function ClearList(lst As ListBox)
-
Dim varItem As Variant
-
-
If lst.MultiSelect = 0 Then
-
lst = Null
-
Else
-
For Each varItem In lst.ItemsSelected
-
lst.Selected(varItem) = False
-
Next
-
End If
-
-
End Function
Appreciate the hard work I will put some feedback up on Monday, Thanks.
Just tried the code and it works like a charm thanks!
Megalog 378
Recognized Expert Contributor
Glad it all worked for you!
Welcome to Bytes!
hi Dears,
I have included Megalog's ocde into the code which I have also taken from Allen Brown, but it brings no records. My list box name is "lst_status " and it should bring records with 2 different status. what am I doing wrong?
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Dim strReport As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
strReport = "Incident_Summa ry_Report_Betwe en_Dates" 'Put your report name in these quotes.
If Not IsNull(Me.txtSt artDate) Then
strWhere = strWhere & "([ReportDate] >= " & Format(Me.txtSt artDate, conJetDate) & ") AND "
End If
If Not IsNull(Me.txtEn dDate) Then
strWhere = strWhere & "([ReportDate] <= " & Format(Me.txtEn dDate, conJetDate) & ") AND "
End If
If Not IsNull(Me.txtsa pno) Then
strWhere = strWhere & "([SAPNo] Like ""*" & Me.txtsapno & "*"") AND "
End If
If Not IsNull(Me.obser vation_cbo) Then
strWhere = strWhere & "([Observation] = """ & Me.observation_ cbo & """) AND "
End If
'Listbox example.
If Me.lst_status.I temsSelected.Co unt > 0 Then
Dim itm As Variant
Dim strList As String
For Each itm In Me.lst_status.I temsSelected
If strList = "" Then
strList = "([Status] = '" & Me.lst_status.C olumn(0, itm) & "')"
Else
strList = strList & " OR " & "([Status] = '" & Me.lst_status.C olumn(0, itm) & "')"
End If
Next
strWhere = strWhere & "(" & strList & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
If CurrentProject. AllReports(strR eport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
DoCmd.OpenRepor t strReport, acViewPreview, , WhereCondition: =strWhere
DoCmd.Close acForm, "msearch_fr m", acSavePrompt
End If
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ringwraith |
last post by:
Hi!
Have anyone ever tried to modify the Gene Cash's Tkinter tree widget
(available from http://home.cfl.rr.com/genecash/) so it supports
multiple items selection ( selection of multiple files like in Windows
Explorer - CTRL and SHIFT keys for file selection)?
Or maybe someone knows the tree component available to download that
supports multiple items selections? Then I will be able to take a look at
the code and see how the multiple...
|
by: starace |
last post by:
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list boxes
do not necessarily need to have a selection made to be used in the
dynamic query.
In essence the form can have selections made in all or none of its list
boxes to form the dynamic query
I am looking to get some feedback in reference to...
|
by: Kevin Murphy |
last post by:
I receive the error message "Try to compose a less restrictive search
query or check spelling" regardless of search term when attempting to
search the list "PgSQL - General" via the mailing list archive search
form at:
http://archives.postgresql.org/pgsql-general/
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
|
by: lightning |
last post by:
Hi all,
I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary...
I am trying to build a search form that allows combinations of search terms. For example, let's say I have three controls on my form; year, keyword, location. Entering a valid value for all three gives expected results from the dataset. However, I'd like to interpret a blank field in the search form as ignoring that constraint....
|
by: Prashant |
last post by:
Hi All,
we are using <input id="testFile" runat="server" type="file" />
control to select file from local machine. A problem with this control
is at a time we can select only single file from 'Choose file'
window.
Here we need multiple file selection option from 'Choose file' window
in my web application.
| |
by: jcf378 |
last post by:
Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only allows me to
filter based on variables in a single table. I would like to have a
search form where I can select multiple variables (from various linked
tables) to filter by, and return results based on this multi-table
filter.
Allen Browne...
|
by: woodey2002 |
last post by:
This problem is driving me crazy.
Hello there,
i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user can enter their search criteria eg. surname, reg num, etc. in the text boxes. The multi select list box allows the user to select multiple counties which they have the option of including in the search. The user should be able to select or omit the...
|
by: woodey2002 |
last post by:
Hi Guys and thanks for your time.
I have a search form for my database that allows users to select multiple criteria from multi select list boxes.
I successfully integrated a multi select listbox for users to select and search for counties.
On the same page however I would like to integrate a similar multiselect box for nationality. I would like the user to be able to search for nationality with county or individually.
After...
|
by: woodey2002 |
last post by:
Hi Everyone. Thanks for your time.
I am trying to create a search form that will allow users to select criteria from multiple multi select boxes. So far i have managed to achieve a search option for 2 list boxes:- county and nationality, while trying to add a third multi select list box for qualifications search is where i encounter my problem.
I've copied the working code from my working list boxes, however it cant seem to pick up the...
|
by: hollinshead |
last post by:
hi there, i have been having this issue for quite some time now and i cant seem to get my head around it. I am trying to create a database for candidates CV's and covering letters. basically the data that is stored is made up of qualifications and areas of business that candidate is involved etc.
My problem is with the query by form where i have a range of combo boxes and list boxes. The combo boxes are working fine, no problem however the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |