I'm assuming you're talking about the situation where each time you get data
from the pivot table you have different field names (e.g. the field name is a
date). Refreshing won't work because whenever you run the report you have
different field names. I have a report that is based on a crosstab query,
which is basically the same as a pivot table. Here is a copy of some code
I'm using for that report that I copied and modified from The Access 2000
Developers Handbook (Getz, Litwin and Gilbert). This is a great book with a
lot very useful examples. You have to create field labels and corresponding
text boxes that correspond to the fields that have the date (or whatever
you're using). I created eight labels and textboxes called lblCol1 thru
lblCol8 and txtCol1 thru txtCol8. The labels get renamed to the date values
in the code. The first loop control and the arrays within it will have to be
modified depending on the fields you have in your pivot table and the number
of fields you define. I had a max of eight values that could be printed
(anywhere from one to eight may be printed based on dates the user selects
from a list box) and the DOS field was the fourth column in the query. IF you
have access to the above mentioned book it may be easier to just use that. I
don't know if they have their examples, etc. defined online anywhere. This
isn't a real simple way fo doing this, but it's the only way I could find!
Dim i As Integer
Dim strName As String
Dim rstSource As ADODB.Recordset
On Error GoTo FoundError
' Variable 'sheetDateCount' has the number of DOS values selected from
frmTreatment.
' Fill in the DOS headings and the control source values for each field.
Because a crosstab
' query is used to create the report data, the field names for the data
values are the
' DOS values, e.g. if dates being printed are 3/1/2005 and 3/2/2005 then the
field names for
' the data values will be 3/1/2005 and 3/2/2005. This loop is executed once
for each DOS
' value. For each one the field 'strName' is set to the DOS value, the label
is also set to
' that, and the control source for the data value is set to that.
Set rstSource = New ADODB.Recordset
rstSource.Open Me.RecordSource, cn, , , adCmdTable
For i = 4 To sheetDateCount + 3
strName = rstSource.Fields(i - 1).Name
Me.Controls("lblCol" & i - 3).Caption = Format(strName, "mm/dd/yy")
Me.Controls("txtCol" & i - 3).ControlSource = strName
Next i
' There is room for up to eight columns (DOS values) on the report. If less
than 8 are
' being printed, then the remaining columns need to be blanked out.
If sheetDateCount < 8 Then
For i = sheetDateCount + 1 To 8
' I had to add these next two lines to stop an error from occurring. Not
sure why
' the error was occurring and not sure why these were needed, but it works!
Without
' this, if you tried to print a report with four dates or less it blew up.
Me.Controls("lblCol" & i).Caption = Format(strName, "mm/dd/yy")
Me.Controls("txtCol" & i).ControlSource = strName
Me.Controls("lblCol" & i).Visible = False
Me.Controls("txtCol" & i).Visible = False
Next i
End If
