AddItem in ListBox (Access) using VBA

[March]

Hello,

I have a question about how to addItem in List box (Access) using VBA.

I have one combo box and one list box. The values in the list box will
change when the value in combo box changes.

I try the code below:

Dim rst As DAO.Recordset
Dim db As DAO.Database

If Me.ComboBox = "A" Then
sqlCmd = "SELECT A FROM TableName;"
Set rst = db.OpenRecordset(sqlCmd)
Do Until rst.EOF
If Not IsNull(rst!A) Then
With Me.ListBox
.RowSource = vbNullString
.AddItem rst!A
End With
End If
rst.MoveNext
Loop


TableName contains field "A" that I need all records in "A" show in the
List Box. I also need to use list box array.

 

[Douglas J. Steele]

Get rid of the line

..RowSource = vbNullString

That erases what's already in the list!

However, I don't see the point of looping through a recordset. Simply use

If Me.ComboBox = "A" Then
Me.ListBox.RowSource = _
"SELECT A FROM TableName " & _
"WHERE A IS NOT NULL" & _
"ORDER BY A"
End If

 

[March]

Thank you so much. That would help me done my project for today.

 

[March]

Do you have any example how to work on ListBox Array?


Thanks,
March

 

[Douglas J. Steele]

What do you mean by "ListBox Array"?

Are you saying that you want to be able to programmatically refer to the
value in the third column of the second row of the list box? That would be

Me!MyListBox.Column(2, 1)

(numbering starts at 0)

 

[March]

In my project, I use 2 list boxes. Let say Left(L) and Right (R). Both have
only one column. L has a list of item, R has nothing when loading form.

I have one bottom that when I click the bottom the selected multiple-item
from L, will copy to R.


Thank you in advanced;

March

 

[Douglas J. Steele]

What's the RowSource for the two list boxes?

 

[March]

Anyways, Thank you so much.

However, I have finisehed this part of my project.


March