Full Version: capture the selected from the list boxes
UtterAccess Forums > Microsoft® Access > Access Forms
vgorskyus
Hi everybody!
I have a form with 2 unbound list boxes - they are dependent. I select the directory/folder from the first one and and the users assigned to the selected directory shows up on the second list. The thing is that it only highlights the records - you can see everyone and only assigned users get highlighted. Now the question - how can I capture the directory and the users highlighted and possibly output it into excel? I know how to use a DoCmd.OutputTo for forms and queries, but not sure what to do in this situation.
Thanks in advance!
Vitaly.
jzwp11
You will have to use some code to loop through each of the list boxes to find out what was selected. Here is a section of code I used for a multiselect list box named submit. This code uses the count parameter of the list boxes ItemsSelected property in a loop which goes through each item selected. For each selected item, the value of the bound field (in this case it is numeric, key field) is stored in a string variable (strIDs). Once you have the info stored in a variable, you can export it to Excel.
CODE
If Me.submit.ItemsSelected.Count > 0 Then
            For lngLoop = 0 To Me.submit.ItemsSelected.Count - 1
            If lngLoop = 0 Then
            strIDs = strIDs & Me.submit.ItemData(Me.submit.ItemsSelected(lngLoop))
            Else
            strIDs = strIDs + "," & Me.submit.ItemData(Me.submit.ItemsSelected(lngLoop))
            End If
            Next lngLoop
End If
vgorskyus
Hi, and thanks for a response. Not sure though how to export variable into excel - can you please provide more details?
Vitaly.
jzwp11
Here is some code that I created for a list box called lstCustomers that dumps the string to a cell in Excel.
[code]
If Me.lstCustomers.ItemsSelected.Count > 0 Then
For lngLoop = 0 To Me.lstCustomers.ItemsSelected.Count - 1
If lngLoop = 0 Then
strIDs = strIDs & Me.lstCustomers.ItemData(Me.lstCustomers.ItemsSelected(lngLoop))
Else
strIDs = strIDs + "," & Me.lstCustomers.ItemData(Me.lstCustomers.ItemsSelected(lngLoop))
End If
Next lngLoop
End If
Dim XL As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlchart As Excel.Chart
Dim xlrange As Excel.Range
Set XL = CreateObject("Excel.application")
Set xlbook = XL.Workbooks.Add
XL.Visible = True
xlbook.Worksheets.Add(after:=xlbook.Worksheets(1)).Name = "results"
Set xlsheet = xlbook.Worksheets("results")
xlsheet.Range("A1") = strIDs
strSaveName = "listboxcapture"
xlbook.SaveAs Filename:=strSaveName, FileFormat:=xlNormal
xlbook.Close
Set xlrange = Nothing
Set xlchart = Nothing
Set xlsheet = Nothing
Set xlbook = Nothing
XL.Visible = False
XL.Quit
Set XL = Nothing
Set cnn1 = Nothing
MsgBox "Search results are stored in an Excel file here: " & strSaveName
End Sub
vgorskyus
Thanks again for your help, but it solves the problem partially - it outputs the selected into one sell divided by coma and I was hopping that it could be done continuously as - A1, A2, A3... Also, how to specify the ItemData so I can get out columns starting from 1 and not from 0?
Thanks!
Vitaly.
jzwp11
What do you mean by continuously? Do you mean you want each value to be in its own cell in Excel? Regarding the column, do you want to capture something other than the bound column from the list box?
vgorskyus
sorry for the confusion - yes, I'd like the value to be in its own cell, and I don't really need IDs. The "uid" is a bound column but I'd like to get the "fname" and the "lname" instead. In an ideal situation I'd like to capture and export the values from the two listboxes - the form has 2 listboxes; when the Directory selected in the first list the second shows users assigned to it in the second list - the lists are dependent. So, it would be an ideal to capture and export the Directory and the Users assigned.
jzwp11
Do you want the Fname in a cell and the Lname in a cell or do you want the full name in 1 cell? Can multiple directories be selected as well as multiple users?
Edited by: jzwp11 on Mon Sep 8 11:41:45 EDT 2008.
vgorskyus
they could be separate - whichever is easier to accomplish.
Thanks!
jzwp11
Can you only select 1 directory from the directory list box at a time? In other words, is the directory list box set to multiselect?
vgorskyus
only one Directory can be selected, but multiple Users could be assigned to a single Directory.
jzwp11
To capture and export to multiple cells, an array would probably be more efficient than a string variable. Please see the attached database as an example.
vgorskyus
Thanks, this is exactly what I was looking for!
jzwp11
You're welcome. Good luck on your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.