Forms :: Concatenating Listbox Items - Column Data
Mar 18, 2013
I saw the following code on the forums...
Dim i As Integer, c As String
With Me.ListBox.Column(0)
For i = 0 To Me.ListBox.ListCount - 1
c = c & Me.ListBox.ItemData(i) & ", "
Next
End With
c = Left(c, Len(c) - 2)
This works great!! Now I am trying to concatenate the column 3 of the listbox.
Tried to use the code below (pointing to the second column) but it always returns the first column data.
'Dim i As Integer, d As String
With Me.ListBox.Column(2)
For i = 0 To Me.ListBox.ListCount - 1
d = d & Me.ListBox.ItemData(i) & ", "
Next
End With
d = Left(d, Len(d) - 2)
ItemData does not allow pointing to my 3 column of the listbox.
View Replies
ADVERTISEMENT
Jul 13, 2013
I have a form with a listbox in which users should be able to move the items up and down. The listbox has 4 columns and multiple selection is enabled.
For a NON multiselect box I have it working, and also for a multiple selection box but in that case it works only for one row at a time.
For a NON multiselect listbox:
Code:
cmdDown_Click()
Dim i As Integer
Dim t1 As String, t2 As String, t3 As String, t4 As String
i = selectie.ListIndex
t1 = Nz(selectie.Column(0, i))
t2 = Nz(selectie.Column(1, i))
[Code] ....
You can select an item and then press "Down" as many times as you want to put the items as "down" in the list as you want it to be.
Multiselect listbox
This code also works for a multipleselection listbox when one item is selected, however, after you press "down" the item is still selected (highlighted) as the code reselects is (last line) BUT the value of selectie.listindex apparently is set to "-1".
Pressing "Down" again generates an error (i = -1). I can evade the error by adding "if selectie.listindex <=0 then exit sub", but that doens't fix the problem that the only way I can manage to reset the listindex to the new "position" of the item is to click on it again and THEN press "Down".
To fix this and be able to press "down" multiple times listindex should be set to the new value.
Adding a "me.selectie.listindex = i -1" doens't work (error), it seems like this value is readonly.
I can't find a way to "simulate" a mousepressed selection and really set the listindex.
The other problem is of course that this code doens't support moving multiple items at once: listindex points to the last selected item, but only one.
So, I tried another piece of code to move multiple items, not using listindex (since that resets to -1), but I run into another problem
Code:
Private Sub cmdDown_Click()
Dim var As Variant
Dim i As Integer
Dim n As Integer
Dim t1 As String, t2 As String, t3 As String, t4 As String
n = Me.selectie.ItemsSelected.Count
[Code] ....
The problem with this is: I can select multiple items and press "Down", but the problem now is that the selection is lost as soon as the code removes the first item, and the 2nd loop skips the if selected(i) = true (nothing is selected anymore).
The me.selectie.selected(i+1) = true doens't work since that would only reselect the first item after moving it.
The code "forgets" which items were selected and moves only one item...So I guess I need to put the indexnumbers in memory while moving the items.
I have been searching a lot, but can only find VB-solutions. In VB it's a lot simpeler using f.e. the .list property of a listbox, which is not available in MSAccess
The solution I'm thinking about is:
- set an array with the numbers of selected items
- put indexnumbers of the selected items in the array (f.e. 3 and 4)
- move items based on the indexnumers in the array
- when moving an item update the indexnumber in the array (3>4, 4>5)
- after moving all items reset the selection based on the array
It occurred to me then if I'm going to use an array anyway, I might as well load all items in an array, do the "resorting" and the reload the items in the list from the array. Might be more straightforward?
Btw...It seems VB has a simple solution to moving items: listbox.list(i) = listbox.list(i+1) or something like that moves an item. Even Excel seems to have this property but not MS Access!
View 1 Replies
View Related
Jul 24, 2013
How do I remove multiple selected items from listbox.
Noticed it is a table/query listbox, not value list.
Remove selected.zip
View 1 Replies
View Related
Nov 1, 2013
I'm creating an employee audit database, and, in the audit form, the user (ie. supervisor) can select a number of items from a listbox. Each item selected corresponds to an error that the employee has made, and, as such, the employee's Audit Score has two points deducted for each item that is selected.
Incidentally, there are other, solitary elements to the form, but this particular listbox houses a collection of items that are related under a single category.
The score is displayed at the bottom of the form, and it needs to update in real-time.
The problems that I am encountering are that I am unable to count the number of items selected and then I am unable to multiply that count by 2 (the point-value of each item on the list.)
View 2 Replies
View Related
Jul 21, 2014
I'm creating a Form called Pharmacy where I can select a [Diagnosis] from a combo box in the form. This combo box source references a table called tblDiagnosis where each Diagnosis also has a multi-valued field called [Indications].
The Pharmacy form also has a multi-select listbox with all possible Indications. I would like to write a VBA code such that when a Diagnosis is selected, the Indications for that Diagnosis are automatically selected/highlighted in the listbox on the form as well.
As such, by selecting a Diagnosis, all the indications attached to that Diagnosis will automatically be selected; however, if additional indications are needed, they can still be selected afterward.
I know that there is a function Me!Listbox.Selected(i) = True, where i is the row of the entry in the listbox. However, the i in the listbox does not correspond to the ID of the Indication. I think that if there is a way to select listbox items by name, that would be much more efficient.
The reason I have a listbox, is because this listbox of Indications then references another table called tblDrugs where all drugs that are approved for the selected indications selected are filtered and displayed.
View 1 Replies
View Related
Mar 7, 2013
I'm trying to create a right-click event on a listbox that will copy selected listbox item(s) to a temp table. So far, I've got this code to acknowledge the right click:
Code:
Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single,
Y As Single)
If Button = acRightButton Then
MsgBox "You pressed the right button."
End If
End Sub
Problem is the selected item on the list box doesn't move until after the mouse down event so whatever code I would run would involve the wrong record(s).
I'm using Access 2000 and 2003. How to get the the correct record selected on mouse down, or point me to a working example of right-click functionality on a listbox.
View 4 Replies
View Related
Mar 17, 2008
i want to allign a listbox column data, associated with a table as right, center & left, is it possible? if possible please help me
thanks in advance
View 2 Replies
View Related
Oct 22, 2013
I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now
View 10 Replies
View Related
Mar 7, 2013
I was wondering if a list box can be made so the header of each columns is clickable.. so you can sort by that column either alphabetically or numerically?
View 1 Replies
View Related
Apr 30, 2013
In short, I have a form that has a combo box containing people type values. Then two text entry boxes, firstname, lastname. Then an add, update, and delete button...
When the person type is chosen from the cbo, and the names are filled out, and the 'add' button pressed a query sends the values back via sql insert, and a listbox next to these controls then lists the new person records. Great - No worries...
Then, when someone wants to update the record now shown in the listbox, on double click of the record in the listbox, vba repopulates the name text boxes from the listbox's columns values (easy), and then *magically* the person type combo box chooses amongst its values the value stored in the person type column from the listbox.
I can't get the combo box to be set to the value that I am grabbing from the listbox's column values.
Code:
cboContactInfoType.{magic happens here} = ListBoxOfficeContactInfo.Column(3)
txtContactInfoValue = ListBoxOfficeContactInfo.Column(4)
txtContactInfoValue2 = ListBoxOfficeContactInfo.Column(5)
Part of me wonders if I would have to program in a way to search among the combo boxes values and find out which one was selected from the listbox, find it's index value and then set the combo box to be that index/value record... but that seems like a hard way to do it... and I'm not exactly sure how?
View 5 Replies
View Related
Dec 24, 2014
How to do this? Or maybe change its color to white.
View 4 Replies
View Related
Mar 13, 2013
This is the function i'm using for summing a listbox column:
Function TonSum() As Variant
Dim I As Integer, J As Integer, ctl As Control
Set ctl = Me.lstDrivera2
J = ctl.ListCount - 1
[code]...
The problem i have is if one the records in the listbox doesn't have a value (is null) then Access gives me a 'Type Mismatch' error. How to treat the null value so it excludes it from the sum?
View 10 Replies
View Related
May 13, 2014
im trying to autosize list box columns depending on the size of the largest field in the column?
View 1 Replies
View Related
Jul 16, 2006
Hello everybody,
Hopefully somebody can help me on this one. I searched the whole internet and access forums, but I didn't find the exact solution for my problem.
I've got a table with students, a table attendance, where I now only save the students who are absent, but I would like to save also the students who are PRESENT (at the same time).
I've got a combobox where I filter the Class, which then updates a listbox with the students from that class. What I do now is select the students from the listbox and then press a save button and it saves the records to the table absence with STATUS: ABSENT.
I would like to save the NON selected students also in that table, but with PRESENT in the column STATUS.
I thought of making another listbox next to it, where after selecting the absent students, they wil apear and disappear in the PRESENT table so I can store all the information.
But the only problem is that I can find this solution when the listbox is populated by a list of values instead by a table or query. And the other solution is to store the temporary data into 2 different tables, but that's not working for me because it's a multi user database and everything will be messed up.
Hope that someone can help me, I will be very happy.
View 4 Replies
View Related
Jul 30, 2013
The following code works to change the column header name for a listbox in form view when the data source is a local table, but not when the data comes from a sharepoint list.
Code:
sqlstatement = "SELECT ID, PONum as [PO Number], ActDate as [Date], VendorName as [Vendor Name], Service, BuildingNumber as [Building Number], ReservationDescription as [Description], POAmount as [Amount], QuoteType as [Type of Quote], Comments" & _
" FROM ActivityLog" & _
" WHERE (Activity = 'AcceptReservation') AND (PSCName = '" & Me.PSCCombo4.Column(0) & "')" & _
" ORDER BY ActDate;"
'MsgBox sqlstatement
Me.EditPOListBox.RowSource = sqlstatement
sqlstatement = ""
Also it appears that there is no such thing as a caption property for a sharepoint list column.
View 3 Replies
View Related
Sep 15, 2005
Hi folks,
New person to the forums here. I've had a scroll through the FAQs and can't see an answer to a problem I need to solve. I wonder if there's anyone could offer some help?
My db has three tables: table_a, table_b and table_c. Each table has the following fields:
table_a
a_id (PK)
a
table_b
b_id (PK)
b
table_c
c_id (PK)
a_id (FK (many to one))
b_id (FK (many to one))
c
I would like field c in table c to be a concatenation [table_a].[a]+"-"+[table_b].[b]. I'd like this field to automatically generate and be constrained as unique. Creation of value c in table_c should be by selection of value a & then value b in a form based on table_c.
Can anyone advise me how I do this?
By the way - editing to add this - I am using MS Access 2000 & can't upgrade to a later version.
View 8 Replies
View Related
Sep 21, 2005
How to code a "Select all" button to a multi select listbox named lbSeleccionePuerto
View 2 Replies
View Related
Oct 11, 2006
Does anyone know if its possible to select all items (that is turn all items black) in a listbox? thanks
View 1 Replies
View Related
Dec 7, 2007
I have a list box bound to a table. 3 columns (ID, item, open). only item is visible. The box contains forms or reports that will open on double click. The column open contains the name of the form or report to open
Some forms and reports are a work in progress, hence, they are still listed in the list box but the open value is blank and of course won't run.
I'd like to change the colour of the list list box items that won't run.
I'm thinking maybe some kind of If.. isnull type statement...
any ideas are appreciated.
View 4 Replies
View Related
Mar 7, 2013
I'm creating user form items (text boxes/combo boxes) for a user to enter data in. In this case it would be for a part. What I would like to happen is, if there is more than 1 part, then after the first part is entered, another part entry item is added below the first one but only if the first one has a value - for a virtually unlimited amount of parts. BUT if there is only say, one part to be entered, then the next section of selection appears directly below the blank next part. Sooo, something like this.......
Invoice:
Part1: (THE PART HERE)
Part2: (THE PART HERE)
Part3: (THE PART HERE)
...
...
...
...
Partn: (THE PART HERE) - blank
(aButtonThatProcessesThis)
OR.........
Invoice:
Part1: (THE PART HERE)
Part2: (THE PART HERE) - blank
(aButtonThatProcessesThis)
Is this possible?
View 2 Replies
View Related
Oct 6, 2006
Hello.
As my title states, that is my problem! I'm building search criteria from a form and have multiple list boxes... I want to be able to determine whether or not a list box has items selected or not. I have tried as many things as i knew how to with no luck...
Also, i searched the forum but could not find what i was looking for -_-
Thanks for any and all aide!
View 2 Replies
View Related
Aug 19, 2013
I have a listbox control on my form which works in conjunction with my search field on the first form. I can add new items with FRM_ItemsAdd. I can delete records by pressing the delete button.
However now I want the ability to Edit items in the list. How can I tell access I want to edit a record?
Attached is my access db.
View 1 Replies
View Related
Sep 9, 2014
I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button):
Code:
Sub CopiTo_Click()
Dim Msg As String
Dim i As Variant
If SearchResults5.ListIndex = -1 Then
Msg = "Nothing"
[Code] ....
This works quite well if I manually select the items that I want to move. Most of the times SearchResults5 has lots of elements so I have decided to create a "Select All" button to speed up the process, here the code:
Private Sub Command271_Click()
Dim n As Integer
With Me.SearchResults5
For n = 0 To .ListCount - 1
.Selected(n) = True
Next n
End With
End Sub
When I use the "Select All" button and I try to move the items between the two listbox, the function does NOT work.
BUT if I manually select one or more items in the first listbox, then I clear the selection and finally I use the subroutine to move the items between the two listbox, then the it works well again. How to make it work properly.
View 5 Replies
View Related
Apr 15, 2015
I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:
Code:
If Me!chkAGDLLA = True Then
Forms!email.lstName = Null
Forms!email.chkComercial = False
Forms!email.chkOperacional = False
strAGDLLA = "email1, email2, email3, email4"
strMail = strAGDLLA & ", " & strISA & ", " & strMAYA & ", " & strSANGER & "," & strSANSE & ", " & strSede & ", " & strGC & ", " & strCSR
MsgBox strMail
End If
Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"
I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.
View 6 Replies
View Related
Oct 24, 2005
Hi all, greate site and i have been able to solve most problems by using the search box although this problem is doing my head in...!!
I have a db that records project numbers and their details. I am using a listbox to allow a user to multiselect Project Involvements Tasks(ie Documentation, Build etc) against a project number.
I am able to read the selections into a separtate table with two columns which is structured as:
ProjectNo - InvolvementType
123 - Testing
123 - Build
123 - Documentation
456 - Build
789 - Testing
789 - Documentation
as you can see I dont have a problem getting the Itemsselected into a table... the problem that i am having is getting them out again when the record is displayed - ie marking them as itemsselected.
I believe that the event would be onCurrent which would loop through this table pick up the project number and recorded invovements and mark them as selected in the listbox. if there is no invovement then the listbox would show no selections.
I am using this code to read the selections in
===========================
'Records project involvements against project
Public Function AddInvolvements(ctlRef As ListBox) As String
On Error GoTo Err_AddInvolvements_Click
Dim i As Variant
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Dim strDelete As String
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qInvolvement
Set rs = qd.OpenRecordset
'Delete records where project number exists against an invovelment incase of involvement changes
strDelete = "Delete Project_Involvement.ProjectNo " & _
"FROM Project_Involvement " & _
"WHERE (((Project_Involvement.ProjectNo)=[Forms]![Add_Project_Details]![ProjectNo]));"
DoCmd.SetWarnings False
DoCmd.RunSQL strDelete
DoCmd.SetWarnings True
For Each i In ctlRef.ItemsSelected
rs.AddNew
rs!InvolvementType = ctlRef.ItemData(i)
rs!ProjectNo = Me.ProjectNo.Value
rs.Update
Next i
Set rs = Nothing
Set qd = Nothing
Exit_AddInvolvements_Click:
Exit Function
Err_AddInvolvements_Click:
Select Case Err.Number
Case 3022 'ignore duplicate keys
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_AddInvolvements_Click
End Select
End Function
===================================
Any help would be much appreciated - also thanks to Pat Hartman for his excellent examples esp http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54924
Regards
Robert
View 3 Replies
View Related
Jan 10, 2006
I have a combo box that inserts data into an unbound list box and table. This works great but I am having trouble with the deletion part. I want to be able to dbl click on the item in the item list and delete it from both the list box and table. Currently, my code is deleting ALL items, not just the one item I want to get rid of. Any ideas would be most appreciated :)
This is what I have for the deletion code:
Private Sub List92_DblClick(Cancel As Integer)
DoCmd.SetWarnings False
sql2 = "delete from PROFILE_Industry where "
sql2 = sql2 & "profile_id = " & Me!Profile_ID
sql2 = sql2 & " And Industry_focus = '" & Me!List92.Value & "';"
DoCmd.RunSQL (sql2)
Me.Refresh
DoCmd.SetWarnings True
End Sub
View 3 Replies
View Related
