Adding Recordset to array then filter pivottable based on array

[CWalsh87]

Afternoon, Apologies I have posted this in another section of the forum, but was advised to move it, i'm not trying to spam!

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

 

[CWalsh87]

To make it a bit clearer:

This works:

Me.subfrmDeliveries.Form.PivotTable.ActiveView.RowAxis.FieldSets(1).Fields(0).IncludedMembers = Array("5113","5112")

but the code in the first post doesn't.

I need to just get the data from the table into the array and this should work?

Any ideas would be greatly appreciated or suggestions

Cheers
Chris

 

[PHV]

What about this ?
Me.subfrmDeliveries.Form.PivotTable.ActiveView.RowAxis.FieldSets(1).Fields(0).IncludedMembers = col

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CWalsh87]

Thanks for the reply.

I've tried the above and it just crashes access. It "encounters a problem and needs to close"

The error is:

AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: oleaut32.dll
ModVer: 5.1.2600.6058 Offset: 00004c27

Any ideas?

Cheers
Chris

 

[PHV]

And this ?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim col()
Dim i As Integer
i = 0
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTempList")
Do While Not rs.EOF
  ReDim Preserve col(i)
  col(i) = rs![Item-no]
  i = i + 1
  rs.MoveNext
Loop
Me.subfrmDeliveries.Form.PivotTable.ActiveView.RowAxis.FieldSets(1).Fields(0).IncludedMembers = col

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CWalsh87]

Thanks, i've tries the above and i get the
same error, I'm starting to think the code is correct but there's a problem somewhere in Access

Any suggestions really appreciated.

Cheers
Chris

 

[CWalsh87]

Figured it out!!

I changed the field type from Number to Text in my table and that resolved the problem. I don't know why that sorted it but it did

Thanks again for your help!

Chris