Populating a listbox via a dynamic array

[esusda]

Hi I'm having a real problem developing an application and would very much appreciate help.

I am obtaining a list of items from a database, and feeding them into a recordset. From there I have created an array, and via a loop have added all of the results into the array. Where I have a problem is with populating these results into a listbox since this needs to obtain results DEPENDENT on each item in the listbox. The version of VB I have to use (it's through Access 97) does NOT recognise lstbox.AddItem, which makes life very difficult as I assume it would be simple to loop through a query adding an item each time with the appropriate array field. However the only way I know to get information in to a listbox though Access 97-VB is through a .rowsource(strSQL)...

Here is the code (the array here is non-dynamic as ReDim each time in the loop was getting no results at all):

Dim strSQL, strTaskRuleSetID As String
Dim intArrayCount As Integer
Dim varArray(60) As Variant
Dim intCounter As Long

Dim dbs As Database
Dim rstRoutStep As Recordset
Set dbs = CurrentDb

'Populates lstRuleNames via a dynamic array
strSQL = "SELECT TASK_RULE_ID FROM TMS_TASK_RULE_SET_RULE WHERE TASK_RULE_SET_ID = " & strTaskRuleSetID & ";"

Set rstRoutStep = dbs.OpenRecordset(strSQL)
intArrayCount = 0

Do
Do While rstRoutStep.EOF = False
varArray(intArrayCount) = rstRoutStep.Fields(0)
rstRoutStep.MoveNext
intArrayCount = intArrayCount + 1
If rstRoutStep.EOF = True Then
Exit Do 'Exits inner loop
End If
Loop
Loop Until rstRoutStep.EOF = True

The problem is HOW to now populate a listbox (lstRuleNames), which needs to make another SQL query but based on the array values. The line below would return 3 results: but ONLY if there were that number, any less and it would not work, any more and it would still display 3..

Form_FrmRulViewRuleSet.lstRuleNames.RowSource = "SELECT TASK_RULE_NAME, TASK_RULE_SEQUENCE_NUMBER FROM TMS_TASK_RULE INNER JOIN TMS_TASK_RULE_SET_RULE ON TMS_TASK_RULE.TASK_RULE_ID = TMS_TASK_RULE_SET_RULE.TASK_RULE_ID WHERE TMS_TASK_RULE.TASK_RULE_ID = " & varArray(0) & " OR TMS_TASK_RULE.TASK_RULE_ID = " & varArray(1) & ";"
TMS_TASK_RULE.TASK_RULE_ID = " & varArray(2) & ";"

If anybody can suggest how I to use all array values to do this I'd be extremely grateful. Many thanks

~eSUSDa~

 

[Alexandre]

Might be stupid, but couldn t you fill the table you listbox is based on with your values (even directly from the recordset you created) then requery the listbox?

 

[bob makepeace]

Hi,

No your not stupid - that's the way to do it!

bob

 

[Alexandre]

I am re-reading your post and see that in fact you use your recordset as a criteria to define the values populating your listbox. Perhaps you could use a select into query to create a table with the relevant values, instead of your recordset. Then use the table to build the new recordsource query. Howeverm the created table is likely to duplicate records form TMS_TASK_RULE_SET_RULE which sounds inefficient.

I can think of an alternative where you would add a field Select (boolean, default value is false) to TMS_TASK_RULE_SET_RULE, and you would just populate the Select field with True/False according to whether the TMS_TASK_RULE.TASK_RULE_ID value exists in your Recordset.
Then the record source query for your listbox would use a statement Where Select = true.

 

[Alexandre]

(my last post is becaus I assume that you want to keep TMS_TASK_RULE_SET_RULE as a base list and dynamically select values from it.)

 

[esusda]

Thanks very much Alexandre, you're suggestions are extremely helpful. Unfortunately a stipulation I forgot to mention is that I cannot alter existing tables in the system! As such I am going to use the temporary table creation plan.

As you said, it's inefficient, but with the limitations of this version of Access/VB etc, I can't see another way to do it.. unless anybody else has an idea?

Thanks again
~eSUSDa~

 

[kal-el]

You can load a list or combo box from an array by using a function as the RowSourceType.

For more information look for RowSourceType in the Help Index and chose the "Create a list box or combo box that gets its rows from a function" topic.