473,856 Members | 1,436 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to use unbound multi-select combo box to filter a query for report

100 New Member
I have a form with an unbound combobox. This combobox serves as the criteria for a Query that then feeds a Report. It is simple, efficient, and works very well. I am also using a vba codefor this purpose.

I would like for this combobox to allow for multiple selections so that when the report is produced more than one selection would show on the report, instead of running the report two different times. How is it possible in MS Access?

Any help will be appreciated.
May 17 '24 #1
6 265
1,278 Recognized Expert Top Contributor
A combobox does not allow for multiple selections. If you use a list box instead you will see in the Other tab of Properties an option for Multi Select.

You can loop through the list of items to test the .Selected property to identify which items in the list have been selected. Something like this:
Expand|Select|Wrap|Line Numbers
  1. dim i as integer
  2. for i = 0 to me.listname.listcount-1
  3. if listname.Selected(i) then
  4. do stuff
  5. end if
  6. next 
May 17 '24 #2
32,584 Recognized Expert Moderator MVP

I agree with Jim. There is no capability in ComboBoxes to support multiple selections, but there is in a ListBox where .MultiSelect is True. Repeating Jim again, the Collection within a ListBox is called .Selected. I'm not sure (Jim can correct me if I'm off here.) the logic of getting i from .ListCount of the ListBox then using it on ListBox.Selecte d works very reliably, but heres some alternative code that should work for you :
Expand|Select|Wrap|Line Numbers
  1. Dim varItem As Variant
  3. For Each varItem In Me.YourListBox.Selected
  4.     Debug.Print varItem
  5. Next varItem
May 17 '24 #3
1,278 Recognized Expert Top Contributor
You may well like Neopa's solution better than my own. I've used both methods but I often forget about that .Selected collection.

I use listboxes extensively, nearly every form I create has at least one listbox on it. They are so very useful. But I don't often use multi-select lists. I use two lists instead, so the user can clearly see what has been selected, no matter how long the origin list is.

I'm glad to be reminded about .Selected.
May 18 '24 #4
32,584 Recognized Expert Moderator MVP
Hi Jim.

Now you have me wondering. Was your line #3 supposed to be different? Perhaps :
Expand|Select|Wrap|Line Numbers
  1. If Me.ListName(i).Selected Then ...
I say this as your code already included the Selected property there. I just can't see that it could work that way. Clearly I need to refresh my understanding of exactly what it contains.

The documentation indicates it actually returns Longs. I need to look into this further to understand properly what information it actually provides.
May 19 '24 #5
1,278 Recognized Expert Top Contributor
Now you have me looking at it more closely. I understand it to be returning a true/false value. Been using it for years ... or have I? I don't use multi-select often enough to have a complete grasp of the details ready at hand.

I poked around the Internet and found this example from Microsoft. Ade went off kilter a little bit. The collection he refers to is actually .ItemsSelected. Here's MS' example, which does not reference the ItemsSelected property:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopyItem_Click() 
  2.  CopySelected Me 
  3. End Sub 
  5. Public Sub CopySelected(ByRef frm As Form) 
  7.  Dim ctlSource As Control 
  8.  Dim ctlDest As Control 
  9.  Dim strItems As String 
  10.  Dim intCurrentRow As Integer 
  12.  Set ctlSource = frm!lstSource 
  13.  Set ctlDest = frm!lstDestination 
  15.  For intCurrentRow = 0 To ctlSource.ListCount - 1 
  16.  If ctlSource.Selected(intCurrentRow) Then 
  17.  strItems = strItems & ctlSource.Column(0, _ 
  18.  intCurrentRow) & ";" 
  19.  End If 
  20.  Next intCurrentRow 
  22.  ' Reset destination control's RowSource property. 
  23.  ctlDest.RowSource = "" 
  24.  ctlDest.RowSource = strItems 
  26.  Set ctlSource = Nothing 
  27.  Set ctlDest = Nothing 
  29. End Sub
And here is an example using the collection:
Expand|Select|Wrap|Line Numbers
  1. Sub BoundData() 
  2.  Dim frm As Form, ctl As Control 
  3.  Dim varItm As Variant 
  5.  Set frm = Forms!Contacts 
  6.  Set ctl = frm!Names 
  7.  For Each varItm In ctl.ItemsSelected 
  8.  Debug.Print ctl.ItemData(varItm) 
  9.  Next varItm 
  10. End Sub
I'm so glad to revisit this because it may help me in the near future. I should take advantage of multi-select more often.

Happy coding!
May 20 '24 #6
2 New Member
I have a reusable dialog box that I feed a list of values to for the user to make their selections.
With the dialog box, I can allow the user to select if they want to Include or Exclude the selections in the list box, if they want to also include Null values, etc. When the user clicks on a button to filter by (for instance) Job Status, the reusable list box opens up and displays their current filter. They can edit that filter, adding or removing items, then the comma delimited list is returned in code. I store the CSV list and decode it to the button so that the user can always see what their current filter is.

A simpler way is to just put a list box on screen with MultiSelect set to true. Then set the Click event for the listbox to gather up a list of everything that's checked with a routine like this:

Expand|Select|Wrap|Line Numbers
  1. Public Function ListBoxItems(ctl As Control, Optional blnForceText As Boolean) As String
  3. Dim vItem As Variant
  4. Dim sList As String
  5. Dim blnTextField As Boolean
  7.     blnTextField = IIf(IsMissing(blnForceText), False, CBool(Nz(blnForceText, 0)))
  8.     For Each vItem In ctl.ItemsSelected
  9.         If blnTextField Or Not (IsNumeric(ctl.Column(0, vItem))) Then
  10.             sList = sList & "'" & ctl.Column(0, vItem) & "', "
  11.         Else
  12.             sList = sList & ctl.Column(0, vItem) & ", "
  13.         End If
  14.     Next vItem
  16.     If Len(sList) > 0 Then sList = Left(sList, Len(sList) - 2)
  17.     ListBoxItems = sList
  18. End Function
Rebuild your underlying query with the list above in a WHERE clause.
May 29 '24 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

by: SalimShahzad | last post by:
dear gurus, I have one problem. i have listed some cities say london, tokyo, newyork,ALL. now based on the combo selection after update. it pass the value to a report query and results comes of that particular citiy. but now the problem is that, if i wanted to have all cities together from combo box. say i select all...so all cities will come together...so it can possible...
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used VBA to create SQL statements, then apply them. Now that I have been using Access/VBA for awhile, I am wondering if there is a simpler way to do this than coding these long sql statements. My report has a query as its record source. Can I just...
by: allie357 | last post by:
I am trying to add a form with a combo box to an existing parameter query report. I followed the directions from this link:http://office.microsoft.com/en-us/assistance/HA011170771033.aspx However, even though the form pops up when I click on the report, the combo box is not populating and the report still has popping up parameters, even though I thought I removed them. Any help is appreciated.
by: Gari | last post by:
Hello, I am trying to build a filter query with some AND and OR. I have three text boxes and 5 check boxes. The checkboxes are linked via code to other textboxes for the purpose of the query. The first three text boxes are:
by: Fran | last post by:
I recently tried to use code for "Use a multi-select list box to filter a report" from Allen Browne in my database. I was able to add the code and adapt it to my needs, however I am getting an error. I click Ok and the report opens. I look in the design view and the filter is there but the IN equals "" (it is blank, the values are not in there)
by: pukhton | last post by:
Hello~ Just a quick question about Access Reports. I want to have a form for user where they will have two textbox and one combo box to pick from and run the query based on that. 1 txtbox is for start date 2 txt box is for end date and combo box is for to pick the person name
by: didihynes | last post by:
Hi Guys, I'm in desparate need of help. I am producing a database for my dissertation and have got majorly stuck. I am currently creating a form in which the user will select a student from a combo box, which that selection populates the next combo for the course selection. I have managed to do that with the coding shown below, my next combo will work from the course selection to bring back the feedback topics associated with that course,...
by: Gord | last post by:
I'm trying to filter a report with a date in VB code. If I type an actual date bracketed with the pound symbol (i.e. #3-Jul-08#) I can get the filter to work. I can't seem to get it to work by using a variable. Am I trying to do the impossible? I've tried using a date variable that I know contains a valid date. I've tried bracketing it with pound symbols, single quotations etc. I've tried a string variable and used the 'CDate'...
by: sueb | last post by:
So I'm following Steward Ross's good advice (see thread http://bytes.com/topic/access/answers/912493-how-access-users-input-crosstab-query-field) about making a form that allows my users to select a FY, and then have buttons that each run one of a handful of summary reports. I made a little table that contains fiscal year strings ("FY08-09", etc.) with associated begin and end dates, and a form with a combo box to allow the user to select a...
by: axkoam | last post by:
Microsoft Access 2007. Background: I have a query that received work orders from out client system and runs them through access. Once in Access, an employee goes through the query output and organizes the records (mentally) by product_code. There can be be 1 or more records of a certain product_code and there are 129 total different product_codes. Say, a typical time the employee runs this query they might see 25 different records consisting...
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: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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 using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.