I am using VBA in Access 2003.

I have a listbox on an Access Form.

This listbox is populated using a SQL Query and that works fiine.

The SQL Query stores 4 text fields in the listbox (addr, city, state and zip).

Now I am hoping that I can read each column of data and store it in 4 separate textbox controls.

I have tried the following, but the results of the assignment to the textboxes are always Null.

Private Sub CheckWorkAddr()

Dim strSQL As String
Dim intSupporterID As Integer

intSupporterID = Me.comboSupporters

strSQL = "SELECT Supporters.SupporterID, Supporters.Addr, Supporters.City, Supporters.State, Supporters.Zip FROM Supporters WHERE Supporters.SupporterID = " & intSupporterID

Me.lstWorkAddr = Null
Me.lstWorkAddr.RowSource = strSQL

txtAddr = Me.lstWorkAddr.ItemData(1)
txtCity = Me.lstWorkAddr.ItemData(2)
txtState = Me.lstWorkAddr.ItemData(3)
txtZip = Me.lstWorkAddr.ItemData(4)

End Sub

I was hoping to NOT have to perform individual db reads to get each of these values.

Any idea how to do this?


I appreciate any assistance.
- Bill

Hi,
You use the column property on the listbox. As in:

txtAddr = Me.lstWorkAddr.Column(1)

You can also specify which row to read too...

txtAddr = Me.lstWorkAddr.Column(1,2)

...would give you column 1 of row 2

Hope this helps,
Kieron



____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Yes - that was it.
Thanks.