If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Select Multiple value from Listbox in MS Access

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 49
Select Multiple value from Listbox in MS Access

Hi,
My Project is in MS Access.
In that I have one form in which I have some textboxes,comboboxes and listboxes.
Now when I select value from 1st combobox(CustomerID) then it wil generate list for 1st listbox(PalletNo).
Now I want to select miltiple values from that 1st listbox(PalletNo).
and based upon this selection from 1st listbox(PalletNo) and combobox(CustomerID) I want to generate list for 2nd listbox(PONo).

For example

CustomerID PalletNo PONo
1000 28300 12345
1001 28301 12346
1000 28302 12345
1000 28303 12345
1002 28304 12347
1003 28305 12348
1000 28306 12350
1000 28307 12350

So when I select 1000 from 1st Combobox(CustomerID) then it will generate list for 1st Listbox(PalletNo).
like this
CustomerID PalletNo
1000 28300
28302
28303
28306
28307


Now when I select multiple values from that Listbox(PalletNo) then it will generate list for 2nd listbox(PONo)
like
CustomerID PalletNo PONo
1000 28300 12345
28302 12345
28307 12350


I don't know how to do this.
->how to select multiple values from listbox?
->how can I generate list if I select multiple values from listbox?
Can anyone help me?
Thanks in Advance.
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
To access multiple selected values you must loop through the contents of the listbox
Code:
For i = 0 To Me.myListBox.ListCount - 1
    ...
Next i
And check to see if the value is selected
Code:
If Me.myListBox.Selected(i) Then
    ...
End If
If selected, append the value to an array or delimited string variable
Code:
myString = myString & Me.myListBox.Column(boundColumnZeroBasedIndex, i) & ","
Any good to ya?
__________________
George
Home | Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Chicago, IL
Posts: 1,312
To be able to select multiple items in your listbox, there is a setting called Multi Select in the Other tab of the properties. Change it to Simple or Extended.

Also, I would recommend using the ItemsSelected collection instead of the way that georgev has proposed. With ItemsSelected you only loop through the items that the user selected. The other way you loop through all the items in the listbox. Here is an example:

Code:
    Dim varReport As Variant
    
    For Each varReport in Me.lstReports.ItemsSelected
       .
       .
       .
    Next varReport
Do you know how to program with VBA? The easiest way to get the data for the second listbox is to create a SQL string with all the PO numbers.
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
Ha, in all my experience I've never come across the ItemsSelected!

Oops
__________________
George
Home | Blog
Reply With Quote
  #5 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Ahhh you ARE human after all
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
  #6 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
Who told you that
__________________
George
Home | Blog
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 49
I am using this code .i don't get it what to do?

Private Sub PalletIDOrTrackingNo_AfterUpdate()
Dim i As Variant
Dim myString As String

For i = 0 To Me.PalletIDOrTrackingNo.ListCount - 1
If Me.PalletIDOrTrackingNo.Selected(i) Then
myString = myString & PalletIDOrTrackingNo.Column(1, i) & ","
Me.LONO = (DLookup("LONo", "PRODUCTION", "((CustomerCode=[Forms]![ShippingAlerts]![CustomerID_Combo]) and (PONo=[Forms]![ShippingAlerts]![PONo])and (PalletNo=myString))"))
Else
Me.LONO = 1
End If
Next i

End Sub

can u give me simple code taking any example, in which u select multiple items from listbox and generate list for another listbox.
Reply With Quote
  #8 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
This will result in a string like this
Code:
myString = myString & PalletIDOrTrackingNo.Column(1, i) & ","
1, 2, 3, 4, 5, ...<snip>... 99,
And your DLookup is then going
Code:
PalletNo=myString
And I imagine you don't have a PalletNo with the value "1, 2, 34, ..."

Try this...
Create a second listbox
Code:
Me.mySecondListBox.RowSourceType = "Value List"
Me.mySecondListBox.RowSource = ""

For i = 0 To Me.PalletIDOrTrackingNo.ListCount - 1
    If Me.PalletIDOrTrackingNo.Selected(i) Then
        Me.mySecondListBox.AddItem PalletIDOrTrackingNo.Column(1, i)
    End If
Next i
__________________
George
Home | Blog
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 49
Thanks buddy for ur help But I got my solution.

Last edited by billy_pit; 01-17-08 at 15:33.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 103
So, to take it the next step, assume we have an intersection table that we'll modify using the multi_select.

We have an items in the list in our intersection table. We want to "select" those items until the user modifies the list. How would we do that?

Sorry if this looks funny, I am sending from blackberry.

Last edited by jdostie; 12-07-08 at 19:01.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 103
Clarification

Back to what I posted yesterday, I have the following (please excuse some of the names, I was rushing through this, and I'll need to fix the names, but: Below is code from a form that I'll use to set up user permissions. I know there are different thoughts on that subject, but for now, this is just about multi-select.

The idea is to load all the forms (I will probably add reports and queries) to the list as well as items from a table that define some other permissions like (can edit certain fields, can setup permissions, can delete records from table x, whatever). That's not important because there are similar applications for other intersection tables, so let's not get caught up on the question of workgroup security files please.

For any intersection table, we'd want a way to have a multi-select box populated with all of the possibilites. Hopefully, preselect the values currently in the intersection table, and upon update, remove any selections that were not in the itemsselected collection and add any that were missing from the table.
__________________________________________________ ___________________
Option Compare Database

Private Sub Command6_Click()
Dim ctl As Control
Set ctl = PermissionList
Dim varItm As Variant

'this is where I would like to next loops to compare everything in dbo_tblPermissionItems
'with everything in Permission items, deleting from dbo_tblPermissionItems if not selected
'and adding to dbo_tblPermissionItems if selected and not already in the table
For Each varItm In ctl.ItemsSelected
MsgBox ctl.ItemData(varItm)
Next varItm
End Sub
__________________________________________________ _________________________

Private Sub Form_Close()
Me.text2.RowSource = ""
Me.PermissionList.RowSource = ""
End Sub
__________________________________________________ __________________________
Private Sub Form_Load()
Dim rs As New ADODB.Recordset

Dim query As String
Dim permlist As String

query = "select permissionname from dbo_tblPermissionItems"
rs.Open query, CurrentProject.Connection, adOpenStatic
permlist = ""

While Not rs.EOF
If Len(permlist) > 0 Then permlist = permlist & ","
permlist = permlist & rs.Fields!PermissionName
Wend

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms

If Len(permlist) > 0 Then permlist = permlist & ","
permlist = permlist & obj.Name
Next obj

Me.PermissionList.RowSource = permlist
'this is where I would like to update me.permissionlist to select everything in the table
'it would make it easier to update later, and also would let the user know what the permissions currently are
'set for
Me.text2.RowSource = "Select dbo_tblUserAccounts.UserID, encrypt([login]) as expr1 from dbo_tblUserAccounts;"
End Sub
__________________________________________________ ____________________________


I am guessing I am about half way there with this, I can certainly accomplish what I want simply by deleting all references in the table and adding new records corresponding to the new list, or via some more complicated method. But, I have to believe this has already been hashed out and optimized a million times over. What is the best approach you have seen?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On