Filling an Array from a Record

[connollyg]

I want to fill an array with data from a record, currently i have a record that looks like this:-

Key autonum
desc text
Pos01 number
Pos02 number
.
.
.
Pos99 number

and i am trying to use the following code to fill the array

'
SQL_statement = "SELECT * FROM PointsBasis WHERE PointsBasis.PointsID="&rst1("PointsBasisID")&";"
Set rst1 = db.Execute(SQL_statement)
For iL_I = 1 To 99
aa = chr(34)+"Pos"&Right("00"&Cstr(iL_I),2)+chr(34)
response.write ">"& aa &"<"
' aG_Points(iL_I) = rst1(aa)
Next

This is the kind of output i get from the response.write

>"Pos01"<
>"Pos02"<
>"Pos03"<
>"Pos04"<

but if i uncomment the statement following the response.write i get

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Results/Results_Service2.asp, line 605

but if i change the statement to this
' aG_Points(iL_I) = rst1("Pos01")
It works fine.

Am i doing this the right way and if so what is wrong with my code?

G

 

[DB7]

Try this, to get the referencing correct.

For iL_I = 1 To 99
If iL_I Like "[0-9]" Then aa = "0" Else aa = ""
aG_Points(iL_I) = rst1("Pos" & aa & iL_I)
Next


In case of problem, throw this in standard module & run, you'll get the idea...

Sub ABC()
Dim iL_I As Integer, aa As String, aG_Points(1 To 99) As Variant
For iL_I = 1 To 99
If iL_I Like "[0-9]" Then aa = "0" Else aa = ""
aG_Points(iL_I) = "Pos" & aa & iL_I
Debug.Print aG_Points(iL_I)
Next

End Sub


Hope this helps, good luck!

 

[Jon K]

For iL_I = 1 To 99
aa = chr(34)+"Pos"&Right("00"&Cstr(iL_I),2)+chr(34)
response.write ">"& aa &"<"
' aG_Points(iL_I) = rst1(aa)
Next

Try moving the two Chr(34)s from aa to response.write e.g.

   For iL_I = 1 To 99
     [b]aa = "Pos" & Right("00" & CStr(iL_I), 2)[/b]
     response.write ">" & Chr(34) & aa & Chr(34) & "<"
     aG_Points(iL_I) = rst1(aa)
   Next

 

[WayneRyan]

Connollyg,

You know if you had those values as individual records it
would be a lot easier. Had to say that. They really should
be organized better.

This should fill your array:

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim i As Long
Dim SQL As string
Dim aG_Points(100) As Long
'
' Can't use rst1, it's not open yet!
'
' Previous SQL had: rst1("PointsBasisID")
'
Set db = CurrentDb
SQL = "SELECT * " & _
      "FROM   PointsBasis " & _
      "WHERE  PointsBasis.PointsID = " & Me.PointsID & ";"

Set rst1 = db.Execute(SQL_statement)
While Not rst1.EOF And Not rst1.BOF
  For i = 1 To 99
    aG_Points(i) = rst1("Pos" & Format(i, "09")
    Next i
  '
  ' Do Something with the array
  '
  MsgBox("Doing Something, then getting next record.")
  rst1.MoveNext
  Wend

btw,
Not sure about the Format, - the "09"
Wayne

 

[WayneRyan]

Jon,

That's a beauty!

aa = "Pos" & Right("00" & CStr(iL_I), 2)

Looks better than my Format call ...

Wayne

 

[connollyg]

Guys,

Thanks for the fast response, in the end this is what worked!

response.write ","& rst1("Pos"&Right("00"&Cstr(iL_I),2)) &"<

so my code now looks like this

'
SQL_statement = "SELECT * FROM PointsBasis WHERE PointsBasis.PointsID="&rst1("PointsBasisID")&";"
Set rst1 = db.Execute(SQL_statement)
For iL_I = 1 To 99
aG_Points(iL_I) = rst1("Pos"&Right("00"&Cstr(iL_I),2))
Next
'

 

[connollyg]

Wayne,

Do you think that having the individual points values in its own record would be more efficient than the way i have done it?

I dont anticipate their being very many sets of these values, perhaps 10 max

what do you think?

G

 

[WayneRyan]

G,

Definitely better in seperate records! Without a doubt.
Then, you can do a lot more with queries and you won't
have to resort to code as much.

Do a search here for "Normalization", good reading.

btw,

Your SQL statement to populate the recordset, contains a
reference to the recordset! That's not possible!

WHERE PointsBasis.PointsID=" & rst1("PointsBasisID") & ";"

rst1 doesn't exist yet!

Wayne

 

[DB7]

Good work Wayne & Jon. Nice catch on the SQL statement, recordset reference Wayne.

Guys, bear with me here, I'm not familiar with the Response.Write statement, and I thought the Execute method, was only applicable to Action queries.

I looked up both in the VBA help, couldn't find anything for "Response.Write", and my previous understanding of the Execute method, was confirmed?

I'm still relatively new to VBA, so I wouldn't be surprised if the full potential of these "methods" , have eluded me.

If no one minds, could I get a brief explanation, of the 2 (alleged) "discrepencies".

Either way, Good Luck connollyg!

 

[WayneRyan]

DB7,

Nice catch yourself:

Set rst1 = db.Execute(SQL_statement)

Obviously that should have been a db.OpenRecordset

As to the Response.Write method ...

We didn't get to see all of the code, it seems to be part of the object for
writing to "html".

Didn't seem pertinent to the problem, so I didn't notice ...

Isn't that two (2) catches for you on this thread?

Wayne

 

[DB7]

Thank-you Wayne,

I appreciate that, both the explanation & recognition (even though my catch, was somewhat inadvertant, LOL!).

Either way Wayne, a pleasure.
Thank-you & take care!