Creating field programmatically doesn't work. Help?

[aristos]

Hello, all.

First, let me thank everyone for the help I've gotten over the last few days. I've made more progress in these few days than I have in weeks.

With that, I've another question for the board:

I'm creating a dynamic report and I need to create field headers based on fields in a query. The query has job number, hours, job name and a field for each person.

Here's the code I've written so far:

Private Sub Report_Open(Cancel As Integer)
    ' Create headers of staff names
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Dim fld As DAO.Field
    Dim rpt As Report
    Dim ctl As Control
    
    Dim i As Integer
    
    Set db = CurrentDb
    Set qry = db.QueryDefs("qryHourProjections_Crosstab")
    
    Set rpt = Reports("rptHourProjections")
    
    i = 1
    For Each fld In qry.Fields
        If (fld.Name <> &quot;JobNumber&quot;) And (fld.Name <> &quot;JobDescription&quot;) And (fld.Name <> &quot;Total of Total of Hours&quot;) Then
            ' For each staff field, create a label
            'Set ctl = CreateReportControl(rpt.Name, acLabel, acPageHeader, , fld.Name, 20 + 100 * (i - 1), 40)
            Set ctl = CreateReportControl(rpt.Name, acLabel, acPageHeader, , fld.Name, 20 + 100 * (i - 1), 40)
        End If
    Next
End Sub

When I run it, I get an error that says I must be in design mode to add or remove controls. I thought that it could be done programmatically.

Can this only be done with pure code and not an OnOpen event?

Any help?

thanks in advance...

Mike

 

[danvlas]

The idea is that you cannot ADD controls, but you can modify the caption, color, source and so on for EXISTING ones.
I can see that you have a crosstab query as recordsource. Try to determine the MAXIMUM number of columns the query can produce, then place a corresponding number of UNBOUND controls in your report and set their Visible property to No.

Here is the example from a dynamic crosstab report in Solutions.mdb (it comes from old dear Access 2.0, but it works fine in Access97 - I have been using it for a long time now). For more info, open the Solutions.mdb example and look at 'Create advanced reports-Create a crosstab report with dynamic column headings'

'============report code here====================
Option Compare Database 'Use database order for string comparisons
Option Explicit

' Constant for maximum number of columns your query would
' create plus 1 for a Totals column.
Const TOTCOLS = 11

' Variables for database object and recordset.
Dim RptDB As Database
Dim RptRS As Recordset

' Variables for number of columns and row and report totals.
Dim IColCnt As Integer
Dim RgColTot(1 To TOTCOLS) As Long
Dim RptTotal As Long

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)

' Place values in text boxes and hide unused text boxes.
'
Dim i As Integer
' Verify that not at end of recordset.
If Not RptRS.EOF Then
' If FormatCount is 1, place values from recordsSet into text boxes
' in detail section.
If Me.FormatCount = 1 Then
For i = 1 To IColCnt
' Convert null values to 0.
Me("Col" + Format$(i)) = xtabCnulls(RptRS(i - 1))
Next i

' Hide unused text boxes in detail section.
For i = IColCnt + 2 To TOTCOLS
Me("Col" + Format$(i)).Visible = False
Next i

' Move to next record in recordset.
RptRS.MoveNext
End If
End If

End Sub

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)

Dim i As Integer
Dim RowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
RowTotal = 0

For i = 3 To IColCnt
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in detail section.
RowTotal = RowTotal + Me("Col" + Format$(i))
' Add crosstab value to total for current column.
RgColTot(i) = RgColTot(i) + Me("Col" + Format$(i))
Next i

' Place row total in text box in detail section.
Me("Col" + Format$(IColCnt + 1)) = RowTotal
' Add row total for current row to grand total.
RptTotal = RptTotal + RowTotal
End If
End Sub

Private Sub Detail1_Retreat()

' Always back up to previous record when detail section retreats.
RptRS.MovePrevious

End Sub

Private Sub InitVars()

Dim i As Integer

' Initialize RptTotal variable.
RptTotal = 0

' Initialize array that stores column totals.
For i = 1 To TOTCOLS
RgColTot(i) = 0
Next i

End Sub

Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim i As Integer

' Put column headings into text boxes in page header.
For i = 1 To IColCnt
Me("Head" + Format$(i)) = RptRS(i - 1).Name
Next i

' Make next available text box Totals heading.
Me("Head" + Format$(IColCnt + 1)) = "Totaluri"

' Hide unused text boxes in page header.
For i = (IColCnt + 2) To TOTCOLS
Me("Head" + Format$(i)).Visible = False
Next i
End Sub


Private Sub Report_Close()

' Close recordset.
RptRS.Close

End Sub


Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' Employee Sales Dialog Box form.
'
Dim i As Integer
Dim MyQuery As QueryDef


' Set database variable to current database.
Set RptDB = DBEngine.Workspaces(0).Databases(0)

' Open QueryDef.
Set MyQuery = RptDB.QueryDefs("YourCrosstabQuery&quot
' Set parameters for query based on values entered in Employee Sales Dialog Box form.
'MyQuery.Parameters("Forms![Employee Sales Dialog Box]![Beginning Date]&quot = Forms![Employee Sales Dialog Box]![Beginning Date]
'MyQuery.Parameters("Forms![Employee Sales Dialog Box]![Ending Date]&quot = Forms![Employee Sales Dialog Box]![Ending Date]

' Open Recordset.
Set RptRS = MyQuery.OpenRecordset()

' If no records match criteria, display message,
' close recordset, and cancel Open event.
If RptRS.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
RptRS.Close
Cancel = True
Exit Sub
End If

' Set a variable to hold number of columns in crosstab query.
IColCnt = RptRS.Fields.Count

End Sub

Private Sub ReportFooter4_Print(Cancel As Integer, PrintCount As Integer)

Dim i As Integer

' Place column totals in text boxes in report footer.
' Start at Column 2 (first text box with crosstab value).
For i = 2 To IColCnt
Me("Tot" + Format$(i)) = RgColTot(i)
Next i

' Place grand total in text box in report footer.
Me("Tot" + Format$(IColCnt + 1)) = RptTotal

' Hide unused text boxes in report footer.
For i = IColCnt + 2 To TOTCOLS
Me("Tot" + Format$(i)).Visible = False
Next i
End Sub

Private Sub ReportHeader3_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at beginning of report
' or when report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
RptRS.MoveFirst

'Initialize variables.
InitVars

End Sub

Private Function xtabCnulls(MyVal As Variant)

' Test if a value is null.
If IsNull(MyVal) Then
' If MyVal is null, set MyVal to 0.
xtabCnulls = 0
Else
' Otherwise, return MyVal.
xtabCnulls = MyVal
End If

End Function
'============end of report code=================


Good luck,

Dan

 

[aristos]

Yes, that's what I'd figured I'd have to do.

BUT....

I've figured out how to do it. The report itself needs to be created dynamically.

Here's the code for the entire report, which I've tested and it works fine!

Sub HourProjection()

    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Dim fld As DAO.Field
    
    Dim ctl As Control
    
    Dim rpt As Report
    
    Dim i As Integer
    
    Dim intLeft As Integer
    Dim Width As Integer
    
    intLeft = 60
        
    Set db = CurrentDb
    Set qry = db.QueryDefs("qryHourProjections_Crosstab")
    Set rpt = CreateReport("", "")
    
    rpt.RecordSource = "qryHourProjections_Crosstab"
        
    rpt.Caption = "Hourly projections"
    
    ' Create HourlyProjection label
    Set ctl = CreateReportControl(rpt.Name, acLabel, acPageHeader)
    With ctl
        .Height = 300
        .Width = 2000
        .Top = 100
        .Left = intLeft
        intLeft = intLeft + .Width + 10
        .Caption = "Hourly Projections"
        .FontName = "Arial"
        .FontSize = "12"
    End With
    
    intLeft = 10
    
    ' Create the JobName label
    Set ctl = CreateReportControl(rpt.Name, acLabel, acPageHeader)
    With ctl
        .Height = 400
        .Width = 600
        .Top = 500
        .Left = intLeft
        .Caption = "Job" & vbCrLf & "Number"
        .FontName = "Arial"
        .FontSize = "8"
    End With
    
    'Create the JobName field
    Set ctl = CreateReportControl(rpt.Name, acTextBox, acDetail)
    With ctl
        .Height = 300
        .Width = 600
        .Top = 0
        .Left = intLeft
        .ControlSource = "JobNumber"
        .FontName = "Arial"
        .FontSize = "8"
    End With
            
    intLeft = intLeft + 600 + 10
    
    ' Create JobDescription label
    Set ctl = CreateReportControl(rpt.Name, acLabel, acPageHeader)
    With ctl
        .Height = 400
        .Width = 2000
        .Top = 500
        .Left = intLeft
        .Caption = "Job Description"
        .FontName = "Arial"
        .FontSize = "8"
    End With
    
    ' Create JobDescription field
    Set ctl = CreateReportControl(rpt.Name, acTextBox, acDetail)
    With ctl
        .Height = 300
        .Width = 2000
        .Top = 0
        .Left = intLeft
        .ControlSource = "JobDescription"
        .FontName = "Arial"
        .FontSize = "8"
    End With
    
    intLeft = intLeft + 2000 + 10
    
    ' Create the headers for the columns
    
    i = 1
    For Each fld In qry.Fields
        
        If fld.Name <> &quot;JobNumber&quot; And fld.Name <> &quot;JobDescription&quot; And fld.Name <> &quot;Total of Total of Hours&quot; Then
            
            ' Create the header for the staff person
            Set ctl = CreateReportControl(rpt.Name, acLabel, acPageHeader)
            With ctl
                .Caption = fld.Name
                .Height = 400
                .Width = 1000
                .Left = intLeft
                .Top = 500
            End With
            
            ' Create the field for the staff person
            Set ctl = CreateReportControl(rpt.Name, acTextBox, acDetail)
            With ctl
                .ControlSource = fld.Name
                .Height = 300
                .Width = 1000
                .Left = intLeft
                .Top = 0
                .TextAlign = 2
                intLeft = intLeft + .Width
            End With
            intLeft = intLeft + Width + 10
            i = i + 1
        End If
        

    Next
                
                
    rpt.Section(acDetail).Height = 300
    DoCmd.OpenReport rpt.Name, acViewPreview
    
End Sub