Re: PivotTable?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Alex (anonymous_at_discussions.microsoft.com)
Date: 07/13/04 

Date: Mon, 12 Jul 2004 19:44:42 -0700

I need the resulting form to be editable. Will this work?

Alex.
>-----Original Message-----
>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.
>
>
>.
>

Quantcast