Store each field of a recordset in its own variable? 2

[xuanb]

Hi,
How do I loop through a recordset and store each field in a variable. I need these variables to perform some calculations and I don't want to hardcode them because they are part of the business rules (like commission type stuff) that might change and then should only be changed in the backend. Pseudo code example:

intVal1 = rst!PctVal.field1
intVal2 = rst!PctVal.field2
...

Thanks a lot...and
It's Friday!

 

[jrbarnett]

Hi,

Either:

intVal1 = rst.Fields("fieldname")

or

intVal1 = rst!fieldname

Use the following structure to loop through your code:

Do While Not rst.EOF
  intVal1 = rst!Fieldname
  debug.print intVal1
  rst.MoveNext
Loop

John

 

[xuanb]

John,
Thanks for your reply. I got this loop structure. The problem is I would like to store EACH record in a NEW variable. i.e. if there are 4 records I want 4 different variables at the end of the loop. With this loop the variable overwrites itself and in the end only holds the last record. How would I store the recordset in an ARRAY?

I guess I could simply hardcode these few values as constants but somehow this problem is nagging me...ye know what I mean?

 

[Norris68]

Create an array.

Dim MyVars() As Long
ReDim MyVars(0)
While Not rs.EOF
    MyVars(UBound(MyVars))=rs.Fields("fieldname")
    rs.MoveNext
    If Not rs.EOF Then ReDim Preserve MyVars(UBound(MyVars)+1)
Wend

or you could find out how many records there are and dimension your array just once

Dim MyVars() As Long
If Not rs.EOF Then 'Populate recordset so we can use the RecordCount property
    rs.MoveLast
    rs.MoveFirst
End If
ReDim MyVars(rs.RecordCount-1) 'Dimension the array (zero-based)
While Not rs.EOF
    'AbsolutePosition property is zero-based too
    MyVars(rs.AbsolutePosition) = rs.Fields("Fieldname")
    rs.MoveNext
Wend

 

[xuanb]

YEAH! Thanks for jumping in Norris68! ...this is the best forum in the world

 

[Norris68]

Encouraged by your star ...

If you wanted to store multiple fields, you could go further and define your own type (in a public module) and declare an array of that type.

(Public Module)
Public Type MyType
    ID As Long
    Description As String
End Type

(Form Module)
...
Dim MyVars() As MyType
...
With MyVars(rs.AbsolutePosition)
    .ID = rs.Fields("IDField")
    .Description = rs.Fields("TextField")
End With

 

[xuanb]

Hey, the stars are bright and the possibilities endless...I'll save this as a code snippet

May I ask you something about the VBE since you mentioned Public and Form Module?

I can't run any code in the Immediate Window when I'm in the Form Module. When I hit F5 my functions and subs don't even appear in the Macros list. And when I type ?functionname in the Immediate Window nothing happens.
To test something I have to copy/paste a bit of code into a public module.
Why???

 

[Norris68]

They need to be either Public or in scope. You can declare variables, subs and functions as being Public in form modules - then you could call them from the Immediate window. If they are not Public, you will need to break your code within a sub in the form module - they will be accessible then. You could reference the form when calling your subs, but it's awkward syntax and you are probably better off moving them to public modules - where you could then call them from other forms/code.

You cannot declare public types and enums in form modules.

 

[xuanb]

I got an out of bounds error. I believe you forgot to increment the AbsolutePosition.

If Not rst.EOF Then 'Populate recordset so we can use the RecordCount property
rst.MoveLast
rst.MoveFirst
End If
ReDim MyVars(rst.RecordCount - 1) 'Dimension the array (zero-based)

While Not rst.EOF
'AbsolutePosition property is zero-based too
MyVars(rst.AbsolutePosition) = rst.Fields("RatingName&quot
Debug.Print MyVars(rst.AbsolutePosition)
rst.MoveNext
If Not rst.EOF Then ReDim Preserve MyVars(rst.AbsolutePosition + 1)
Wend

Tks for the module tip.