Reading an Array from a Recordset 2

[edsearl]

I get a "Run time error 3265 Item not found in this collection." message at If xmax < d3y!KVA(i) Then when running this code.
Any help?

Private Sub Command0_Click()
Dim db As DAO.Database
Dim d3y As Recordset
Dim KVA(36) As Integer
Dim MAX As Single
Dim MIN As Single
Dim xmax As Single
Dim xmin As Single
Dim i As Single

Set db = CurrentDb()
Set d3y = db.OpenRecordset("Devices3YearsT", dbOpenDynaset)

With d3y
Do Until d3y.EOF
xmin = 0
xmax = 0

For i = 1 To 36
If xmax < d3y!KVA(i) Then
xmax = d3y!KVA(i)
End If
If xmin > d3y!KVA(i) Then
xmin = d3y!KVA(i)
End If
Next i
.Edit
!MAX = xmax
!MIN = xmin
.Update
d3y.MoveNext
Loop
End With

End Sub

 

[ByteMyzer]

The above code assumes that your table/query Devices3YearsT has 37 fields. Note that field numbering for a recordset starts with zero. If the table/query only has 36 fields, you may need to revise the following line:

    For i = 1 To 36

...to...

    For i = 0 To 35

 

[Zion7]

Item not found in this collection, means you're referencing your fields incorrectly. First glance,will also testify.

For i = 1 To 36
If xmax < d3y!KVA(i) Then
xmax = d3y!KVA(i)

Should be...

For i = 1 To 36
If xmax < d3y("KVA" & i) Then
xmax = d3y("KVA" & i)


But sinced you used With d3y, you can, I believe, use this syntax...

Do Until .EOF
xmin = 0
xmax = 0

For i = 1 To 36
If xmax < .("KVA" & i) Then
xmax = .("KVA" & i)

Good Luck!

 

[scriverb]

I am a little confused by these postings. In your original code you have set the recordset with this command:

Set d3y = db.OpenRecordset("Devices3YearsT", dbOpenDynaset)

I take it that "Devices3YearsT" is a table that you want to look at the value of the fields. You have also made a declaration of an array with the following statement:

Dim KVA(36) As Integer

I fail to see how you are making any connection between them other than trying to use the array as a field name for the recordset. Doesn't make sense to me. You can enumuate through the recordset fields collection with a field value but to use a reference as you are here "d3y!KVA(i)" or any of the recommendations by dboulos I don't believe will get you want you want. If what you are looking for is to find the min and max of the field values in each record then try something like this:

d3y.Fields(i).value

The above uses the fields collection of the table identified in the recordset to reference its value. As you loop enumerates through the number of fields in the table each value will be analyzed. You do not need an array and surely you don't use an array as the field name in this instance.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Zion7]

Actually Bob, you're right. You don't need to declare KVA, as an array, and it wasn't used correctly(not populated). I overlooked that. But, the fields he wants analysed are named KVA1, KVA2, KVA3 up to KVA36. To reference that as

For i = 1 To 36
d3y("KVA" & i)

would be correct, in this case. Yes, you can use the fields collection also, like you said, possibly depending on the objective though. If wrong fields are compared, unexpected results, may occur?

But you're right again, the procedure is a little confusing.
edsearl you have MIN & MAX declared as variables, but use them as field references....!MAX = xmax !MIN = xmin?

And, if I'm not mistaken, the Edit/Update part, will give you the value of xmin & xmax, according to the last field only, KVA36. The For/Next statement, becomes redundant?

Thank-you for that Bob, I read the thread a little hastily, just addressing the run-time error.

edsearl, I think I've understood the objective correctly, but maybe not? The referencing aspect should be correct, if you do a test & it works, this may cut down the variables(no pun intended).

 

[scriverb]

dboulos: Sorry about the confusion here. Yes, if the names of all of his fields are KVA & some number what you say is correct. Don't understand the field naming convention here but glad that I could help with some of the confusion. Probably mostly on my part as I was focusing in on the thread title, declaration of the array and then trying to make reference to it in the expression.

Seems like you have well in hand now to help edsearl. Will keep monitoring and will assist if things get bogged down.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[edsearl]

Thank you all for your time.
The d3y.Fields(i).value worked.
What I should have told you in the beginning was that when I created the table I named those fields KVA(1),KVA(2)...KVA(36). I did that so that I could use them in a do loop.
Now it seems that the parentheses were a waste of effort. Or worse.
I can't even get a value even when I try to make something = d3y!KVA(1), though I can make something = d3y!name for example.