Marking a record without bookmark?

[ineedahelp]

Hi,

I am trying to evaluate data in a table. First I need to evaluate NotionalValue for Symbols that match. If it passes my parameters, I need to apply my "flags" to the flag field. Instead of using so many rst.movepreviou s and rst.movenext commands, is there a better way to "tag" a record so that I can return to it? I do not have a key field...symbol is my unique field. Also, with my current code, I am getting a "No Current Record" error message at the "*****" I know that the rst!Symbol is at the rst!movefirst position, but for some reason my rst.BOF is not true...any thoughts here too? Thanks so much!

[CODE]
Private Sub cmbApplyFlag_Cl ick()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mNV1, mNV2, mTQ1, mTQ2, mRate1, mRate2 As Double
Dim counter, mUnder, mOver, i As Long
Dim curSymbol, BM As String

Set db = CurrentDb()
Set rst = db.OpenRecordse t("TEMPLongShor t")
counter = 0
mUnder = 0
mOver = 0

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst

Do Until rst.EOF
curSymbol = rst!Symbol
BM = rst.Bookmark
Debug.Print BM
Debug.Print curSymbol & " " & rst!NotionalVal ue & " " & rst!Symbol
Do While rst!Symbol = curSymbol
If rst!NotionalVal ue > 1000000 Then
mOver = mOver + 1
ElseIf rst!NotionalVal ue < 1000000 Then
mUnder = mUnder + 1
End If
counter = counter + 1
rst.MoveNext
BM = rst.Bookmark
Debug.Print BM
Debug.Print curSymbol & " " & rst!NotionalVal ue & " " & rst!Symbol
Loop
rst.MovePreviou s
Debug.Print curSymbol & " " & rst!NotionalVal ue & " " & rst!Symbol
If counter = mUnder Or counter = mOver Then
rst.MoveNext
Debug.Print curSymbol & " " & rst!NotionalVal ue & " " & rst!Symbol
Else
Debug.Print rst!Symbol, rst!NotionalVal ue
Debug.Print curSymbol
Do While rst!Symbol = curSymbol
If rst!NotionalVal ue > 1000000 Then
rst.Edit
rst!Flag = "$"
rst.Update
Else
rst.Edit
rst!Flag = "M"
rst.Update
End If
If rst.BOF Then
Exit Do
Else
rst.MovePreviou s
*****Debug.Prin t curSymbol & " " & rst!NotionalVal ue & " " & rst!Symbol
End If
Loop
For i = 1 To (counter + 1)
rst.MoveNext
Debug.Print curSymbol & " " & rst!NotionalVal ue & " " & rst!Symbol
Next
End If
counter = 0
mUnder = 0
mOver = 0
Loop
End If
db.Close
rst.Close
Set db = Nothing
Set rst = Nothing
End Sub
[ENDCODE]

[MMcCarthy]

Firstly, instead of using ENDCODE use /CODE to closed the code tags.

I can't follow the logic of your code. Can you explain what you are doing here and why.

Mary

[ineedahelp]

I am basically cycling through records in a table. I need to perform some analysis and then depending on what happens here I need to perform a different analysis on the same subgroup of records. I thought I would use the bookmark 'function', but have been unsuccessful. The work around I found was using rst.movepreviou s by my variable 'counter'. Sorry for being unclear.

[maxamis4]

The best answer for this is an array. You would store the values that meet your criteria an array. When your first pass is through and you need to get more information on the subset of numbers you use the array as the table to reloop through the recordset.

What exactly are you trying to do. I know you explained what it suppose to do, but could you explain the purpose?

[jamjar]

If you're trying to find a subset of records to process, could you use and SQL statement to retrieve the records meeting your criteria (say, over $1000000) rather than evaluating each record through code? James

[MMcCarthy]

I am basically cycling through records in a table. I need to perform some analysis and then depending on what happens here I need to perform a different analysis on the same subgroup of records. I thought I would use the bookmark 'function', but have been unsuccessful. The work around I found was using rst.movepreviou s by my variable 'counter'. Sorry for being unclear.

From a logic point of view think of using two recordsets.

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("TEMPLongShort")

rs1.MoveFirst
Do Until rs1.EOF
   ' Act on record here?
   Set rs2 = db.OpenRecordset("SELECT * FROM TEMPLongShort WHERE Symbol=" & rs1!Symbol)
	  rs2.MoveFirst
	  Do Until rs2.EOF
		 ' Act on subset of records here.  
		 ' If you would like to remove these records from further action 
		 ' add a checkbox field and set to true to eliminate them.  Don't 
		 ' forget to check subsequent records for the value of this field
		 rs2.MoveNext
	  Loop
   rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

Mary

[ineedahelp]

Thank you for your ideas...I have thought of this approach too. It seems so inefficient as I will only have 2-5 records in the subset at a time. I was hoping I could MARK the start and end of a new group of records (I have sorted my list so all matching symbols will be together) and return to the start point and RST.MOVENEXT through the list again and again as need be. I thought using a bookmark like feature could work, any thoughts on this or should I just use your suggestion of 2 recordsets.

Thanks again!