VB and SQL (List Box)

[rmartinez]

Hi Everyone,

I need help with a function I am trying to create. I have a table(table1) with three columns, and about 100 rows. I have a form(form1) with a listbox(listbox1) that populates itself using an SQL statement. It only calls for column A out of the three (A,B,C …..) to show in the list box. I need the function to execute some code based on what the user has selected from the multi-select listbox1. Heres kind of the layout I have, I had more, but it just looks more confusing, and I might have been heading in the wrong direction. Can someone please help me convert this pseudocode I wrote… I'm not that familiar with DAO and recordset and all that syntax so that’s where I am ultimately stuck I guess. I had a recordset.EOF loop in my code at one time, if that helps someone understand what I am trying to do, then again I could have been way off..

Dim x As Integer
For x = 0 To listbox1.ListCount - 1
If listbox1.Selected(x) Then
' pull equivalent DAO fields from its control TABLE!
' i need two variables stored from each selection
'example: columns are A B C in the TABLE are present,
'i want to store A in one variable name and C in another
'for each selection of A (which happens to be the only column
'populating listbox1)

' execute code for each selected A from listbox 1 involving, its A variable and same
'row corresponding C field variable
End If
Next x

I would really appreciate it if someone can help me.

 

[modest]

The best way to do this (depending on the size of the other columns) is to load all three columns in the listbox, but only show column A.

You can hide the other columns by changing the column widths in the property window of the listbox (i.e. Column Widths: 2.5";0";0")

The code to loop through only items that are selected in the listbox is:

For Each row In Me.listbox1.ItemsSelected
  'You can reference each of the selected columns as so
    Me.listbox1.Column(0, row)   'Column A
    Me.listbox1.Column(1, row)   'Column B
    Me.listbox1.Column(2, row)   'Column C
Next row