creating string arrays causing me a headache 2

[1DMF]

Hi,

I don't seem to be able to create a simple string array, I must be doing something really stupid, so all help is appreciated.

    Dim rs As DAO.Recordset
    Dim sCols As String
    Dim sData As String
    Dim iCnt As Integer
    Dim vFld As Variant
    Dim vVal As Variant
    Dim sFld As String
    Dim sVal As String
    Dim sProv As String
    Dim sProv_Type As String
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Provider_Import_Map] WHERE 1=1", dbOpenSnapshot, dbSeeChanges)
        
    sFld = ""
    sVal = ""
    
    For iCnt = 0 To (rs.Fields.Count - 1)
        sFld = sFld & "," & rs.Fields.Item(iCnt).Name
        sVal = sVal & "," & rs.Fields.Item(iCnt).Value
    Next iCnt

    vFld = Array(sFld)
    vVal = Array(sVal)
    
    Set rs = Nothing
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Provider_Import] WHERE 1=1", dbOpenSnapshot, dbSeeChanges)
        
    sCols = ""
    sData = ""
    
    Do While Not rs.EOF
   
        For iCnt = 1 To UBound(vFld)
            If sCols <> "" Then
                sCols = sCols & ","
            End If
            sCols = sCols & vVal(iCnt)
            
            MsgBox rs.Fields.Item(iCnt).Type
            
        Next iCnt
        
        rs.MoveNext
    Loop

If i breakpoint the code and hover over 'vVal(iCnt)' i get a 'subscript out of range' error, yet iCnt = 1 ?

So I'm assuming although

vVal = Array(sVal)

didn't error , it can't have created an array of strings?

What am I doing wrong?

Thanks, 1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

It's OK, I worked it out..

    ReDim vFld(rs.Fields.Count - 1)
    ReDim vVal(rs.Fields.Count - 1)
    
    For iCnt = 0 To (rs.Fields.Count - 1)
        vFld(iCnt) = rs.Fields.Item(iCnt).Name
        vVal(iCnt) = rs.Fields.Item(iCnt).Value
    Next iCnt

It seems you have to redim the variant declared variables to the dimensions required before you can use them!

Well that's what i've deduced, please correct me if i'm wrong.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[SkipVought]

hi,

    vFld = Split(sFld,",")
    vVal = Split(sVal,",")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[1DMF]

That's what the Array() keyword is supose to do isn't it?

create an array form a comma dilimited string, only it didn't work?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[SkipVought]

Does not say, "comma delimited string", rather "comma-delimited list of value[red]s[/red]"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

in other words
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

MyWeek = Split("Mon, Tue, Wed, Thu, Fri, Sat, Sun,",")

 

[1DMF]

OIC, as my CSV string wasn't 'double-quote' encapsulated for each index required, I got the sub-script out of range error.

I had ended up with an array with only one index which had the entire sting in it.

D'oh!

cheers peeps!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!