Access 2003 Multi Select "Extended" listbox Issue

[Icekola]

Hi, I am a Technical Sales Coordinator for a Telecom Company. To track my orders I have created an MS Access 2003 Form. I am having an issue with multi select “Extended” listbox. Keep in mind I am a dummy

Row Source Type: Value List
Row Source: "FLEX";"DIA";"PL";"TF";"RCF";"BBL";"PRI";"SDT";"TC";"MPLS";"DLD";"ELD";"XOP5";"XOP6";"XOP7";"XOP13";"XOP15";"XOP16"

After Update: [Event Procedure]

Basically I need a Procedure that for which (as example):

if I multi select FLEX and DIA the output onto textbox (Text61) is FLEX;DIA;

Or for example:

If I multi select RCF, SDT and MPLS the output onto textbox (Text61) is RCF;SDT;MPLS;

I believe the following thread

http://www.tek-tips.com/viewthread.cfm?qid=1399484

pointed me towards the right direction however because I am NOT a programmer, I am unable to get the result I want no matter how many times I tried modifying that code.

I would appreciate any help. Thanks in advance.

 

[MajP]

I am unable to get the result I want no matter how many times I tried modifying that code.

1. What results do you get?
2. Any error messages?
3. What does your code look like?
4. What part of the code do you not understand?
5. Where does the code break?

Looks to me like you already have a procedure from the link that does exactly what you want, so I am not sure what you are looking for.

 

[Icekola]

Private Sub List126_AfterUpdate()

Dim Lbx As ListBox, Pack As String, idx
   
   Set Lbx = Forms!OETv2!List126
   
   For Each idx In Lbx.ItemsSelected
      If Pack <> "FLEX" Then
         Pack = Pack & "FLEX;" & Lbx.Column(1, idx)
      Else
         Pack = Lbx.Column(1, idx)
      End If
   Next
   
   For Each idx In Lbx.ItemsSelected
      If Pack <> "DIA" Then
         Pack = Pack & "DIA;" & Lbx.Column(1, idx)
      Else
         Pack = Lbx.Column(1, idx)
      End If
   Next
   
   Me!Text256 = Pack
   
   Set Lbx = Nothing
   
End Sub

I have been modifying that code from the link I provided to make it work for my purpose. However I don't know anything about VB.

The modified code I have above is what I have currently and while it does not give me any errors, it does not display FLEX;DIA when i multi select both. Instead it displays FLEX;FLEX;DIA;DIA.

I have tried modifying in many ways, but most trial and error gives me a an error msg in regards to Pack = Lbx.Column(1, idx) which i have no idea what that means.

I could post every thing I have tried, but that would likely clobber up this thread.

I really have no idea what I am doing and could use your expertise.

 

[MajP]

I see why it is doing what you say, but this is a big modification from the original code. You have two loops in it vice 1. You added some if checks from the original code as well. The original code should have been very close, did it not work?

In plain language what are you trying to do when you check that the value of the string does not equal "DIA" or "FLEX". Was this just a attempt to fix an earlier problem?

I used your names in the original code. It works find for me. The only issue is the column you are taking data from. I have a zero for my column because the first column of a list box has a zero index. (i.e. Lbx.Column(0, idx)). Now often you may have a hidden column as the first column. From what you describe it looks like you should use a zero instead of a one.

I think this code will work. I used your names.

Private Sub List126_AfterUpdate()
  Dim Lbx As ListBox
  Dim Pack As String
  Dim idx As Variant
  Set Lbx = Forms!OETv2!List126
  For Each idx In Lbx.ItemsSelected
    If Pack <> "" Then
       Pack = Pack & ";" & Lbx.Column(0, idx)
    Else
      Pack = Lbx.Column(0, idx)
    End If
  Next idx
   Me!text256 = Pack
   Set Lbx = Nothing
End Sub

 

[TheAceMan1]

How are ya Icekola . . .

. . . and this:

[blue]   Dim Lbx As ListBox, Pack As String, idx
      
   Set Lbx = Forms!OETv2!List126
   
   For Each idx In Lbx.ItemsSelected
      If Pack <> "" Then
         Pack = Pack & ";" & Lbx.Column(1, idx)
      Else
         Pack = Lbx.Column(1, idx)
      End If
   Next
   
   Me!Text256 = Pack
   
   Set Lbx = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Icekola]

Whoa, it works. Much appreciated. Thank you. Never even thought about changing that 1 to a 0. Again thank you.

 

[MajP]

Indices in VBA can be confusing. Some are zero based others start at one.

 

[Icekola]

You must of posted while i was replying TheAceMan1. Your code works too if i change the 1 to a 0.

Thank you guys for helping me out.