We help IT Professionals succeed at work.

Renaming subform label in VBA

deedub84 asked
Last Modified: 2013-11-28
Hi Experts,

I'm stumped on a subform control reference issue.

I am creating a budgeting database that will budget projects that may not align with calendar years (ie the first month of a project year could be any month).  The user chooses the expected start month when the budget is created, but that can change.

I have a form that allows the user to select the project and a version of the budget.  On the form is tabcontrol with up to 10 tabs, representing each budget year; on each tab is a subform, whose source object is one of 10 subforms.  Depending on the length of the project, I hide the tabs that are irrelevant.

I would like to change the name of the captions for each of the month fields on the subform to correspond with the apppropriate calendar period.  So that if a project year, month1 is Sept 2007, the caption reads 200709.  I have a function that creates the text.

My problem is that the subform reference isn't working...  I get an error saying that Access can't find the form.

Here is my module:

Function CreateBudgetTab()

Dim strFormName As String, strQueryName As String
DoCmd.Echo False, "Getting Budget Information ..."
Dim strType As String
Dim strSubformName As String
Dim strMonthLabel As String
Dim OffsetMonths As Integer
Dim strCaption As String

With Forms!frmprojectbudget
        Debug.Print .cmdLevel.Caption
        Select Case .cmdLevel.Caption
            Case "GL Acct Level"
                strType = "GrantCat"
            Case "AcctCat Level"
                strType = "GLAcct"
            Case "GrantCat Level"
                strType = "AcctCat"
        End Select
        If Not IsNull(.cboProject) And Not IsNull(.cboVersion) And Not IsNull(.cboStartYear) And Not IsNull(cboStartMonth) And Not IsNull(strType) Then
        For x = 0 To 9
            If x >= .txtTotalYears Then
                .tabctlYears.Pages(x).Visible = False
                strFormName = "sfrmPage" & x + 1
'                .Form(strFormName).SourceObject = "sfrmBlank"
                .tabctlYears.Pages(x).Visible = True

                        For z = 1 To 12
                            strMonthLabel = "Month" & z & "_Label"
                            OffsetMonths = x * 12
                            strCaption = CalcFiscPer(CDbl(.cboStartYear), CDbl(.cboStartMonth), x + OffsetMonths)
 Problem Here --->      !Form(strFormName).Controls(strMonthLabel).Caption = strCaption
                        Next z
                    .Form(strFormName).SourceObject = strSubformName
            End If
        Next x
            MsgBox "Please make full selections"
            .Visible = True
            Exit Function
        End If
        .cmdExtData.Visible = True
        .cmdShow.Visible = True
        .Visible = True
    End With

DoCmd.Echo True
End Function


Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

See this link for the ultimate reference in form/subform ... report/subreport systax


Database Architect / Application Developer
Top Expert 2007
This one is on us!
(Get your first solution completely free - no credit card required)


The offending line needed to be
    .Form.Controls(strFormName).Controls(strMonthLabel).Caption = strCaption
Thx for your help.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

You are welcome.  That reference will come in handy ... covers just about all cases.


Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.