Array compile error "Expected: As"

[eschloss]

Access 2003

I do not understand why I receive an error for the indicated line below.
Please let me know my syntax error.

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim name() As String
Dim i As Integer
i = 0
sql = "SELECT L_tblArea_Tech.L_Tech " & _
"FROM L_tblArea_Tech " & _
"WHERE L_tblArea_Tech.L_Area = 'Q/A' And L_tblArea_Tech.L_Tech_Current
= Yes " & _
"ORDER BY L_tblArea_Tech.L_Tech;"
Set db = CurrentDb()
Set rst = db.OpenRecordset(sql)
rst.MoveFirst

Do While Not rst.EOF
i = i + 1
ReDim name(1 To i) As String
name(i) = rst!L_Tech <-----------Error
here----------------
rst.MoveNext
Loop

ReDim name(1) As String
rst.Close
Set rst = Nothing

MsgBox name7

End Sub

 

[Jim Burke in Novi]

Not sure why the error, but I think I'd do it this way:

if rst.eof then
do what you need to in case of eof...., exit sub
end if

rst.MoveLast
ReDim name(1 to rst.Recordcount) As String
rst.MoveFirst
Do While Not rst.EOF
i = i + 1
name(i) = rst!L_Tech
rst.MoveNext
Loop

 

[eschloss]

Thanks for the reply Jim. When I used your suggestion, I still received the
same error on the same line. Any thoughts why?

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim name() As String
Dim i As Integer

i = 0
sql = "SELECT L_tblArea_Tech.L_Tech " & _
"FROM L_tblArea_Tech " & _
"WHERE L_tblArea_Tech.L_Area = 'Q/A' And L_tblArea_Tech.L_Tech_Current
= Yes " & _
"ORDER BY L_tblArea_Tech.L_Tech;"
Set db = CurrentDb()
Set rst = db.OpenRecordset(sql)

If rst.EOF Then 'for when there are no records
End If

rst.MoveLast 'count the records beforehand
ReDim name(1 To rst.RecordCount) As String

rst.MoveFirst
Do While Not rst.EOF
i = i + 1
'ReDim name(1 To i) As String
'ReDim Preserve name(1 To i) Only if you want to keep previous info.
name(i) = rst!L_Tech
rst.MoveNext
Loop

ReDim name(1) As String
rst.Close
Set rst = Nothing

MsgBox name7

End Sub

 

[John Spencer]

Also, you want to avoid using NAME as a variable. Every object in
Access has a name. So Access could be confused when you try to use name
as a variable name.

Also, if you redim a dynamic array without using the optional preserve
argument all the data is wiped from the array. Check the help for
Redim. So you would probably be better off resizing the array to the
required size ONCE as Jim Burke pointed out.

AND sizing the array once is faster then continually resizing the array.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[eschloss]

Thanks guys. Changing "name" fixed my issue.

Also, you want to avoid using NAME as a variable. Every object in
Access has a name. So Access could be confused when you try to use name
as a variable name.

Also, if you redim a dynamic array without using the optional preserve
argument all the data is wiped from the array. Check the help for
Redim. So you would probably be better off resizing the array to the
required size ONCE as Jim Burke pointed out.

AND sizing the array once is faster then continually resizing the array.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================