0

I populate a list box on a user form with the following code:

Dim CONSTRING As String
Dim SQL As String
Dim RSSQL As New ADODB.Recordset

CONSTRING = "xxxxxxxxxx”

SQL = "SELECT [ID], [Name] FROM [Table X] WHERE Name <>'' ORDER BY [Name] ASC;"
RSSQL.Open SQL, CONSTRING, adOpenStatic, adLockBatchOptimistic, adCmdText

Set Forms!Cockpit.LB.Recordset = RSSQL

RSSQL.Close
Set RSSQL = Nothing

This code works perfectly. I can run this code as often as I want, it always populates the listbox with the expected data.

BUT: If I select one item in the Listbox and try the run the code again, it crashes everytime without returning any error code.

Any idea how to solve this? Thanks in advance..

aTuring
  • 47
  • 6

2 Answers2

0

Try 'RowSource':

Dim SQL As String

SQL = "SELECT [ID], [Name] FROM [Table X] WHERE Name <>'' AND NOT Name IS NULL ORDER BY [Name] ASC;"
Set Forms!Cockpit.LB.RowSourceType = "Table/Query"
Set Forms!Cockpit.LB.RowSource = SQL
LeJandr
  • 11
  • 1
0

More common for listbox - RowSource property. If you use Recordset property, you should be more careful.
I think, listbox items is bounded to recordset rows. When you change LB.Recordset, previous recordset will be closed and binding "will hang in the air" - this cause memory conflict and app crash.

Remove LB items before changing .Recordset property.

Dim i As Long
For i = Forms!Cockpit.LB.ListCount-1 To 0
   Forms!Cockpit.LB.RemoveItem (i)
Next i   

Set Forms!Cockpit.LB.Recordset = RSSQL
ValNik
  • 1,075
  • 1
  • 2
  • 6