Adding listbox to array

[CWalsh87]

Afternoon,

Basically what I am trying to do is enter a value into a listbox (value is stored in a table) add these values into an array, then update a pivot table based on the values in the array.

I can add the items to the listbox / table. This works fine.

I can filter the pivot table based on manually input arrays eg/ array ("Part1", "Part2") I just can't seem to get the pivottable to filter based on a dynamic array. The code I am using is as follows:

Dim Ar1
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim col()
Dim i As Integer
Dim Results


i = 0
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTempList")
Do While Not rs.EOF
i = i + 1
ReDim Preserve col(i)
col(i) = rs![Item-no]
Ar1 = Array(col(i))
rs.MoveNext


Loop

Me.subfrmDeliveries.Form.PivotTable.ActiveView.RowAxis.FieldSets(1).Fields(0).IncludedMembers = Ar1

It just crashes when i do this, can anyone help please?

Cheers
Chris

 

[SkipVought]

Please post MS Access questions in MS Access forums like forum705.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]