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.moveprevious 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_Click()
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.OpenRecordset("TEMPLongShort")
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!NotionalValue & " " & rst!Symbol
Do While rst!Symbol = curSymbol
If rst!NotionalValue > 1000000 Then
mOver = mOver + 1
ElseIf rst!NotionalValue < 1000000 Then
mUnder = mUnder + 1
End If
counter = counter + 1
rst.MoveNext
BM = rst.Bookmark
Debug.Print BM
Debug.Print curSymbol & " " & rst!NotionalValue & " " & rst!Symbol
Loop
rst.MovePrevious
Debug.Print curSymbol & " " & rst!NotionalValue & " " & rst!Symbol
If counter = mUnder Or counter = mOver Then
rst.MoveNext
Debug.Print curSymbol & " " & rst!NotionalValue & " " & rst!Symbol
Else
Debug.Print rst!Symbol, rst!NotionalValue
Debug.Print curSymbol
Do While rst!Symbol = curSymbol
If rst!NotionalValue > 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.MovePrevious
*****Debug.Print curSymbol & " " & rst!NotionalValue & " " & rst!Symbol
End If
Loop
For i = 1 To (counter + 1)
rst.MoveNext
Debug.Print curSymbol & " " & rst!NotionalValue & " " & 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]