Yes! I have had to do this *many* times. If you can live with a read only
datasheet 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 wan the fields to be updateable then you will need to Insert
the results of your crosstab into a temp table, point your form to the temp
table, update the field and label names as above.
The really Nasty part is updating your data with the 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
what was the source for the Crosstab. Typically I add a Dirty field to the
temp table that I mark 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
