Modules & VBA :: Multi-select List Box Items To Pass Into Text Boxes
Oct 16, 2014
I have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.
The columns are PayPeriod, StartDate, EndDate, FiscalYear
What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.
I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.
Code: Dim ItemIndex As Variant For Each ItemIndex In Me.lstPayPeriods.ItemsSelected If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then Date1.SetFocus Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex) End If Next
In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.
Code: Dim CurrentRow As Integer Dim FirstDate As Date For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1 If Me.lstPayPeriods.Selected(CurrentRow) Then Date2.SetFocus
[Code] ....
I tried to do something similar with this code. Again, it only uses the last item I have selected.
I have created a search form which I would like to use to run a query (so the data is in a spreadsheet form and I can export it).
In this search form I have a multi select list box (simple) that list the states in the US. I need to be able to pass 1 or more states as a search criteria at the same time. Also I need it to pull ALL states if there is nothing selected. The search form has a bunch of fields on it that won't all be used.
I know once you select more than one thing from a list you can't reference it directly, is there a way to accomplish what I want to do?
Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler 'Purpose: Open the report filtered to the items selected in the list box. Dim varItem As Variant 'Selected items
I'm new to access. Hope someone can offer some help!
I have created a simple database to better organise some day-to-day office data management. For instance, I have a form (just) to record event attended by staff. The data would then be output as a brief report on overall office activities. Since it's event-oriented, staff attendance is just one complementary information. I went through most of the post re multi-select. I understand that it may not be a good idea to return values from multi-select list boxes into a text field, as it would be problematic for using data-analysis in the future.
In my case, I just need to have a text box, neatly (and easy for user, too) shows who attended. As suggested in one of the posting, I follow the instruction of http://support.microsoft.com/default.aspx?scid=kb;en-us;210203. But it doesn't seem working. I attach my databsae (sorry, it's very simple, as I want to test whether the multi-select work).
Your help would be greatly appr:confused: :confused: :confused: ciate!
Also, what about if I group option button, will it allow multiple return by the way?
I have a simple question that I know will have a very complex answer
You have two sets of tables with identical structures. The first set contains data that is a model for the second set. The users run queries that append data to the second set of tables
Here is an example of the structure TblSourceOne SourceOneID SourceOneData
The user adds a record to the main table (TblTargetOne) then uses an append query to add all of the applicable records from TblSourceTwo to TbleTargetTwo
So now the user has a data model in TblSourceTwo which has been “copied” to TblTargetTwo
Next for each record in TblSourceTwo that has been copied to TblTargetTwo that has daughter records in TblSourceDetails another query is executed to copy all of the pertinate records to the TblTargetDetails
Now the user has completely copied all of the necessary record for both table.
Finely the question
How can I use a multiselect list box to achieve this in one operation? I don’t care if it includes running multiple queries but it needs to appear as one single operation to the user.
I am making a form at work for a coworker with cascading combo/list boxes where she can select a Customer, then Platform Description, then Period, then Year. I used VBA code for this and it's working perfectly - for single selection. I want to update this so she can select multiple Platform Descriptions and/or Periods and it will give all the applicable info.
I can use or a way to set up a query, etc. so that multi-select will work for me? Unfortunately, just changing the formatting to multiselect makes the listbox just blank white, so I'm assuming it's going to be more complicated.
(Here's the code I currently have for the cascading combo/list boxes, if you need it. SD0039DA_T is the table name where my listboxes are pulling info from.)
Code: Option Compare Database
Private Sub Form_Load() On Error Resume Next CustomerCB.SetFocus PlatformDescriptionL.Enabled = False PeriodL.Enabled = False YearCB.Enabled = False End Sub
I am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name tblFunction - PK - FID (autonumber) has departments tblTL - PK TLID (autonumber) has list of team leaders tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
I have 7 multi-select list boxes, which I want the user to be able to select multiple items and have it filter a subform by what is selected. The subform will not be visible.
Here is the code I tried for filtering my subform (which is throwing an error when I call it).
Private Function MasterSearch() On Error GoTo Error_MasterSearch
Dim StrgSQL As String Dim WhereClause As String StrgSQL = "SELECT * FROM MasterTbl"
[Code] ...
The above code was to test just 1 of the 7 listboxes. When I call it on click for the command button, it throws an error about the "Call MasterSearch".
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.
I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.
I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.
Public Function Public Function fMultiSelect(ctlRef As ListBox) As Variant Dim Criteria As String Dim i As Variant
' Build criteria string from selected items in list box. Criteria = "" For Each i In ctlRef.ItemsSelected If Criteria <> "" Then Criteria = Criteria & "," End If Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000") Next i
fMultiSelect = Criteria gMultiSelect = Criteria End Function
I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.
Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.
Am I missing something here?
The long explanation:
I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)
Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.
Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.
I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.
Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.
I have the code below and am trying to have a form allow Text Box to become visible based on selected items from a List box. Why I am getting the error listed?
Compile Error: Invalid Qualifier
Code: Private Sub specific_opt_Click() Dim users As Control Dim ctrler As String Dim xx As Long If Me.specific_opt = True Then GoTo 169
Hi, is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help. Thanks!
This is a somewhat complicated question to explain, so please bare with me.
I have a form with a ‘list box.’ The box is populated using a Table/Query Row Source Type method. The table I am grabbing the list from is “Department,” where it contains a list of my company’s departments(ie. Accounting, HR, Payroll, etc.).
Now, the only thing this table does not have is the word “ALL.” I need this word in the “List Box” because I want users to have the option to select ALL.
Is there anything in VBScript code wise or in Row Source that I can do to include this word?
I know I can manually go into the Department table to simply add the word, but this is not the point, because there is more to that(I have different list boxes that are grabbing lists from different tables, AND I have to refresh these tables twice a week). Please help.
Hi, This is my first posting on this forum, and I would greatly appreciate any help with this issue...
I have a form that is used for entering information into tables. I would like to be able to select multiple options from a list on a form and have it saved into a table. Any ideas? Is this even possible :confused:
Here are some paths I followed: The table has a List Box field, type Text, that gets values for the list items from another table. On the form, I have changed the Multi-Select property of the List Box to "Extended" so that the user can select multiple items from the list. But when I select items from the List Box, the table is not getting updated with the selected items.
Another way I tried doing this: The table has a List Box field that has no values (I'm not looking up values from another table). On the form, I set the Row Source of the List Box equal to the query that gets the list values from a table. I then set the Multi-Select property to "Extended". When I select multiple items from the List Box, the table doesn't get updated.
FYI: The control source for the list box on the form is pointing to the field in the table.
I have two questions regarding a multi-select list box.
First, is there any way to have the list box include multi-line items...or which will scroll beyond the right border of the box?
Second, I have a multi-select list box which is populated with an ID # column and a Description column. The user can select as many items from the list box as needed and, as the list box item is clicked, the ID # is added to a memo field on the form, with each ID # delineated with a coma. Is it possible to separate the memo field back out so that each item is identified individually (for report purposes)?
Thanx so much for your help with this...you guys/gals are simply the best!
I would like to be able to select multiple items from a dropdown list like we often see on web forms. The kind where you hold down the control key to select up to 5 items. Is this possible on an access form? If so, how would do we implement, and how is such data be stored?
I have a dialog box where the users can filter a report by various combo boxes that works well. I'm trying to add a multiselect lis box option for one of the combo boxes and have added code which I've also used before. Together the code is looping through and opening the report without a problem, except it's not filtering by the options selected in the list box. Here's what I've got:
If Left(Me![cboSupplier].Column(0), 1) = "*" Or Right(Me![cboSupplier].Column(0), 1) = "*" Then where = where & " AND [qssupp] Like " + Me![QSSupp].Column(0) Else where = where & " AND [qssupp]=" + Me![cboSupplier].Column(0) End If
If Left(Me![cboPartNo].Column(0), 1) = "*" Or Right(Me![cboPartNo].Column(0), 1) = "*" Then where = where & " AND [partID] Like " + Me![PartID].Column(0) Else where = where & " AND [PartID]=" + Me![cboPartNo].Column(0) End If
For Each varItem In cboStatus.ItemsSelected If strCondition = "" Then strCondition = "[odstatusid] = " & cboStatus.Column(0, varItem) Else strCondition = strCondition & " OR [odstatusid] = " & cboStatus.Column(0, varItem) End If Next varItem
Set QD = db.CreateQueryDef("Dynamic_Query", _ "Select * from qryWeeklyOrderStatusRpt " & (" where " + Mid(where, 6) & ";")) If (DCount("*", "Dynamic_Query") = 0) Then MsgBox "There are no records to print" Exit Sub End If
I am using a Multi Select List Box to display a list of names from a table, in order to select e-mail recipients. The names are in two fields: [Main]![First Name] and [Main]![Surname].
I have done this by using the following code for the rowsource:
SELECT DISTINCTROW Main![e-mail], Main![First Name], Main!Surname FROM Main;
This gives a rather undesired effect, in that shorter first names have a large gap before the surname. In addition, there is a dull separating line between the two columns, thus:
Jane | Smith Bartholomew | Simpson
My questions:
Is it possible to arrange the names so they appear in one column, with a space between them? (i.e. they would appear as written) If not, can I get rid of the separating line?
I use access to gather information on testing that is conducted at my company. In the database I designed I set up a multiselect list box for when the people who conducted the test can check off the test equipment that they used. I did this because at anytime any number of different items can be used. Though I don't think we have ever used more then 10 test items. The reason for tracking the items that were used is if they, when out for yearly calibration, come back out of spec we can look at what tests used that equipment. As I further develop my system, I am starting to ask for more information from the requesters. I would like them to be able to check off what individual items are in the item being tested. Creating that list isn't the hard part for me.
My question comes in here. I know that using a multiselect list box is frowned upon as they can be sloppy to deal with when looking to extract data. Being as I am looking to expand my database, I was looking for what to do. I would still like to give the option of a multiselect list box, but should I create more fields and have the items used put into these new fields? Should I do the same for equipment that is used for the testing. If not and the list box is ok, how best to go through all the records to gather what ones used the specified equipment?
I have a form which contains a multi select list box of team leaders. Unbound getting source from tl table. Based on one or more selection from user I want to run query that brings up those team leaders. When I have multi select to none in list box and select one to the query works fine. When I change it to simple or expanded it shows nothing. I have query criteria to get data from form list box.
I am running MS Access 2010 on Windows XP and my access skills are limited.
So I have a db with tables, forms and reports and would like to give my users the option of opening a filtered report (from a form).
The form (frmSelectStatus) and report (rptStatus) have been created and both open correctly by normal selection from the 'All Access Objects' side bar.
frmSelectStatus has a multi-select list box (lstStatus) and a command button (cmdStatus). When I click cmdStatus i get the correct report opening but it is all status records and not filtered by the selection made in lstStatus - and i know this is a result of not referencing 'lstStatus' in the code. i also have a query (qryStatus) which does not contain anything.
My 'on-click' code for cmdStatus is
Private Sub cmdSelect_Click() DoCmd.OpenQuery "qryStatus", acViewNormal, acEdit DoCmd.OpenReport "rptStatus", acViewReport DoCmd.Close acQuery, "qryStatus" End Sub
I know I need to reference 'lstStatus' but am not sure where or how to do that in the code.
I am trying to make a simple database where the data entered in a form will update to a table. My issue is, one of the fields is manufacturing location where I would like the user to be able to enter multiple locations and then have those locations update the table where the record is stored. I've been able to set up a list box with multi-select but am stuck at getting the table to update with the choices made from the list box selection.
I have two forms - Notes and JobLookup Notes contains a number of text boxes for entering data, one of them being for Job No. Beside it is a link to the form JobLookup. This form contains a simple listbox that lists all the job no. Is it possible to pass the selected job no from this list to the text box on the Notes form?
