List box - Call back function

[ssecca]

I am using a call back function to populate a list box. The items in the list box are subject to a choice made in a previous field. None of this seems to be a great problem except... I can not seem to control the number of items permitted in the list box. If I happen to call for a list that contains only three items, the next and all future calls will be limited to three items regardless of how many items it should really have. If the second of subsequent call happens to have 2 items I get an error where the function is trying to read line three that does not exist. If the subsequent call has 20 items I only get three. I know the constant acLBGetRowCount controls this, but I don't know how to reset it each time the callback is requested. It appears that this constant only gets set one time. Is there a way to change the number of rows value when using a call back?

 

[bodmin]

The following is a function that I have used to do this function in other databases.

Function FillWithTableList(ctl As Control, vntID as Variant, lngRow As Long, lngCol As Long, intCode As Integer) As Variant

Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim qdf As ADOX.View
Dim intCounter As Integer
Static sastrTables() As String
Static sintNumTables As Integer
Dim varRetVal As Variant

varRetVal = Null

Select Case intCode
Case acLBInitialize
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine the total number of tables + queries
sintNumTables = cat.Tables.Count + cat.Views.Count
ReDim sastrTables(sintNumTables - 2)
'Loop through each table adding its name to the list box
For Each tdf in cat.Tables
If Left(tdf.Name, 4) <> "MSys" Then
sastrTables(intCounter) = tdf.Name
intCounter = intCounter + 1
End If
Next tdf
'Loop through each query adding its name to the listbox
For Each qdf in cat.Views
sastrTables(intCounter) = qdf.Name
intCounter = intCounter + 1
Next qdf
varRetVal = sintNumTables
Case acLBOpen 'Open
varRetVal = Timer 'Generate unique ID for control
Case acLBGetRowCount 'Get number of rows
varRetVal = SintNumTables
Case acLBGetColumnCount 'Get number of columns
varRetVal = 1
Case acLBGetColumnWidth 'Get column width
varRetVal = -1 ' -1 forces use of default width
Case acLBGetValue 'Get the data
varRetVal = sastrTables(lngRow
End Select
FillWithTableList = varRetVal
End Function

Sorry about the loads of code but this is a function I had lots of trouble playing about with as well so I am unsure which bit fixed the same problem as you have but it seemed to be working for filling a listbox with a choice of tables and queries on an Access 2000 system. Hope it helps cheers.

 

[ssecca]

The code posted is essentially the code I am using. The problem I am having is that the code does not seem to re-initialize each time it is called therefore the row count remains fixed to the first call and does not change. This is a problem that can be handled when the subsequent call has fewer rows but is a real problem when it has more rows. In that it chops off the extra rows.

Thanks for any help anyone can lend to this.

 

[bodmin]

Perhaps try forcing the row value to be reset outside of the function and see what difference this makes to the function.