SQL Running Behind the Scenes

[jo15765]

I inherited a database (yet again) and whenever the form loads, I get
Run time error 9
Subscript Out of Range

On this line of SQL

SqlValues = SqlValues & "," & ID(i) & ",'" & CtcName(i) & "','" & Title(i) & "','" & Address(i) & "','" & Phone(i) & "','" & Interviewed(i) & "','" & DM(i) & "'"

Can someone tell what the issue is or if you need further let me know what else I would need to display?

[pbaldy]

It might help to see the rest of the code. Offhand I'd wonder if the value of i was outside the range of the array.

[jo15765]

I have no idea?! How would I check that?
Here is the code in its entirity

    Dim ID(10) As String
    Dim CtcName(10) As String
    Dim Title(10) As String
    Dim Address(10) As String
    Dim Phone(10) As String
    Dim Interviewed(10) As String
    Dim DM(10) As String
    Dim Sql, SqlInsert, SqlValues As String
    Dim i, j, NumCtcs As Integer
    Dim dbConn As New ADODB.Connection
    Dim Rs As New ADODB.Recordset
    
    DoCmd.SetWarnings False
    
    Set dbConn = Application.CurrentProject.Connection
    Rs.Open "SELECT * FROM tbl_ct WHERE AID = " & Me.txtAID & ";", dbConn, adOpenForwardOnly, adLockReadOnly
    DoCmd.RunSQL "DELETE * FROM tbl_Clist WHERE AID = " & Me.txtAID & ";"
    
    i = 1
    While Not Rs.EOF
        If (i <= 10) Then
            ID(i) = Rs("ID")
            CtcName(i) = Rs("Sal") & " " & Rs("FName") & " " & Rs("LName")
            If (Rs("Title") <> "") Then Title(i) = Rs("Title") Else Title(i) = "No Title Captured"
            Address(i) = Rs("Address1") & ", " & Rs("City") & ", " & Rs("State") & ", " & Rs("Zip")
            If (Rs("Phone_Main") <> "") Then Phone(i) = Rs("Phone_Main") Else Phone(i) = "No Direct Phone"
            If (Rs("Interviewed") <> "") Then Interviewed(i) = Rs("Interviewed") Else Interviewed(i) = "No"
            If (Rs("DM") <> "") Then DM(i) = Rs("DM")
        End If

NextContact:
        i = i + 1
        Rs.MoveNext
    Wend
    j = i
    i = 0
    If j > 10 Then j = 10
    NumCtcs = Me.txtNumCtcs
    While i <= j
        If (DM(i) = "Dead") Then
            If i < 10 Then
                NumCtcs = NumCtcs - 1
                ID(i) = ID(i + 1)
                CtcName(i) = CtcName(i + 1)
                Title(i) = Title(i + 1)
                Address(i) = Address(i + 1)
                Phone(i) = Phone(i + 1)
                Interviewed(i) = Interviewed(i + 1)
                DM(i) = DM(i + 1)
            End If
        End If
        i = i + 1
    Wend
       
    i = 0
    
    Sql = "INSERT INTO tbl_Clist "
    SqlInsert = "(AID"
    SqlValues = ") VALUES(" & Me.txtAID
    While i < NumCtcs
        i = i + 1
        SqlInsert = SqlInsert & ",Xtel_" & i & ",Name_" & i & ",Title_" & i & ",Address_" & i & ",Phone_" & i & ",Interviewed_" & i & ",DM_" '& i
        SqlValues = SqlValues & "," & ID(i) & ",'" & CtcName(i) & "','" & Title(i) & "','" & Address(i) & "','" & Phone(i) & "','" & Interviewed(i) & "','" & DM(i) & "'"
    Wend
    
    
    Sql = Sql & SqlInsert & SqlValues & ");"
    
    Me.Text5 = Sql
    DoCmd.RunSQL Sql
    DoCmd.SetWarnings True
    
    Rs.Close
    dbConn.Close
    Set Rs = Nothing

[pbaldy]

At the point when it errors, go into debug mode and hover over i and see what its value is. My guess is 10.

[jo15765]

That is the OnLoad() event of a Form, and it throws this error as soon as I try to open the form. Would there be a value assigned on OnLoad()?

[pbaldy]

I assumed the error would offer you the option to go into debug mode. Does it not? Also, I don't get the point of the array. Why not just insert from the recordset?

[jo15765]

i = 11 when I get the subscript out of range message. I don't have a clue why it was set up the way that it is. Someone else built this database and it was handed off to me, and I am trying to make heads or tails of the situation.

It's pulling the data from tbl_ct and inserting the data into tbl_Clist. It is inserting it into a different table because the tbl_Clist is the "most recent" data, and there are a few reports based solely off of this table. I think that answers your question (If I understood it correctly)

[pbaldy]

Well, the error is because 11 is outside the bounds of the array. I understand the "what" of what you're doing (though I might disagree with the "why"), it's the "how" I don't see the point of. I can't remember the last time I used an array. You're going through steps to copy data from recordset to array, tweaking it a little, then inserting to a table. I'd probably go straight from table a to table b, then tweak the data.

[jo15765]

Can I select all records with an ID of (hypothetical) 2, move into a new table, then once in that table, alter the data? Like putting phone in the format of (000) 111-2222 and adding "No Phone" if Phone is null?

[pbaldy]

Sure:

INSERT INTO...
SELECT...
FROM...
WHERE ID = 2

[jo15765]

How would the formatting then take place if it is done after the records have been inserted into the table?

[pbaldy]

I don't really follow what that bit is trying to accomplish, but you can open a recordset on the destination table and step through it.

[jo15765]

Its basically taking fields from the table, combining them before placing into the new table for example:
CtcName is combining Salutation First Name and Last Name
Title is either Title or No Title Captured
Addresss is Address1 City State & Zip
Phone Main is either Phone or No Direct Phone
Interviewed is either Interviewed or NoDM is DM

[pbaldy]

That you could do in the SQL populating the second table in the first place, using either concatenation or an IIf() to evaluate the field and put the "No whatever" if it's blank.

[jo15765]

i = 11 when I get the subscript out of range message.

If I increased the range of i to be higher than 11, would that cause the subscript out range error to go away? But my question is also, what else would that possibly create issues with?