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