Help with navigating a recordset

[Zaeed]

Hey,

I have a recordset which extracts atm just one field from a query and runs through that field looking for a particular value (-1), when it finds one it executes a bit of code and then continues its search.

The bit of code extracts information via DLookup from the same query. The problem with this is that i've discovered that there can be more than one value to be extracted when that code executes. The obvious issue then is that DLookup only takes one of these values, thus not getting all the information.


What I have now done is include the whole query in the recordset (SELECT * FROM ...)
My issue now is I am unaware of how to correctly navigate the recordset the same way I was before, since there is now more fields than just the simple way from above.
Basically I am hoping somebody can show me how to cycle through the the recordset based on just one of the colums, if that makes sense.

Cheers

 

[pbaldy]

For starters, I'd use an SQL statement to open the recordset, so I only got the relevant records:

"SELECT Blah FROM TableName WHERE FieldName = -1"

Then:

Do While Not rs.EOF
  'do your thing
  rs.MoveNext
Loop

Will loop through whatever records are returned.

 

[Zaeed]

um, sorry to be a pain but i made a mistake in that first post. The field that gets extracted (say 4 for example) is the Primary Key from another table, thats the table where the -1's are that get found.

My bad

Edit**
So just to make it clear.

What I have so far:
Recordset filled from query.
Cyclying through query till finding correct column name.
This gives me a primary key for another table.
I have a value to crosscheck that table with.

Im thinking the best method would be to have another recordset that gets filled based on that primary key, and then cycle though that, close it, then move to the next record of the first recordset.

That make sense??

 

[pbaldy]

I'm probably still not clear, but you could certainly open a second recordset within the first, using a value from the first:

"SELECT...WHERE FieldName = " & rs!FieldName

 

[Zaeed]

yeah, thats what i've done and it works a treat.
Thanks for your help paul.

I do have one issue though, when looping through the fields of the second recordset, the code block

For Each fld2 In .Fields
    If (fld2.Value) = -1 Then
        If fld2.name = primLoc Then
            x = MsgBox("SUCCESS")
            x = MsgBox(rst!FullName)
        End If
    End If
Next

doesnt seem to stop when it hits the end of the record, and gives me an error saying <no current record>

any suggestions?

 

[Zaeed]

wait, nevermind, when copying the Do Until rst.EOF i forgot to change rst to rst2

Thanks again Paul

 

[pbaldy]

No problem Matt.