Text box cannot catch the value from Array?

[johnnychow]

Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim total As Double

Set conn = CurrentProject.Connection

For i = 1 To 2
sql = "select sum([Offering Query].[Among]) from [Offering Query] where [Offering Query].[FundCode]='GEN'" & _
"and [Offering Query].[Year]=" & 2009 & _
"And [Offering Query].[Month] =" & i

rst.Open sql, conn
total = rst.GetString
rst.Close
Form_Form1.Txt(i) = total <-------Wrong? If I put a actual number then work. why? Please help!!!!!!!!!!!!Thank!
Next i

Set rst = Nothing
End Sub

 

[Galaxiom]

I assume you mean it works with an actual number for i in the controlname.

You cannot simply use a variable in the middle of an object name and expect the compiler to realise you meant that letter to be a variable.

The variable must be concatenated into a string.

You could use:
Form_Form1.Controls("Txt(" & i & ")")

or more typically:
Forms!Form1.Controls("Txt(" & i & ")")

However the use of special characters in an object name is a very bad idea. It makes expressions like this very hard to read and easy to get wrong especially when using parentheses.

BTW:
GetString returns a String not an Array.
Month and Year are function names so are not the best choices as field names.

 

[johnnychow]

I assume you mean it works with an actual number for i in the controlname.

You cannot simply use a variable in the middle of an object name and expect the compiler to realise you meant that letter to be a variable.

The variable must be concatenated into a string.

You could use:
Form_Form1.Controls("Txt(" & i & ")")

or more typically:
Forms!Form1.Controls("Txt(" & i & ")")

However the use of special characters in an object name is a very bad idea. It makes expressions like this very hard to read and easy to get wrong especially when using parentheses.

BTW:
GetString returns a String not an Array.
Month and Year are function names so are not the best choices as field names.

Galaxiom, thank you! I am a new guy to learn VBA, thank you for your suggestion.