Help on variable to call form field

[millercj]

I'm trying to get data from one of 5 form fields through a loop but i'm having trouble accessing the field with the variable

Dim Count As Integer
Count = [Forms]![Comparison]![Count]
Dim Run As Integer
Run = 0
Do Until Run = Count
Run=Run+1
    Select Case [Forms]![Comparison]!Run
...

Loop

"Select Case [Forms]![Comparison]!Run" isn't isn't working how do i use a variable in a field call?

 

[KenHigg]

This smells like a normalization issue. Can provide a little more info on what you're trying to do and maybe a screen shot of the form?

 

[Dennisk]

You assign a variable to the value in the Control or use the value in an expression, The menthod you are using is T SQL and will not work in Access

MyVariable = Forms!Comparison!Run

IF Forms!Comparison!Run = something Then
..........................
End IF

 

[millercj]

None of the data that runs in the form uses any tables. I just need to know how to call a variable field name.

Attached is a screen shot. The first combo box is named "1" and the second, "2". I want the loop increment variable to be the field name

for example:
Select Case [Forms]![Comparison]!Run would equal either Select Case [Forms]![Comparison]!"1" or Select Case [Forms]![Comparison]!"2"

 

[KenHigg]

Instead of:

forms!MyFormName!MyFieldName

You can do something like:

forms!MyFormName("MyFieldName")

Or

Dim strVar as string

strVar = "MyFieldName"

forms!MyFormName(strVar)

Does this help?

 

[millercj]

yes but with one problem...it only works for the first itteration

Dim Count As Integer
Count = [Forms]![Comparison]![Count]
Dim Run As Integer

Run = 0
Do Until Run = Count
    Run = Run + 1
    MsgBox (Run)
    MsgBox (Forms!Comparison(Run))
    Select Case Forms!Comparison(Run)
...
Loop

it runs with run=1 and works great...when it runs where run = 2 (and the field does exist) it says "Object doesn't support this property or method" and highlights both MsgBox (Forms!Comparison(Run)) and Select Case Forms!Comparison(Run)

 

[KenHigg]

This is getting more confusing. I think Forms!Comparison(1) will give you the enumerated control.

Say you have two text boxes on a form and thats all. Text box one, regardless of its name, would be referenced with something like:

Forms!Comparison(0)

And the second text box would be something like:

Forms!Comparison(1)

Etc...

I'm still not sure what your trying to do. Sorry...

 

[millercj]

this is cheating but this is what i did

    If Run = 1 Then
    current = [Forms]![Comparison]![1]
    ElseIf Run = 2 Then
    current = [Forms]![Comparison]![2]
    ElseIf Run = 3 Then
    current = [Forms]![Comparison]![3]
    ElseIf Run = 4 Then
    current = [Forms]![Comparison]![4]
    ElseIf Run = 5 Then
    current = [Forms]![Comparison]![5]
    End If

 

[KenHigg]

I was wondering why you didn't do it that way to begin with. Anyway, glad you have it working...

(In fact I'd replace the 'if' with a 'select' )