Reference to value in recordset

[dhartford]

There are totle 3 records in recordset. How do I reference to the records in the set.




....
for i = 0 to Rst.recordcount -1
weight = data1(i) + data2(i)
next i



It doesn't work. Thanks in advance.

[Demosthine]

Hey there again.

There are several different methods to navigate with. You can MoveFirst, MovePrevious, MoveNext, and MoveLast.

In your code, once you access the data, you have to manually "turn the page" so to speak. Using the MoveNext command, you are able to access the next set of records.

RecordSet Objects also have a Beginning of File (BOF) and End of File (EOF) property. These properties tell you if you are at the beginning or end of the records. Think of it as the equivalent to LBound and UBound for Arrays. This may give you an alternate method to control your loop as seen in the second set of code below.

Now, to access the individual fields in a Recordset, you pass the Field Name as an Index. You can pass an Integer as well, but using the text equivalent for the Field Name is more definitive.


For i = 0 to Rst.RecordCount - 1
GrossWeight = Rst("PackageWeight").Value + Rst("ContentWeight").Value
Rst.MoveNext
Next i

' OR

Do
GrossWeight = Rst("PackageWeight").Value + Rst("ContentWeight").Value
Rst.MoveNext
Loop Until Rst.EOF


Any other questions?
Scott

[dhartford]

Hey there again.


For i = 0 to Rst.RecordCount - 1
GrossWeight = Rst("PackageWeight").Value + Rst("ContentWeight").Value
Rst.MoveNext
Next i

' OR

Do
GrossWeight = Rst("PackageWeight").Value + Rst("ContentWeight").Value
Rst.MoveNext
Loop Until Rst.EOF


Any other questions?
Scott

Very appreciate, Scott.

What if the values at different i levels e.g. Rst("PackageWeight")(i).Value + Rst("PackageWeight")(i+1).Value ?

Thanks again.

[Demosthine]

Unfortunately, RecordSets aren't as easily accessed as an Array is. You're referring to accessing different portions of a an Array using the Index value.

Think of an Array as a bookshelf. An each Index tells you what shelf you need to look at. Array(1) is the top shelf, Array(2) is one shelf down, etc. You can easily see all of the data at one time and chose the correct "book" from any of the shelves.

Now think of a Recordset as one of those books on the shelves. Each Record of your data is held on a separate page. In order to get the information from inside the book, you have to turn the pages. Once you turn a page, though, you can't access what was on that previous page. Unless you write it down on a separate sheet of paper.

That's essentially what you have to do with the math you are asking about. There are several ways of doing this, but ultimately, it comes down to having separate copies of the data you need.

The first method is to store the first "weight" as the example uses, in a variable, such as dblWeight1. Now you can MoveNext and store it in dblWeight 2, etc. Once you have all of the necessary values from your Recordset, you add them.

You can simplify this and make it more dynamic using an Array. Define your array large enough to hold all of your data. You state your RecordSet has three Records, so your Array would be 3. Use a loop to navigate the RecordSet and assign it to the array.


Dim rstPackages as Recordset
Dim dblWeights() as Double

' Query rstPackages...

ReDim dblWeights(rstPackages.RecordCount)

For intIndex = LBound(dblWeights) to UBound(dblWeights)
dblWeights(intIndex) = rstPackages("PackageWeight").Value
rstPackages.MoveNext
Next intIndex

Dim dblTotalWeight as Double
For intIndex = LBound(dblWeights) to UBound(dblWeights)
dblTotalWeight = dblTotalWeight + strWeights(intIndex)
Next intindex


Using the above method and separating the data storage from the math allows you to perform additional calculations inbetween if necessary. You can, of course, combine the above code into a simpler version.


Dim rstPackages as Recordset
Dim dblTotalWeight as Double

' Query rstPackages...

Do
dblTotalWeight = dblTotalWeight + rstPackages("PackageWeight").Value
rstPackages.MoveNext
Loop Until rstPackages.EOF = True


As you get more advanced with SQL Statements, there are ways to do the math from within the Query Statement.

Scott

[dhartford]

Unfortunately, RecordSets aren't as easily accessed as an Array is. You're referring to accessing different portions of a an Array using the Index value.

Think of an Array as a bookshelf. An each Index tells you what shelf you need to look at. Array(1) is the top shelf, Array(2) is one shelf down, etc. You can easily see all of the data at one time and chose the correct "book" from any of the shelves.

Now think of a Recordset as one of those books on the shelves. Each Record of your data is held on a separate page. In order to get the information from inside the book, you have to turn the pages. Once you turn a page, though, you can't access what was on that previous page. Unless you write it down on a separate sheet of paper.

Scott

Scott,

You're really a VBA expert. Your explanation of difference between recordset and array definitely cleared my confusion. I reference to your idea and got my code work.

I'm new to VBA and currently ad will need your advise for sure.


Very appreciate.