Transfering data from recordset to recordset question

[What-a-Tool]

I have a table which tracks steel inventory.
Steel is categorized by width and thickness, and tracks the total weight in
stock.

Sometimes steel is entered into the table as single rolls in stock, and
sometimes by total delivery weight. Once it is into the table I group it all
together and Sum the weight field.

I do this by creating a recordset from a SUM query of the table, and also
create another recordset of the contents of the table.

I loop thru the table contents recordset, deleting all items, then I AddNew
items to the list, which I populate with the data from the SUM recordset in
another loop.

This works fine - but there is so much about recordsets that I am un-aware
of, I figure there must be an easier way to copy the data from the SUM
recordset into the table recordset.

Help?


--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous

 

[Douglas J. Steele]

I'm not sure I understand why you're trying to do what you're asking to do.

It doesn't sound appropriate to me to add the Sum information to the Detail
information.

It might be more appropriate to change the query you're using to populate
the recordset. (FWIW, if it can be done in SQL, it's almost always more
efficient than looping through recordsets)

 

[What-a-Tool]

I'm not adding the Summed data to the detailed, I'm replacing.

I want to know if there is an easier way to move all data from one recordset
into another empty one.

(Looking at this post, I don't know why I went thru that long convoluted
description - Sorry

Thanks

--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous

 

[Douglas J. Steele]

Again, why?

Is there some reason you have to repopulate the recordset, or can you just
create a new recordset?

 

[What-a-Tool]

I could create a new recordset, if I can poppulate my now empty table with
it, or if I could just take my already existing recordset (the Summed one)
and connect it to my table to update?

Opened that SUM query recordset with:

Qry = "SELECT tblSteelInStock.[SteelID], Sum(tblSteelInStock.[Weight]) AS
SumOfWeight " & _
"FROM tblSteelInStock " & _
"GROUP BY tblSteelInStock.[SteelID] " & _
"ORDER BY tblSteelInStock.[SteelID],
Sum(tblSteelInStock.[Weight]);"

rst.Open QRY, con, adOpenKeyset, adLockReadOnly

-which is I thought the way to execute a query string that will display my
table data in a modified format, and which also I thought, makes it non
updateable back to the table.

After I populate this recordset, I clear the table, and refill the table
with this summed data.


--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous

 

[Douglas J. Steele]

I'm not convinced that really makes sense. I'd keep the details, and have a
query that uses the same SQL as in your SUM recordset. Use that query
instead of the table when you need the totals.

However, if you're determined to do it, I know the following DAO code works
('cause I just tested it):

Dim dbCurr As DAO.Database
Dim rsRaw As DAO.Recordset
Dim rsSum As DAO.Recordset
Dim strSQL as String

strSQL = "SELECT [SteelID], Sum([Weight]) AS SumOfWeight " & _
"FROM tblSteelInStock " & _
"GROUP BY [SteelID] " & _
"ORDER BY [SteelID], Sum([Weight]);"

Set dbCurr = CurrentDb()
Set rsSum = dbCurr.OpenRecordset(strSQL)
dbCurr.Execute "DELETE FROM tblSteelInStock", dbFailOnError

Set dbRaw = dbCurr.OpenRecordset(SELECT [SteelID], [Weight] FROM
tblSteelInStock")

With rsSum
.MoveFirst
Do While .EOF = False
rsRaw.AddNew
rsRaw!SteelID = !SteelID
rsRaw!Weight = !SumOfWeight
rsRaw.Update
.MoveNext
Loop
End With

(I never use ADO with Jet databases, so I didn't bother testing whether the
equivalent will work using ADO)

 

[What-a-Tool]

Thank you - that's actually not a whole lot different from what I have - was
hoping that maybe there was a simpler way of doing it that I didn't know
about. And now I know the DAO method.
Thank you for spending the time though.

(I never use ADO with Jet databases, so I didn't bother testing whether the
equivalent will work using ADO)

I'm curious of the reason for this - personal preference, or functionality
issue?

I'm not convinced that really makes sense. I'd keep the details, and have a
query that uses the same SQL as in your SUM recordset. Use that query
instead of the table when you need the totals.

I originally designed this to keep track of steel inventory by keeping track
of each individual roll of steel. Would have been a much more accurate way
than I am currently using if, as a roll was used, it was removed from the
inventory list.
But, because of communication issues (machine operator only speaks Spanish),
and the fact that the machine operator who would have to keep track of this
really doesn't care, this method just wasn't working.

So now I calculate how much steel should have been used, by knowing the
number of pieces that were produced, and automatically subtract this amount
from the Summed steel inventory list as a part/ order is closed. Keeps us in
the ballpark, anyway.

I could add each roll weight directly to the total weight in stock as it is
input, but prefer to be able see what has been entered individually before
adding it to the total with a button click.

Once again - thanks for taking the time to help.


--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous

I'm not convinced that really makes sense. I'd keep the details, and have
a query that uses the same SQL as in your SUM recordset. Use that query
instead of the table when you need the totals.

However, if you're determined to do it, I know the following DAO code
works ('cause I just tested it):

Dim dbCurr As DAO.Database
Dim rsRaw As DAO.Recordset
Dim rsSum As DAO.Recordset
Dim strSQL as String

strSQL = "SELECT [SteelID], Sum([Weight]) AS SumOfWeight " & _
"FROM tblSteelInStock " & _
"GROUP BY [SteelID] " & _
"ORDER BY [SteelID], Sum([Weight]);"

Set dbCurr = CurrentDb()
Set rsSum = dbCurr.OpenRecordset(strSQL)
dbCurr.Execute "DELETE FROM tblSteelInStock", dbFailOnError

Set dbRaw = dbCurr.OpenRecordset(SELECT [SteelID], [Weight] FROM
tblSteelInStock")

With rsSum
.MoveFirst
Do While .EOF = False
rsRaw.AddNew
rsRaw!SteelID = !SteelID
rsRaw!Weight = !SumOfWeight
rsRaw.Update
.MoveNext
Loop
End With

(I never use ADO with Jet databases, so I didn't bother testing whether
the equivalent will work using ADO)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I could create a new recordset, if I can poppulate my now empty table with
it, or if I could just take my already existing recordset (the Summed one)
and connect it to my table to update?

Opened that SUM query recordset with:

Qry = "SELECT tblSteelInStock.[SteelID], Sum(tblSteelInStock.[Weight]) AS
SumOfWeight " & _
"FROM tblSteelInStock " & _
"GROUP BY tblSteelInStock.[SteelID] " & _
"ORDER BY tblSteelInStock.[SteelID],
Sum(tblSteelInStock.[Weight]);"

rst.Open QRY, con, adOpenKeyset, adLockReadOnly

-which is I thought the way to execute a query string that will display
my table data in a modified format, and which also I thought, makes it
non updateable back to the table.

After I populate this recordset, I clear the table, and refill the table
with this summed data.

 

[Douglas J. Steele]

I'm curious of the reason for this - personal preference, or functionality
issue?

DAO was developed specifically for Jet, whereas ADO is a generic approach.
That means you have to go through more layers of abstraction using ADO, so
that DAO will almost always be faster.

You might also be interested in reading what MichKa has to say on the topic
at http://www.trigeminal.com/usenet/usenet025.asp (entitled "What does DAO
have that ADO/ADOx/JRO do not have (and might never have!)")

Besides, ADO has been deprecated by ADO.Net

 

[What-a-Tool]

Thanks - I had learned that ADO was the way to go
Interesting

--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous