Re: Update fields in previous row from fields in current row


"Tony Girgenti" <tony(nospam)@lakesideos.com> wrote in message
news:uUAKLAoYIHA.4696@xxxxxxxxxxxxxxxxxxxxxxx
Hello Ralph.

After reading up on Bookmarks, i'm still not sure of how to code this
problem. If i do it this way, i get an object required error on the
statement after the If. I can't figure out how to refer to the bookmark's
fields.

Dim rsMark As Variant
casInputFileRecordset.Open
casInputFileRecordset.MoveFirst
rsMark = casInputFileRecordset.Bookmark

Do Until casInputFileRecordset.EOF
If casInputFileRecordset.Fields("CASNumber").Value <> "" Then
rsMark.Fields("CASNumber").Value =
casInputFileRecordset.Fields("CASNumber").Value
End If
casInputFileRecordset.MoveNext
rsMark = casInputFileRecordset.Bookmark
Loop



First off, the last two lines in your code ...
casInputFileRecordset.MoveNext
rsMark = casInputFileRecordset.Bookmark
Captures the bookmark for the 'next' record. Not the one you are chewing on.

I really don't know if you mean to use the "last" row or some previous row
of interest. (Which is important because one can never assume the exact
order of records in a recordset unless you specifically request one.) That
is the only reason I suggested that you take a look at "Bookmarks". It was
probably an unnecessary distraction.

In the example above you could do this ...
[Warning! Air Code follows]
Dim vLastValue As Variant 'only because I don't know its type
casInputFileRecordset.Open
casInputFileRecordset.MoveFirst

' have to have some kind of default value for the first one
' vLastValue = <default>
Do Until casInputFileRecordset.EOF
If casInputFileRecordset.Fields("CASNumber").Value <> "" Then
casInputFileRecordset.AddNew
casInputFileRecordset.Fields("CASNumber").Value = vLastValue
casInputFileRecordset.Update
Else
vLastValue = casInputFileRecordset.Fields("CASNumber").Value
End If
casInputFileRecordset.MoveNext
Loop


.

Loading