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

Tony Girgenti wrote:
Hello Ralph.

Maybe if i explain what i'm trying to do with the data, you can tell me what to do.

The rows in the recordset contain lines of inventory items. Each group of items has a totals line at the end of the group in a separate row. There can be many groups and a group can have as few as one item, so each group will have at least two rows.

I want to find the total line for each group, take the totals fields for each group and put those totals on the first row of the group, delete the totals line.

Each row in the recordset has all the fields that are in both the items rows and the totals rows, so i don't have to create new fields in the target row.

Thanks for all of your help.
Tony

"Ralph" wrote:

"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




I THINK I can see how to use .bookmark, and it would require bookmarking twice.
Once to pick up the record to be updated in a group, and a second to go back to the record that flagged the group had changed. (so the recordset progression could be continued). Never having used bookmarking, I wouldn't know

Tony, what you need to do is pick on just one record in the group to HAVE a total. That way you don't need to add records, and only works if all records in a group have the total cleared each time your program is run.

In some other area of your program, I guess you're after totals on the data. Just set a query to pick records that have the total greater than zero. Already grouped, too, and you don't need the DISTINCT query modifier, either.

You wouldn't need bookmarking

ie

(reduced your recordset name for clarity)

WARNING, WARNING, WILL ROBINSON - AIR CODE

Dim sngTotal as single
Dim strGroup as string

rs.Open ' assuming you're setting the groups in the query

rs.MoveFirst

strGroup = rs.fields("group") 'set the fist group name

Do Until rs.EOF

If rs.fields("group") = strGroup then
If rs.Fields("CASNumber").Value <> "" Then
strTotal = strTotal + rs.Fields("CASNumber").Value
Endif
else ' whoops - a new group, better go back and set total
rs.moveprevious
rs.Fields("total").Value = sngTotal
rs.update
sngTotal = 0
rs.movenext ' back to record with new group
strGroup = rs.fields("group") 'get group name change
End If
If rs.Fields("total").Value > 0 then 'there's an unwanted total
rs.Fields("total").Value = 0 'clear total in current record
rs.update
endif
rs.MoveNext
Loop

The above will set ONLY one record with a total, so you don't have to worry about which one has it, or adding records to save your totals.

Graham

.

Loading