Pro
I have had to do this kind of thing *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 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. Then you can update the field and label names as shown 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.
I typically use two temp tables, one to hold the data, and another to
describe it.
In the data table for your data I'd create something like:
CREATE TABLE [tblTemp] (
[TempID] [int] IDENTITY (1, 1) NOT NULL ,
[Dirty] [bit] NOT NULL CONSTRAINT [DF_tblTemp_Dirty] DEFAULT (0),
[CurUserID] [int] NOT NULL ,
[CustID] [int] NULL ,
[CustName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Temp3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TempN....] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Where you have as many Temp columns as you think you'll EVER need. If the
database is single user you do not need the CurUserID field. For the table
that describes the data you'll need something like this:
CREATE TABLE [tblTempDataStruct] (
[ColumnDataID] [int] IDENTITY (1, 1) NOT NULL ,
[CurUserID] [int] NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Where the Field Name would contain the Name of the Field that holds the data
(Temp1, Temp2, Etc.) and Display Name would be the text that you displayed
in the attached label for the control on your form for the column (Color,
Size, Quantity in your world).
You may also have to add a few more fields in tblDataStruct to make it
easier for you to mung the edited data back into your normalized data
structure, or to limit the range of values you want to allow the users to
enter. On occasion I have used a column in the DataStruct table to hold the
sql statement to be used as the row source for the combo box on the form,
and another to tell the form what kind of control should be used (textbox,
combo, checkbox, etc). I have found this format is extensible and flexible
enough to cover almost anything you will want to do.
In my experience the toughest part of this process is re-normalizing the
edited data. You'll be writing code the opens and loops through a recordset
of the temp tables data, looking for the record from which it came and
updating that record or creating a new record if there was no original
record (as would be if there were holes (Nulls) in your matrix.
The dirty column will allow this routine to determine if any change was made
on the row and allow you to skip the row when not set. Of course your form
will be responsible to set this flag on the afterupdate event. Make sure
that the user can not add new records or delete existing records and you
should be good to go.
It's not really that bad, but there are a lot of steps, and therefore places
for things to go horribly wrong.
Ron W
