Solved

Subscript out of range - Access 2003 - VBA

Posted on 2008-10-14
5
1,062 Views
Last Modified: 2013-11-29
Hello,
     There are a couple of records in the database setup as a test for this.  My code seems to be correct, not sure why I am getting the subscript out of range...  Is there something I am missing with dynamic arrays in Access 2003/VBA?

Here is my code... Thanks..

Private Sub CmdEmailPersonnelInterests_Click()
    On Error GoTo Err_CmdEmailPersonnelInterests_Click
        Dim sSQL_Archaelogy As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i As Integer
       
 
 
        'create sql query, load results into dynamic array for later.
        If ckArchaelogy = True Then
            sSQL_Archaelogy = "SELECT Email FROM tblVolInfo WHERE Archaelogy = -1"
            Set db = CurrentDb
            Set rs = db.OpenRecordset(sSQL_Archaelogy, dbOpenSnapshot)
            Dim ArchaelogyArray() As String
           
         
            'Load array with email addresses
            i = 0
            Do While Not rs.EOF
                ArchaelogyArray(i) = rs(i).Value
                i = i + 1
            Loop
           
            'close the recordset
            rs.Close
         
            'release memory
            Set rs = Nothing
            Set db = Nothing
        End If
       
       

 

   ' stAppName = "outlook /c ipm.note /m"
    'Call Shell(stAppName, 1)

Exit_CmdEmailPersonnelInterests_Click:
    Exit Sub

Err_CmdEmailPersonnelInterests_Click:
    MsgBox Err.Description
    Resume Exit_CmdEmailPersonnelInterests_Click
End Sub
0
Comment
Question by:strong-daniel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:strong-daniel
ID: 22712990
I think I might have to use the redim every time I add something to the array?  

0
 

Author Comment

by:strong-daniel
ID: 22713039
Ok...  Still not working... changed code to get record count, use the count to redim the dynamic array...  still getting the errors...




Private Sub CmdEmailPersonnelInterests_Click()
    On Error GoTo Err_CmdEmailPersonnelInterests_Click
        Dim sSQL_Archaelogy As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i, iRecCount As Integer
       
 
 
        'create sql query, load results into dynamic array for later.
        If ckArchaelogy = True Then
            sSQL_Archaelogy = "SELECT Email FROM tblVolInfo WHERE Archaelogy = -1"
            Set db = CurrentDb
            Set rs = db.OpenRecordset(sSQL_Archaelogy, dbOpenSnapshot)
            Dim ArchaelogyArray() As String
           
            iRecCount = rs.RecordCount
            ReDim Archaelogy(iRecCount)

            'Load array with email addresses
            i = 0
            Do While Not rs.EOF
                ArchaelogyArray(i) = rs(i).Value
                i = i + 1
            Loop
           
            'close the recordset
            rs.Close
         
            'release memory
            Set rs = Nothing
            Set db = Nothing
        End If
       
       

 

   ' stAppName = "outlook /c ipm.note /m"
    'Call Shell(stAppName, 1)

Exit_CmdEmailPersonnelInterests_Click:
    Exit Sub

Err_CmdEmailPersonnelInterests_Click:
    MsgBox Err.Description
    Resume Exit_CmdEmailPersonnelInterests_Click
End Sub
0
 

Accepted Solution

by:
strong-daniel earned 0 total points
ID: 22713143
I removed the ReDim Archaelogy(iRecCount) and added  ReDim ArchaelogyArray(0 To iRecCount) As String

and now it works...

Weird, I remember in the old Visual Basic days you never had to do that, you could grow or shrink a dynamic array without redim.

Hmm...

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22713221
Hello strong-daniel,

Chaneg i|RecCount to Long

Regards,

jmoss111
0
 

Author Comment

by:strong-daniel
ID: 22713911
Thanks...  That is a good point...




0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question