Forms path Advice

[JayAndy]

Hi

Im trying to make a loop that refers to my text boxs. Im Using Me!Saturday1.Value

Does anyone know of a way where l can just change the number on the Me. using a +1 somehow.

For Example Me.Saturday1.value for change to Me.Saturday2.Value
the text box names are below.

Saturday1
Saturday2
Saturday3
Saturday4
Saturday5

Many Thanks

 

[Grumm]

You can do something like this :

Dim ctl As Control
Dim i as Integer
Dim sControlName as String

sControlName = "Saterday"
i = 1
For Each ctl in Me.Controls

   If ctl.Name = sControlName & Cstr(i) Then
        debug.print ctl.Value
  End If
i = i + 1
Next

The code will loop over all the controls in the form. So you need an extra IF to check that you are now on a valid one. Then you can do what you want.
I hope this will help you.

 

[JayAndy]

Thanks will give it a go.

You can do something like this :

Dim ctl As Control
Dim i as Integer
Dim sControlName as String

sControlName = "Saterday"
i = 1
For Each ctl in Me.Controls

   If ctl.Name = sControlName & Cstr(i) Then
        debug.print ctl.Value
  End If
i = i + 1
Next

The code will loop over all the controls in the form. So you need an extra IF to check that you are now on a valid one. Then you can do what you want.
I hope this will help you.

 

[Grumm]

I made a small change so that the order doesn't matter :

Dim ctl As Control
Dim i As Integer
Dim sControlName As String

sControlName = "Saterday"
i = 1
For Each ctl In Me.Controls

   If ctl.Name = sControlName & CStr(i) Then
        Debug.Print ctl.Value
        i = i + 1
  End If

Next

Maybe there are beter ways to do this. I will try to make a function that will work every time.

Here is a more compact one :

Private Sub Button1_Click()
Dim ctl As Control
Dim SearchArray() As Variant

SearchArray = Array("Saterday1", "Saterday2", "Saterday3", "Saterday4", "Saterday5")

For Each ctl In Me.Controls

   If IsInArray(ctl.Name, SearchArray) Then
        Debug.Print ctl.Value
  End If

Next
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

You still need to create the array with all the valid control names.
But now it doesn't matter of Saterday1 is after Saterday2.

Much saver than the first codes