Solved

Subscript out of range - Access 2003 - VBA

Posted on 2008-10-14
5
1,003 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
  • 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now