continuous loop through a recordset - help

[monrosal]

I need to loop through a recordset and grab a value while I'm looping.

First let me display an example table I'm using:
level levelAbove qty
------ ----------- ----
levelA none 1
levelB levelA 1
levelC levelB 2
levelD levelB 3

The "levelAbove" field is a field that stores the level above a specific level. So, levels levelC and levelD belong to levelB.

So let's use levelD. First I want to grab the qty of levelD. Next I look at levelD's levelAbove which is levelB and move to levelB's record then I grab that quantity which is 1. Then I search for the levelB's levelAbove which is levelA and move to that record and grab levelA' quantity which is 1. So it's like a continuous loop until I get to the top level.

Then I multiply the quantities 3 * 1 * 1.

Does anybody know how I can do this?

Thanks so much

Ramon

 

[jebry]

Hi Ramon!

Try this:

Dim strLevel As String
Dim rst As DAO.Recordset
Dim lngProduct As Long

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

strLevel = txtLevel(or whereever you get your initial level from)
lngProduct = 1

Do Until strLevel = "none"
rst.FindFirst "level = '" & strLevel & "'"
lngProduct = rst!qty*lngProduct
strLevel = rst!levelAbove
Loop

Set rst = Nothing

I haven't tested it but I think this will give you what you want.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[monrosal]

Jeff,

Thanks for the help. The code works. I really appreciate it.

 

[monrosal]

Jeff,

Thanks for the help. The code works. I really appreciate it.

Ramon