As long as you can live with a read only form you can do this by dynamically
Hiding/Unhiding textboxes on your form (as a Datasheet) based on the columns
returned by your query as you open/re-query the form.
Create a form with a bunch of text boxes named something clever like text1,
text2, text3,... The text boxes will need attached labels which could be
named label1, label2, label3,... Create as many text boxes and labels as
you think you will *EVER* need. Make the forms DefaultView a Datasheet.
When you need the form open it and set its Record Source to your canned
CrossTab query or a sql statement that you generate on the fly.
In the OnLoad or OnCurrent 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 associated 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. Done this way the data in
the form is Read Only. As long as you can live with this you are good to
go. If you want to allow the users to update the data then you will have to
do a LOT more work, using at least one temp table (I have typically used two
temp tables one for the data and the other for the meta data). You will be
completely responsible for writing the code that inserts/updates the data in
your normalized tables from any changed data in your form.
Ron W
