Moving Access control arrays to module

[JBG]

I am using the control array workaround in my Access 2002 apps in multiple forms.

 me("txtSomeControl_" & x)

What I want to do is take the code and functionality that doesnt belong in the forms and move the code to modules.

So, I try to do this with a call:

 s_SomeSubroutine me

and receive the call like this, then attempt to use a control array:

 public sub s_SomeSubroutine(byval frmForm as Form)
       dim x as integer
       
             for x = 0 to 4 
                  frm("txtSomeControl_" & x) = x
             next x
end sub

Of course the code errors out as the control array trick seems to work only within the form using "me".

Does anyone have some sample code that shows how to use a control array within a module after passing the module a reference to the form?

Much obliged,

JBG

 

[JBG]

oops...this is what I meant to write for the code in the sample subroutine (I meant to write "frmForm" instead of "frm" in the for loop):

public sub s_SomeSubroutine(byval frmForm as Form)
       dim x as integer
       
             for x = 0 to 4 
                  frmForm("txtSomeControl_" & x) = x
             next x
end sub

Still the same question though: how do I use a control array within a module?

Thanks!

JBG

 

[sfm6s524]

Looks like the sub should work, but call it by s_SomeSubroutine Forms![frmSomeForm]. If the controls are Textboxes, you will get an error unless they are unbound. If they are bound, you'll need to set the ControlSource, like frmForm("txtSomeControl_" & x).ControlSource = "=" & x. And the form has to be open too.

 

[JBG]

word up. Ill try it...

Thanks for the reply

Jeff

 

[RoyVidar]

Hi!

I might be missing something obvious...

Tried your sample code on both a2k and xp, and only time it bombed was when
1 - control out of scope ("txt_0" didn't exist, "first" control was "txt_1&quot
2 - assigning values conflicting with the datatype/validation rules of the control/field

Are you able to referernce any of the controls?
Immidiate pane: ?frmForm("NameOfAnyExistingControl&quot

Could it be an alternative, if this path doesn't lead to success, to consider using the controls collection? That might be considered a more dynamic approach (even if it doesn't specificly address your question)

If the challenge still persists, try posting current code and error msg.

Roy-Vidar

 

[sfm6s524]

You always have to build in error checking, either catching them and deciding what to do, or ignore them and resume the next commands. Just depends on how big of deal the error will create. I usually use the Controls collections, as you suggest, frequently on apps with 12 months of data, and I want to set the column heading labels to mmm yyyy format for the various years, and perhaps set the color of past months versus future months of a fiscal year. So, how about:

Public Sub s_SomeSubroutine(ByVal frmForm As Form)
Dim x As Integer
Dim Ctrl As Control

On Error Resume Next
For x = 0 To 5
Set Ctrl = frmForm("Text" & x)
If Err.Number <> 2465 Then
Select Case Ctrl.ControlType
Case acTextBox
Ctrl.ControlSource = &quot;=&quot; & x
End Select
End If
Next x
End Sub

As far as checking the controls from the Immediate window, using the frmForm object will only work from within the s_SomeSubroutine sub, so you'd have to set a breakpoint to stop execution and evaluate while sub objects are active. But if you try to reference controls outside of sub from Immediate window, you'll need to use the Forms collections as in Forms![frmNameWhatever]![txtControlNameWhatever].

 

[JBG]

Thanks all for the replies. When I wrote the original post, it was based on an incident a few weeks ago when for some reason I could not use a control array in a module.

Becuase of that incident, starting a few days ago, I began writing an app for a client and &quot;had&quot; to keep all of the functionality that utilized a control array within the form becuase I thought I could not utilize a control array in a module. Rather than trying, again, what I had a few weeks ago (which failed likely for one of the reasons you folksmentioned), I posted the question.

After the first reply, I tried it again using both &quot;Me&quot; in the call as well as forms![frmSomeForm], and both worked fine.

So, no, no one is missing anything, I just had a doh! moment and should have tried the control array again before posting my question.

Again, thanks for the replies.

Jeff