Control Arrays

[extraordinare]

I have searched the VB.NET Forums for references towards control arrays in Access 2003 using VBA code. I haven't found what I was looking for. I will try to explain. I am trying to find a way to programmatically loop through a collection of controls on a form. Here is an example of code that I have written so far.

Option Compare Database
Option Explicit
 
Private Sub btnSave_Click()
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strMonth, strYear, strMonthID, strQuery As String
Dim ctrl As Control
'Checks if the Month field is selected
If cboMonth.ListIndex = -1 Then
  MsgBox "Please select a Month ", vbOKOnly
  cboMonth.SetFocus
  Exit Sub
End If
'Checks if the Year field is selected
If cboYear.ListIndex = -1 Then
  MsgBox "Please select a Year ", vbOKOnly
  cboYear.SetFocus
  Exit Sub
End If
strMonth = CStr(IIf(cboMonth.ListIndex < 10, 0 & cboMonth.ListIndex, cboMonth.ListIndex))
strYear = CStr(cboYear.Value)
'Combines the Month and Year into one variable
strMonthID = strMonth & strYear
'Checks each TextBox to determine whether or not it meets certain criteria
'will exit the subroutine if any conditions test true
 
For Each ctrl In Me.Controls
  If TypeOf ctrl Is TextBox Then
    MsgBox "Cool "
  End If
Next ctrl
 
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection

End Sub

The problem is the For Loop. I can loop through multiple controls but cannot access the Textbox control properties. Like the Name property of the Textbox. If someone has a creative way for achieving this please let me know because it will take to much to write each control individually.

 

[JohnH]

For Each ctrl In Me.Controls
  If TypeOf ctrl Is TextBox Then
    MsgBox(directcast(ctrl,Textbox).Text)
  End If
Next ctrl

 

[extraordinare]

Control Arrays Revisited

I had attempted to use the code you provided without success. I'm using VBA not VB.NET. I'm not sure if there is any other reason why this subroutine is not working.

 

[JohnH]

well, this is a VB.Net forum...
I haven't got access to Access or any other product with VBA, but I found code like this in another forum:

For Each ctrl In Me.Controls
  If ctrl.ControlType = acTextBox 
    MsgBox(ctrl.Text)
  End If
Next ctrl

 

[extraordinare]

Control Arrays (Solved)

For i = 1 To 104
  Me("TextBox" & i).SetFocus
    If Me("TextBox" & i).Text = "" Then
      Msgbox "Please enter a value in " & "TextBox" & i & " field", vbOKOnly
      Exit Sub
    ElseIf Not IsNumeric(Me("TextBox" & i).Text) Then
      Msgbox "Please enter a numeric value in " & "TextBox" & i & " field", vbOKOnly
      Exit Sub
    ElseIf Me("TextBox" & i).Text < 0 Then
      response = Msgbox("Are you use you want to enter a negative number", vbYesNo)
      If response = Yes Then
        strText(i) = Me("TextBox" & i).Text
      Else
        Me("TextBox" & i).Text = Clear
      End If
    End If
 
Next

Sorry but I had figured since these thread was dealing with Access that this would be a pretty good place to start. I had found this solution on http://www.pcreview.co.uk/forums/thread-1658659.php. I'm sorry if I used the wrong forum for my request.