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

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



.

Relevant Pages

  • Re: How can I find, when overtime starts
    ... You can use a totals query to sum an employees hours across multiple records ... but its all those jobs that are giving me the problem. ... >> with values from a recordset and then written back to a recordset. ... >> calculate the total hours for all jobs for the employee for the week? ...
    (microsoft.public.access.queries)
  • Re: Tips on domain aggregate replacements
    ... In this case the amount of effect to obtain the account balance is effectively spread across each individual transaction as opposed to being all at once in a query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ... In fact, in my personal experience, it seems to be faster if I first create a recordset with no domain aggregates in it if I need a group of data. ... It will be based on a totals query, or possibly a query with a subquery. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Multiply qty of filtered records
    ... I do know how to get this information in a Report, ... the question is still open as to how a recordset can be ... I've played around with the Totals in the query design view but haven't been ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Recordset with SQL strings
    ... You can do this in a single query (if I'm understanding your question ... SELECT SumAS Total, EmployeeID ... first recordset by using the .Filter property of a recordset, ... transaction date in the Totals query, the totals are grouped by the date ...
    (microsoft.public.access.modulesdaovba)
  • Re: One more ! Calculating Recordset Values
    ... EOF means that the end of the RecordSet has been reached. ... > 'Total = The sum of all the SubTotals ...
    (microsoft.public.frontpage.programming)
Loading