Re: PivotTable?

From: Ron Weiner (weinNoSpam1_at_mindspring.com)
Date: 07/12/04 

Date: Mon, 12 Jul 2004 17:00:30 -0400

Alex

Yes! I have had to do this *many* times. If you can live with a read only
data*** you need to:

Create a form with a bunch of text boxes named something like text1, text2,
text3,... The text boxes will need attached labels named label1, label2,
label3,... Create as many text boxes as you think you will *EVER* need.

When you need the form open it an set its Record source to the CrossTab
query or a sql statement.

In the OnLoad event of the form you will need to run some code that looks at
your forms' recordset and sets the all of the TextBoxes Source and the Label
Captions to the Field names of the reordset. It will also have to hide any
textboxes that are not used. The code might look like

    Set rst = Me.Form.RecordsetClone
    For i = 0 To rst.Fields.Count - 1
        If i < 30 Then
            Me("Text" & i + 1).ControlSource = rst.Fields(i).Name
            Me("Label" & i + 1).Caption = rst.Fields(i).Name
            Me("Text" & i + 1).ColumnHidden = False
            Me("Text" & i + 1).ColumnWidth = 1500
        End If
    Next
    For i = i + 1 To 30
        Me("Text" & i).ColumnHidden = True
    Next
    Set rst = Nothing

In this case I planed for a max of thirty Fields.

However if you want the fields to be updateable then you will need to Insert
the results of your crosstab into a temp table, and point your form to the
temp table, update the field and label names as above.

The really Nasty part is updating your data with any fields that changed
during this edit process. You'll have to write some code that does this.
Obviously the code might be rather complex and slow executing depending on
the source for the Crosstab. Typically I add a Dirty field to the temp
table that I set true whenever any field on the row was edited . That way
if the user updated only one field I have to update only one row of fields.
This not for the feint of heart.

Ron W

"Alex" <anonymous@discussions.microsoft.com> wrote in message
news:2b7b701c4684f$810378d0$a401280a@phx.gbl...
> Is there a way to have data in a form which looks like a
> Pivot Table. e.g. have studentName field as column
> heading, have question field as row heading, then have
> answer field as data. I can see this data in Pivot Table
> view but cannot amend it. Can Access create forms like
> this?
>
> Alex.