We help IT Professionals succeed at work.

Renaming subform label in VBA

deedub84
deedub84 asked
on
548 Views
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
            Else
                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
       
        Else
            MsgBox "Please make full selections"
            .Visible = True
            Exit Function
        End If
        .cmdExtData.Visible = True
        .cmdShow.Visible = True
       
        .Visible = True
        .Requery
        .Repaint
       
    End With

DoCmd.Echo True
End Function


Thanks!

Deedub84
Comment
Watch Question

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

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

http://www.mvps.org/access/forms/frm0031.htm

mx
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2007

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

mx

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.

OR

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.