where to put event code for dynamic crosstab subform

[ano]

i have a database with 3 tables,
table1 contains items price and date is linked to table2,primary key in form
table2 contains description of item and category
table3 contains date and used is linked to table1,primary key in form as dataview
union query day/week/month contains price per day/week/month in form1 linked to table1
query contains unused items in form2
crossquery contains used per week in form3


only form3 has problem because of growing number of weeks to display in top row
often i get error about ole but i not use ado nor dao just database but i added ado/dao/activex
if i put the dynamic code in form then i debug.print table1 but not controls (how print controls?)
if i put the dynamic code in form3 then i not see any table
the code :
Option Compare Database


Private Sub Form_Open(Cancel As Integer)
Me.Recordset.MoveLast ' ! for table1 rest for table3
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim StrName As String
On Error Resume Next
intColCount = Me.Recordset.Fields.Count
intControlCount = Me.Detail.Controls.Count
Debug.Print "field:" & intColCount & "::" & intControlCount (result "field:4::10)
If intControlCount < intColCount Then
intColCount = intControlCount
End If
For i = 1 To intColCount
StrName = Me.Recordset.Fields(i - 1).Name
Me.Controls("lblPgHdr" & i).Caption = StrName
Me.Controls("tbxData" & i).ControlSource = StrName
Me.Controls("tbxSum" & i).ControlSource = _
"=Sum([" & StrName & "])"
Debug.Print "name:" & StrName (4lines : id week item price)
Next i
For i = intColCount + 1 To intControlCount
Me.Controls("tbxData" & i).Visible = False
Me.Controls("lblPgHdr" & i).Visible = False
Me.Controls("tbxSum" & i).Visible = False
Next i
End Sub

if i know where to put what event and how to print controls that will help a lot already

[Welshgasman]

Please start using code tags for code.
The # icon.

[CJ_London]

Similar threads section at the bottom of this thread

[June7]

Print what controls - textboxes? For every record? Just to the immediate window? Why?

[Gicu]

Your description is somehow lacking clarity but usually to address the variable\growing number of columns associated with a crosstab query you need to use the actual (crosstab) query as the source object of the subform control (on the main form) instead of a form object.

Cheers,

[ano]

Print what controls - textboxes? For every record? Just to the immediate window? Why?

if u do coding as suggested by several guru and it does not work it can help to understand where to look also called debugging

[June7]

Okay, you are printing control name to immediate window after setting label caption and textbox control. Since this result should be viewable on form, what does Debug.Print tell you about the code process?

You want to change form properties of controls on form to reflect field names generated by CROSSTAB query.

As Gicu suggested, would be simpler to just use the query as subform container SourceObject.