VBA code to change captions in a subform

[rockyjr]

I'm sorry in advance if someone ask this question before. I couldnt find anything on this...

I have a form (Form1) and a in it, I have 2 subforms datasheet (Subform1 and Subform2). I have ID, First Name, Last Name in each as headers.

I need to:

1) Find the subforms
2) Loop through the subforms and get all the labels
3) Change the captions

The reason for this is because I need to have the forms bilingual. I do have a function that will translate the caption (called TranslateThisWord). I just cant figure out a way to loop through the subforms and find all the labels.

Thanks in advanced....

Luc

 

[pr2-eugin]

How good are you with VBA? This will involve a little bit of VB coding.

1. Find the subform.
2. Loop through the controls in the Subform -> Identify the labels
3. Use the Caption property to pass it through the function and the same to change it.

 

[rockyjr]

Pretty good... but for some reason.. I just cant find a way to loop through....

I know this changes the caption but I dont want to it to be static.
Forms![Form1].[Subform1]!ID_Label.Caption = "whatever"

Luc

 

[pr2-eugin]

Pretty good... but for some reason.. I just cant find a way to loop through....

That is a good news. It should be something like.

Private Sub translateButton_Click()
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acLabel Then _
            ctrl.Caption = TranslateThis(ctrl.Caption)
    Next
End Sub

I know this changes the caption but I dont want to it to be static.

Since you are using a function, this will not be static.

 

[rockyjr]

Ok, that's what I had for the form... I even went a little step further... Since I want to reuse the same code over and over i was thinking of passing the form name to a function, this is how I did it...

On the main form I have this:

Private Sub Form_Load()
'Check User Language Preferrence. Translate if Needed.
    Dim frmCurrentForm As Access.Form
    Me.Visible = True
    Set frmCurrentForm = Screen.ActiveForm
    Call Translate(frmCurrentForm)
End sub

It calls the Translate function....

Public Function Translate(sCurrentForm As Access.Form)
For Each ctl In sCurrentForm.Controls '--> buttons and labels
    If ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton Then
        ctl.Caption = TranslateThisWord(ctl.Caption)
    End If
End Function

This works well for the form itself but doesnt change anything in the subform.

I tried to add the code in the Form_OnLoad Sub of the subform but that didnt work either.

 

[pr2-eugin]

You need to get a little bit deeper there, I just gave a starting point. You have to understand that SubForm is also a control in a Main form, so you have to identify if the control is a SubForm, then get those controls and loop through again, a For inside a For. Something like (air coded)

[COLOR=DarkRed][B]Public Sub[/B][/COLOR] Translate(sCurrentForm As Access.Form)
    [COLOR=Magenta][B]Dim ctl As Control, subCtrl As Control[/B][/COLOR]
    For Each ctl In sCurrentForm.Controls[COLOR=Green] '--> buttons and labels[/COLOR]
       [COLOR=Red] Select Case ctl.ControlType[/COLOR]
            [COLOR=Red]Case[/COLOR] acLabel, acCommandButton
                ctl.Caption = TranslateThisWord(ctl.Caption)
            [COLOR=Red]Case acSubform[/COLOR]
                [COLOR=Blue]For Each subCtrl In ctl.Form.Controls
                    If subCtrl.ControlType = acLabel Or subCtrl.ControlType = acCommandButton Then _
                        subCtrl.Caption = TranslateThisWord(subCtrl.Caption)
                Next[/COLOR]
        [COLOR=Red]End Select[/COLOR]
    Next
[COLOR=DarkRed][B]End Sub[/B][/COLOR]

Few things to note : Change it to Sub, Declare all variables, Close all Loops/statements.

 

[rockyjr]

Does it matter if the subform is a datasheet???

The header names dont change. :banghead:

 

[pr2-eugin]

Should not matter. I had a quick check. You did use the code in Post#6 right?

 

[rockyjr]

Ya I think so... I will recreate the database on a small scale and show you what I'm doing wrong.... yep... I'm sure I'm doing something wrong... lol...

 

[rockyjr]

Oh man!!..

I got it to work on the small scale one... hummm... I guess I'll have to check what I'm doing wrong in the other one....

Thanks pr2-eugin,

Learned a lot!

Luc

 

[pr2-eugin]

Not a problem ! I am sure you will be able to figure it out in no time. Post back if you have found/not found a solution. Good Luck !

 

[rockyjr]

I guess it works the same... but, for navigation controls... is it the same?

I went here but they dont talk about the navigation control...
http://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx

 

[pr2-eugin]

I guess it works the same... but, for navigation controls... is it the same?

Unfortunately I have no experience with Navigation controls. So it is hard to give a straight forward answer.