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.
I have tried:
Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)
but when skipping the first item in the listbox it is still passed as into the textbox.
View Replies
ADVERTISEMENT
Oct 22, 2014
I have a listbox set to Multiselect property of Simple. The listbox is populated by using a table. There are 4 columns in the listbox
Code:
1 3/23/2014 4/5/2014 2014
2 4/6/2014 4/19/2014 2014
3 4/20/2014 5/3/2014 2014
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.
View 2 Replies
View Related
Apr 24, 2015
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?
View 1 Replies
View Related
Nov 20, 2013
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
[Code] .....
View 14 Replies
View Related
Nov 27, 2005
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?
delamerbleu
View 2 Replies
View Related
Sep 24, 2014
1. I have created a parameter query in access 2010. This parameter is on only one field.
2. I have created a multi-select list box in a form so that users can select one of more items.
I want to pass the selected items as parameters to the save access query.
View 1 Replies
View Related
Apr 21, 2006
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
TblSouceTwo
SourceTwoID
SourceOneID
SourceTwoData
TblSouceDetails
SourceDetailsID
SourceTwoID
SourceDetailsData
As you can see TblSourceTwo contains a foreign key from TblSorceOne and TblSorceDetails contains one from TblSourceTwo
The main table for the second set
TblTargetOne
TargetOneID
SourceOneID
TargetOneData
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
TblTargetTwo
TargetTwoID
TargetOneID
TargetTwoData
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
TblTargetDetails
TargetDetailsID
TargetTwoID
TargetDetailsData
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.
View 2 Replies
View Related
Sep 4, 2013
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
[code]...
View 3 Replies
View Related
Apr 16, 2013
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.
View 1 Replies
View Related
Oct 6, 2014
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".
View 1 Replies
View Related
Apr 23, 2015
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?
View 2 Replies
View Related
Jan 30, 2008
All -
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
Call:
Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)
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.
Any ideas? Thanks!
View 5 Replies
View Related
Oct 13, 2014
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
[Code] ....
View 8 Replies
View Related
Aug 28, 2004
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!
View 1 Replies
View Related
Oct 4, 2006
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.
Thank you.
Joe
View 1 Replies
View Related
Jun 10, 2005
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.
Thanks,
dbnewbie
View 4 Replies
View Related
Jun 6, 2006
Good morning all...
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!
Karen
View 1 Replies
View Related
Mar 24, 2006
Hi,
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?
Thanks!
~Bruce
View 12 Replies
View Related
Sep 29, 2005
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
Can anybody see where I'm going wrong?
View 4 Replies
View Related
Jan 30, 2006
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?
Many thanks in advance if you can help!
View 4 Replies
View Related
Dec 22, 2014
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?
View 2 Replies
View Related
Mar 6, 2013
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.
View 1 Replies
View Related
Jul 22, 2015
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.
View 1 Replies
View Related
Mar 26, 2012
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.
View 2 Replies
View Related
Dec 4, 2006
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?
Hope this makes sense :)
Michael
View 3 Replies
View Related
Apr 30, 2015
I have a list with 50 items in it, I would like to select some of these items by pasting in a coma separated list, is this possible?
View 12 Replies
View Related
