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
'====================================================
Jim Burke in Novi wrote:
> 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" wrote:
>
>> 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
|
Similar Threads
