Adding values multiple columns to listbox in form access vba

Question

I have problem with adding values to multiple columns in listbox in access. I have tried solution like this: Adding items in a Listbox with multiple columns and this: vba listbox multicolumn add [duplicate], but it doesn't work. Listbox in my case hasn't a property "List". I have compile error:

Private Sub cmdAddPosition_Click()

Dim i As Integer

Me.lstAddPositions.ColumnCount = 7

If Me.txtAddPos.Value = i And i > 0 And i < 50 Then
     Me.lstAddPositions.AddItem (Me.txtAddPos.Value)
    'Me.lstAddPositions.AddItem(Me.txtAddPos.Value,(i))
     Me.lstAddPositions.List(0, i) = Me.txtAddPos.Value
    'Me.lstAddPositions.Column(0, i) = Me.txtAddPos.Value 'adding number of position
    'Me.lstAddPositions.Column(2, i) = Me.lstAddHidden.Column(0, 0) 'adding titel
End If

Me.lstAddPositions.Requery

End Sub

What can I do in this situation?

Mohair

have you seen this walkthrough ? find the .List(.ListCount - 1, 1) = TextBox2.Value and make changes to suit your case? – Conant

Still I have problem with .List and now even with .ListCount, for first method doesn't exist, even on a list on methods for this list I can't choose this method, second error is for .ListCount: Invalid or unqualified reference – Mohair

Doesn't i, in your case, have to be between 0 and 6? You only have 7 columns, so you can only go from List(0, 0) to List(0, 6). – Wester

Variable i is for rows, it's maximum number of rows which I can have and durin one execution this i as just one value. – Mohair

Take a look at ScaryJim's answer here: forums.hexus.net/programming-web-development/… – Wester

I see that even if I have just adding to first column I don't have any result on my list: Me.lstAddPositions.AddItem Me.txtAddPos.Value, I'm clicking a button and nothing, listbox still is empty: img – Mohair

In documentation for vba access 2007 also is not this method List: msdna – Mohair

Answer 1

Here is an example of adding items to a multi-column unbound list box on an access form if the row source is a value list. You have to add it by creating a string that you place in a value list.

Private Sub cmdAddPosition_Click()
    Dim i As Integer

    Me.lstAddPositions.ColumnCount = 7

    If Me.txtAddPos.Value = i And i > 0 And i < 50 Then
         Me.lstAddPositions.AddItem "Col1" & "," & "col2" & "," & "Col3" & "," & _
         "Col4" & "," & "Col5" & "," & "col6" & "," & "col7"  &";"     
    End If

    Me.lstAddPositions.Requery
End Sub

Cry

Answer 2

Firstly set the following properties for list box

Row source type: value list Column count: 2

Suppose name of list box is : listName Let us assume that u want to add two different elements in two different columns and two different strings are stored in

String1 and String2 then follow the code

Code:

Dim strName as string
strName=String1&";"&String2
Me.listName.addItem strName

Draco

Answer 3

select propety

Row Source Type => Value List

Code :

ListbName.ColumnCount=2

ListbName.AddItem "value column1;value column2"

Monmouthshire

Answer 4

I didn't quite understand Zaider's answer, but taking his sample I ended up with this which worked for my unbound listbox:

Me.listbox2.AddItem (Me.listbox1.Column(0) & ";" & Me.listbox1.Column(1))

Alpestrine

Answer 5

Here's a full example of how you can add multiple columns to a listbox.

SQL to create the table:

DROP TABLE Test;
CREATE TABLE Test (TestID AUTOINCREMENT(1, 1),
                   TestName TEXT,
                   TestDescription TEXT,
        CONSTRAINT TestPKey PRIMARY KEY (TestID));
INSERT INTO Test (TestName, TestDescription)
          VALUES ('Test A', 'Testing Record A')";
INSERT INTO Test (TestName, TestDescription)
          VALUES ('Test B', 'Testing Record B')";
INSERT INTO Test (TestName, TestDescription)
          VALUES ('Test C', 'Testing Record C')";

Button Event Code:

Private Sub TestButton_Click()
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field
  Dim lb As ListBox
  Dim rowStr As String

  Set rst = CurrentDb.OpenRecordset("Test")
  Set lb = Me.TestListBox

  ' Set the number of listbox columns to reflect recordset fields
  lb.ColumnCount = rst.Fields.Count
  ' Set the row source type to Value List
  lb.RowSourceType = "Value List"
  ' Erase the listbox data so we can populate it
  lb.RowSource = ""

  ' If ColumnHeads property is enabled, then first record is field
  ' names.  Lets populate those.
  If lb.ColumnHeads Then
    rowStr = ""
    ' Build a string for each record
    For Each fld In rst.Fields
      rowStr = rowStr & replace(fld.Name,",","") & ","
    Next
    ' Strip final comma
    rowStr = Left(rowStr, Len(rowStr) - 1)
    ' Add each record (all fields) at once.
    lb.AddItem rowStr
  End If

  ' Loop through each record
  Do Until rst.EOF
    ' Build a record string and add it
    rowStr = ""
    For Each fld In rst.Fields
      rowStr = rowStr & replace(fld.Value,",","") & ","
    Next
    rowStr = Left(rowStr, Len(rowStr) - 1)
    lb.AddItem rowStr
    rst.MoveNext
  Loop

  ' Close and release objects
  rst.Close
  Set fld = Nothing
  Set rst = Nothing
  Set lb = Nothing
End Sub

If you wish to change the listbox contents you will need to blow away and rebuild the recordsource property of the listbox. In my experience, trying to modify individual rows when multiple columns are involved - never works.

Hopefully that helps out.

Mathia