Loading subforms using strings

[tdubs]

hi... I have a form containing 120 subforms...

the form will be loaded depending on the user...
i name my subforms in sequence that is: loadA1, loadA2, loadA3,....

to load a form I just use the code: me.loadA3.sourceobject = "FormName"

and then I make a counter to keep track of which forms are loaded and which one to load next.. as they load in sequence..

the question is if its possible to write the above code in a string type or other way to make the code shorter...

For example my code is as follows:

If ACount = 0 Then
    Me.LoadA1.SourceObject = "TEMPCableName"
ElseIf ACount = 1 Then
    Me.LoadA2.SourceObject = "TEMPCableName"
ElseIf ACount = 2 Then
    Me.LoadA3.SourceObject = "TEMPCableName"
ElseIf ACount = 3 Then
    Me.LoadA4.SourceObject = "TEMPCableName"
ElseIf ACount = 4 Then
    Me.LoadA5.SourceObject = "TEMPCableName"
ElseIf ACount = 5 Then
    Me.LoadA6.SourceObject = "TEMPCableName"
ElseIf ACount = 6 Then
    Me.LoadA7.SourceObject = "TEMPCableName"
ElseIf ACount = 7 Then
    Me.LoadA8.SourceObject = "TEMPCableName"
ElseIf ACount = 8 Then
    Me.LoadA9.SourceObject = "TEMPCableName"
ElseIf ACount = 9 Then
    Me.LoadA10.SourceObject = "TEMPCableName"
ElseIf ACount = 10 Then
    Me.LoadA11.SourceObject = "TEMPCableName"
ElseIf ACount = 11 Then
    Me.LoadA12.SourceObject = "TEMPCableName"
ElseIf ACount = 12 Then
    Me.LoadA13.SourceObject = "TEMPCableName"
ElseIf ACount = 13 Then
    Me.LoadA14.SourceObject = "TEMPCableName"
ElseIf ACount = 14 Then
    Me.LoadA15.SourceObject = "TEMPCableName"
ElseIf ACount = 15 Then
    Me.LoadA16.SourceObject = "TEMPCableName"
ElseIf ACount = 16 Then
    Me.LoadA17.SourceObject = "TEMPCableName"
ElseIf ACount = 17 Then
    Me.LoadA18.SourceObject = "TEMPCableName"
ElseIf ACount = 18 Then
    Me.LoadA19.SourceObject = "TEMPCableName"
ElseIf ACount = 19 Then
    Me.LoadA20.SourceObject = "TEMPCableName"
End If

If ACount < 19 Then
    ACount = ACount + 1
ElseIf ACount < 0 Then
ACount = 0
Else
ACount = 20
End If

anyway to make it something like:

"me.loadA" & Acount & ".SourceObject = "TEMPCableName"

??


Thanks in advance
help is much appreciated

 

[Mr.K]

I'm in no way an expert in vba but can't you use:

for i=0 to 19

... and then use the i (or i+1) in your if condition and also in creating the name of the subform

 

[clerics]

i dont know what you re trying to do but it would be easier if you just use only one subform and update your Subform's database base on what user want to see where user click on Main form (Cobobox or anything in main Form)
Something like :
Dim strSQL as String
strSQL = "Select a,b,c From [yourtable] Where [yourtable].ID=" & Form!mainForm!ID
Me.SubForm.RecordSource= "strSQL"
Me.SubForm.requery

But if you also want to do that , let store all subform' s name in a table (that makes you control your database well)