Having trouble referencing a variable with a string&variable combo

[raynman1972]

Hello,



I have some values that I am going to use throughout my code, so I have made them Const. My issue is that in my code I need to refer to these Const values using a string and variable combination (e.g. Eval("WeekVar", x)), but I keep getting number of argument errors. I have this code set up this way so that I can loop through a For loop and refer to different Const values. Is there a simple fix?

Const WeekVar0 As String = "Week4"

Sub Something()

Misc Code...

For x = 0 To 8
    DateHolder = cboWeekBeg.Value + WeekDelta
    Me.Controls("txt_MAIN_" & Eval("WeekVar", x)).SetFocus
    Me.Controls("txt_MAIN_" & Eval("WeekVar", x)).Value = DateHolder
    WeekDelta = WeekDelta + 7
Next x

Misc Code...

End Sub

[rpeare]

have you tried:

dim sControlName

For x = 0 To 8     
    DateHolder = cboWeekBeg.Value + WeekDelta
    sControlName = "txt_main" & x     
    Me.Controls(scontrolname).SetFocus     
    Me.Controls(scontrolname).Value = DateHolder     
    WeekDelta = WeekDelta + 7 
Next x

I'm assuming your controls are named txt_main_0 through txt_main_8

you probably don't want to set focus on each individual control, doesn't really make sense to do it on anything but the first or last control depending on where you want to be.


this was my test code and it worked:

Dim scontrolname  As String

For x = 0 To 2
    scontrolname = "Text" & x
    Me.Controls(scontrolname) = "TESTER"
Next x

[raynman1972]

I have done that kind of thing for my other variable references to userform controls and I have that part down. My issue is that I have a situation with a large grid of textboxes that I need to loop through repeatedly and I have named this range in such a fashion that I can grow the range by additional weeks in either direction of the current week (Week0) without having to renumber all of the textboxes.

Const WeekVar0 As String = "Week4"
Const WeekVar1 As String = "Week3"
Const WeekVar2 As String = "Week2"
Const WeekVar3 As String = "Week1"
Const WeekVar4 As String = "Week0"
Const WeekVar5 As String = "WeekPlus1"
Const WeekVar6 As String = "WeekPlus2"
Const WeekVar7 As String = "WeekPlus3"
Const WeekVar8 As String = "WeekPlus4"

[rpeare]

So are you saying you want to be able to handle negative numbers as well as positive? and project/regress by an undetermined number of weeks? Or are you always going to have 9 weeks but you may want to take the current record and project 8 weeks into the future OR 8 weeks into the past

Are these unbound text boxes where you're just trying to do some simple analysis or estimates and the data is basically throw away other than your starting (seed) values?

Because it looks like you're just entering calculated values so if the fields you're trying to fill are, in fact, unbound controls I would approach this a different way entirely.

Can you give an example of what your cboWeekBeg value is and what you want your final values of weekvar0 through weekvar8 to be?