Refresh form or report with dynamic headings

[davper]

Win XP
Access 2003


I am trying to build a complex form where a user can view and change the details of a Contract.

The form has a tab control that contains different sections of of the contract to keep the screen size down.

On each tab, there is a form based on a crosstab query that displays each point and the amount by year. I will use an onclick event to bring up a form where the user can add/delete/change the details. So my only concern is getting the data to display correctly

So depending on the start date of the contract, the displayed years will vary.

I have been able to dynamicly run the query to use a subform setting the column headings at runtime. My problem is within the form itself.



I need to add/rename controls as each contract is viewed. I know I can do it at runtime, but only if the form is in design mode.

Is it possible to send a subform only into design mode to make these changes and then redisplay as the subform?

Would a subreport be a better choice?

how would you do it?


Please and Thank You

[davper]

After much research and trial and error, I was able to solve my issue.

I set the ControlSource of the subform container to nothing, edited the subform, then reset the ControlSource.

Private Sub Form_Current()
    Me.sfm_ClinEff.SourceObject = ""
    Call CreateClinicalEfforts(Me.CID)
    Call ChangeClinEffortForm(Me.CID)
    Me.sfm_ClinEff.SourceObject = "frm_Clinical_Efforts_by_Year"
    Me.Refresh
End Sub

Sub ChangeClinEffortForm(lngCID As Long)
    DoCmd.SetWarnings False
    Dim dbCurr As DAO.Database
    Dim rsCurr As DAO.Recordset
    Dim frm As Form
    Dim NewLabel As Control
    Dim NewTextBox As Control
    Dim X As Integer
    
    Set dbCurr = CurrentDb()

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False
    
    'Open form in design mode and delete all controls
    DoCmd.OpenForm "frm_Clinical_Efforts_by_Year", acDesign
    Set frm = Forms("frm_Clinical_Efforts_by_Year")
    Do Until frm.Controls.Count = 0
        DeleteControl frm.Name, frm.Controls(0).Name
    Loop

    'Create form controls
    Set rsCurr = dbCurr.OpenRecordset("SELECT DISTINCT qry_Clinical_Effort_by_Year.Fiscal_Year " & _
    "FROM qry_Clinical_Effort_by_Year " & _
    "WHERE qry_Clinical_Effort_by_Year.CID=" & lngCID)
    
    Set NewTextBox = CreateControl(frm.Name, acTextBox, acDetail)
    With NewTextBox
        .Name = "txt_ClinEffort"
        .ControlSource = "Clinical_Effort"
        .Top = 180
        .Left = 1620
        .Width = 4320
        .Height = 360
        .ColumnWidth = 4320
    End With
    
    Set NewLabel = CreateControl(frm.Name, acLabel, , NewTextBox.Name)
    With NewLabel
        .Name = "lbl_ClinEffort" & strHeadings
        .Caption = "Clinical Effort"
        .Top = 180 + (450 * X)
        .Left = 180
        .Width = 1880
        .Height = 360
    End With
    
    'Creates label and text box for each year
    X = 1
    Do While rsCurr.EOF = False
        strHeadings = rsCurr.Fields(0).Value
        
        Set NewTextBox = CreateControl(frm.Name, acTextBox, acDetail)
        With NewTextBox
            .Name = "txt_" & strHeadings
            .ControlSource = strHeadings
            .Top = 180 + (450 * X)
            .Left = 1620
            .Width = 1440
            .Height = 360
            .ColumnWidth = 1080
        End With

        
        Set NewLabel = CreateControl(frm.Name, acLabel, , NewTextBox.Name)
        With NewLabel
            .Name = "lbl_" & strHeadings
            .Caption = "FY " & strHeadings
            .Top = 180 + (450 * X)
            .Left = 180
            .Width = 1440
            .Height = 360
        End With
        
        rsCurr.MoveNext
        X = X + 1
    Loop
    
    'Destroy instances
    rsCurr.Close
    Set rsCurr = Nothing
    dbCurr.Close
    Set dbCurr = Nothing

    
    'save and close form
    DoCmd.Save acForm, frm.Name
    DoCmd.Close acForm, frm.Name
    DoCmd.SetWarnings True

End Sub