Get other than bound column from multi-select listbox (1 Viewer)

[madEG]

Hello,

I have two listboxes. One for Activities and the other for Organizations.

I set the Org listbox to be multi-select so I could run a loop on that listbox to join multiple Orgs to a single Activity. That part works well.

To do so, I am using the bound column (which is the pkey value) from the multi select listbox, and on the single select listbox I'm using the ListboxName.value to gather the pkey for the "1" side of this 1:Many series of inserts.

Now... I want to use one of the other-than-bound-column value from the multi-select listbox, but I don't know how. When setting a value I only know of the use of :

ListBoxOrganizations.ItemData(varItem)

and I don't know how to do the equivalent of

ListBoxOrganizations.ItemData(varItem's column(2).value)

yes, I just made that up, but I hope you get the point. Meaning, as the loop cruises the ItemsSelected on the multiselect I'd like to use other than the bound column when setting variables.

Does this make sense? How can I do this?

Thanks!

Update: I've tried using the column property to then cruise to the proper record in the listbox, e.g. :

 txtCollaborationDesc = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
[code]

But this seems to return the column(2) value of the first record loaded into the list box, as if the varItem piece is being ignored.  I believe the loop is working properly, as when the inserts are happenning correctly with the bound column of the multi-select list is correctly present as an Fkey in the resulting child records.

I just can't get any other column's value for some reason.

Oh duh  MS Access 2010 and this is an accdb.  :)

 

[spikepl]

        Dim varItem as Variant
        For Each varItem In Me.lstSomeListbox.ItemsSelected
            debug.print Me.lstSomeListbox.Column(2,varItem) ' lists what is in the 3rd column
        Next varItem

 

[madEG]

Spikepl,

Thanks, just a moment before or after your post I came across that use of the column property.

The weird thing is that what returns is the column I want, but for the wrong record.

It seems to always deliver the value from the first record shown in the listbox, even though the child records being built in the code do end up with the FKeys correct, showing that the loop is actually making its way through the itemsselected in the multiselect list.

For example, if selected the last two records in the listbox, the resultant child records are created using the last two records IDs for the children record's Fkeys, but the name part always is taking the column3 value from the first record shown in the listbox - even though it is not selected.

The complete code in case I am overlooking stupid...

Also, the message in the sub where the txtCollaborationName field is built always shows the column for the first record shown in the listbox.

I'm stumped.

Private Sub btnAddNewRecord_Click()
   Dim varItem As Variant

' Test for *ANY* records selected in MULTI SELECT listbox:
   If ListBoxOrganizations.ItemsSelected.Count = 0 Then
      MsgBox ("You must Select Organization Record(s) for tagging.")
      Exit Sub
   Else
       ' do whatever
       For Each varItem In ListBoxOrganizations.ItemsSelected
               BuildName ' check if the auto name is turned on, if Y then build name/desc, else use what is allotted in the fields
                ' do inserts here
                If (Format(Trim(txtCollaborationName)) <> "") And (ListBoxOrganizations.ItemData(varItem) <> "") And (listboxActivities.Value <> "") Then
                   Dim strSQL As String

                   strSQL = "INSERT INTO dbo_Collaborations (OrgIDf, ActivityIDf, CollaborationName, CollaborationDesc, CollaborationScope, CollaborationReason) VALUES (" & _
                   ListBoxOrganizations.ItemData(varItem) & _
                   ", " & _
                   
[listboxActivities].[Value] & _
                   ", '" & _
                   Replace(Format(Trim(txtCollaborationName)), "'", "''") & _
                   "', '" & _
                   Replace(Format(Trim(txtCollaborationDesc)), "'", "''") & _
                   "', '" & _
                   Replace(Format(Trim(txtCollaborationScope)), "'", "''") & _
                   "', '" & _
                   Replace(Format(Trim(txtCollaborationReason)), "'", "''") & "');"
                   
                   MsgBox ("strSQL: " & strSQL)
                   'DoCmd.SetWarnings False
                   DoCmd.RunSQL strSQL
                   'DoCmd.SetWarnings True

                   ListBoxCollaboration.Requery
               Else
                   MsgBox ("Select an Activity, then Organization, then enter Collaboration details (Or Autoname using Multi-Select if Collaboration's Scope/Rationale is not known) before pressing ADD.")
               End If

               txtCollaborationName = ""
               txtCollaborationDesc = ""
               txtCollaborationScope = ""
               txtCollaborationReason = ""

       Next varItem
   End If


End Sub


Private Sub BuildName()
    If chkAutoNameYN = True Then
    
       MsgBox (Me.ListBoxOrganizations.Column(2, varItem)) ' lists what is in the 3rd column
    
       txtCollaborationName = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
       txtCollaborationDesc = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
    Else
    ' use manually or double-clicked values vs auto built...
    End If

End Sub

 

[madEG]

...oh man... it's because the varItem being used in the BuildName is not the same varItem in the record building routine, and so its value is not the same...

That's it, isn't it? Dangit!

Yep, that was my problem. Thanks for the help spikepl!