pivot query as form recordsource

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I use a pivot query as recordsource of a continuous
form. Row items are time periods, Column items are
product categories. Values are selling incomes.
In the form I have a field for every different category
(fieldname=categoryname).
I can select different periods but if for a certain
period there are no product of a certain category then
the corresponding field value is set to '#value?'

Is there a way to avoid this?

Thank you
Alex
 
YES

You need to dynamically Hide/Unhide your textboxes 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 named label1,
label2, label3,... Create as many text boxes and labels as you think you
will *EVER* need.

When you need the form open it and set its Record Source to a 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
 
Back
Top