Compare Recordsets--Basic Code 1

[pvsmith5]

I have a basic recordset coding problem that is stumping me. I want to flip a SELECT flag whenever the IDNUMBER changes, but this would be the last date associated with that IDNUMBER. Here's what I've started:

Dim Dbs As DAO.Database
Dim rst As DAO.Recordset

Set Dbs = CurrentDb()
Set rst = Dbs.OpenRecordset("tblStudent_Summary", dbOpenDynaset)
rst.MoveFirst

Do While Not rst.EOF
rst.Sort = "IDNUMBER, DATE asc"
'For Each rst In .Recordsets
With rst
If rst![IDNUMBER] <> rst![IDNUMBER] Then
rst![Select] = -1
Else
End If
End With

rst.MoveNext
Loop

Set rst = Nothing
Set Dbs = Nothing

End Sub

But where the problem is that I don't know how to declare the previous IDNUMBER and the currentIDNUMBER in the code. I think that I'm only a line or two away from getting this to work. Sure would appreciate your help.

 

[RoyVidar]

Just a typed up attempt with no testing at all, just illustrating some issues - Don't use reserved words as names of objects, fields etc. Select is a SQL statement/clause, Date is a function available also within queries.

To update DAO recordsets, you'll need to invoke the .edit method, and, as far as I recall, do an explicit .update.

This is an attempt at "moving back" to the "original" value, and alter the "select" field for that - again, air code, no testing, just a typeup ...

[tt] dim lngIdNumber as long
Set rst = Dbs.OpenRecordset( _
"select IDNUMBER, [DATE], [select] " & _
"from tblStudent_Summary " & _
"order by IDNUMBER, [DATE]", dbOpenDynaset)

if rst.recordcount>0 then
with rst
.MoveFirst
lngIdNumber = .fields("IDNUMBER").value
.movenext
Do While Not .EOF
If .fields("IDNUMBER").value <> lngIdNumber Then
.moveprevious
.edit
.fields("Select").value = -1
.update
.movenext
lngIdNumber = .fields("IDNUMBER").value
End If
.movenext
Loop
.close
end with
end if
Set rst = Nothing
Set Dbs = Nothing[/tt]

Roy-Vidar

 

[pvsmith5]

Roy-Vidar--Works like a charm. Thank you for ending my two days of research and debugging. This is really a clean piece of code.