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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Graham.

I'll need time to study your coding.

Thanks for doing this. I'll let you know if it will work.

Thanks,
Tony

"argusy" <argusy@xxxxxxxxxxxxxxx> wrote in message
news:13pve5qeoi01a3a@xxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Update fields in previous row from fields in current row
    ... 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. ... After reading up on Bookmarks, i'm still not sure of how to code this ... Dim vLastValue As Variant 'only because I don't know its type ... Dim strGroup as string ...
    (microsoft.public.vb.database.ado)
  • Re: Update fields in previous row from fields in current row
    ... grouped, too, and you don't need the DISTINCT query modifier, either.<< ... I'm not after totals. ... I'm just trying to put those totals on the first row of the group and ... I THINK I can see how to use .bookmark, and it would require bookmarking twice. ...
    (microsoft.public.vb.database.ado)