Using the i index in a form filter

[CD Tom]

Here's the code I have without the i as the index.

Me.Filter = "PageNumber1 = " & vpagenumbers & " and Range1 = '" & VRange & "' and Category = '" & VCatSelection & "'"

What I would like to do is change the pageNumber and the range so instead of having a 1 or 2 etc the i would change with the page selection. This way I wouldn't have to have a different line for each page and range.

[CD Tom]

OK I figured that one out but now I get an error on the following statement this is the original statement

me.rwx3.move 3000

I want to use the i index and I tried thie

me.RWX" & i & ".move 3000

and get an error Expected: end of statement. Is there a way to use the i index in this type of a statement?
Thanks for your help AGAIN.

[MikeTheBike]

Hi

Without seeing more of the code it is difficult to know what you are trying to achieve.
I assume that 'rwx3' is one of a number of recorsets.
If so you cannot refer to an object variable as a string, which is what you as trying to do by concatenating a variable in the name.

I also assume that this is in a loop, with i as the loop counter.

Again if this is the case then you could try a construct similar to this

    Select Case i
        Case Is = 1
            Me.rwx3.Move 3000
        Case Is = 2
            Me.rwx4.Move 3000
        'etc.
    End Select

This of course could be completely nonsence!

MTB

[jforbes]

I also am a little confused on what you are attempting to do. If you are looking to refer to a control based off of a variable, you can use the Forms Controls() collection. Here is some code from a project I worked on recently for an example:

Private Sub clearSelect()
    Dim iCount As Integer
    Dim iCount2 As Integer
    For iCount = 1 To 5
        For iCount2 = 1 To 7
            iHours(iCount, iCount2) = 0
            Me.Controls("txtTimeSlot" & iCount & "_" & iCount2).Value = ""
            Me.Controls("txtTimeSlot" & iCount & "_" & iCount2).BackColor = vbWhite
            Me.Controls("txtTimeSlot" & iCount & "_" & iCount2).BorderColor = nColorAccessTheme4
        Next iCount2
    Next iCount
End Sub

[CD Tom]

I guess I was a little lax in explaining what I was trying to do. I have a single form used to enter data, the data comes from sheets printer by a round number so Round1 could have Page1, Page2 etc. The form has Fields RWX1 RWX2 RWX3 etc and RTime1, RTime2, Rtime3 these are associated with the round number. They are all set to Visible = false, when the round is selected the code turns the appropriate field to true and moves it to a position on the form. Here's the original code for say round 3
There can be up to 24 rounds. I have that many set up in the program but that's a lot of code and I was trying to code down.

    If VrndNumber = 3 Then
        Me.RWX3.Visible = True
        Me.RTime3.Visible = True
        Me.RWX1.Visible = False
        Me.RTime1.Visible = False
        Me.RWX3.SetFocus
        Me.Filter = "PageNumber3 = " & vpagenumbers & " and Range3 = '" & VRange & "' and " & VTF & ""
        Me.FilterOn = True
        Me.OrderBy = "Range3, RandLaneNumber3, LaneNumber3"
        Me.OrderByOn = True
        Me.VPageNumber.ControlSource = "PageNumber3"
        Me.Range.ControlSource = "Range3"
        Me.RWX3.Move 3000
        Me.RTime3.Move 3500
    End If

The program is working fine as is, like I said I'd like to code down on the number of lines.
I hope this makes a little more sense. I'm sure there was a better way to originally set this up but at the time couldn't think of one.
Thanks again for your understanding and help.

[Rabbit]

Use the controls collection that jforbes uses in his code.

[CD Tom]

That worked perfect. Thanks this will eliminate lots of extra code.