Something Wrong with SQL

[Guest]

Hi everybody
I have two sqls which are as

Set rst = CurrentDb.OpenRecordset("SELECT SUM(Closing) AS ClosingStock, BRD
AS Brand FROM " & txtTableName & " GROUP BY BRD")

above sql bringing correct record, 42 records with the grouping and suming
of closing Stock I tested in immediate winodw.

2nd sql is

Set rTarget = CurrentDb.OpenRecordset("SELECT * FROM tReportGenerating WHERE
Brand = ' " & rst!Brand & " ' ")

Do Until rst.EOF
rTarget.Edit
rTarget!ClosingStock = rst!ClosingStock
rTarget.Update
rst.MoveNext
Loop
rst.Close
rTarget.Close

Im getting error no. 3021
pls tell me where im doing wrong
thanks

 

[John Spencer]

One thing I see is that you are probably not returning any records with
your rTarget query since it is looking for Space Brand Space.

So when you attempt to edit rTarget you have no record to edit.

Set rTarget = CurrentDb.OpenRecordset("SELECT * FROM tReportGenerating
WHERE Brand = '" & rst!Brand & "' ")


That aside, you are looping through rst and attempting to assign that
value to the same record in rTarget.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks Mr. Jhon it works but still it is not taking all records and writing
to target table, only last records is updating can you tell me why?

once again, realy thanks actually l loose the hope on this one.
Wahab

 

[John Spencer]

Your problem is that you are looping through the rst and setting the
value to the same record.

I think what you might be attempting to do is to do a loop within a
loop. However, what you seem to be doing is not a good idea. You are
calculating a value that you can calculate at any time you need it.
Usually this is a bad idea. If you can calculate the value, then
calculate it when you need it, so it is always current.

However, there are times when you need to pre-calculate values. If you
are attempting to do what I think you are attempting, I hope the
INCOMPLETE, UNTESTED VBA code that follows will help you.

BACKUP your data first, as if this code runs successfully there will be
no way to undo the change in tReportGenerating

Dim rst As DAO.Recordset
Dim rTarget as DAO.Recordset
Dim txtTableName as String '<<< ???

....

Set rst = CurrentDb.OpenRecordset("SELECT SUM(Closing) AS ClosingStock,
BRD AS Brand FROM " & txtTableName & " GROUP BY BRD")

Do Until rst.EOF
'For the current rst!Brand get records in rTarget
Set rTarget = CurrentDb.OpenRecordset("SELECT * FROM" & _
" tReportGenerating WHERE Brand = '" & rst!Brand & "' ")

Do Until rTarget.EOF
rTarget.Edit
rTarget!ClosingStock = rst!ClosingStock
rTarget.Update
rTarget.MoveNext 'Move to the next record in rTarget
Loop 'Loop through all the records with brand

rst.MoveNext 'Move to the next record in rst
Loop
rst.Close
rTarget.Close

As I said, doing the above is probably NOT a good idea

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks; it works, exactly waht I want, I got. This DB is not mine, for whom
I m doing, he is Bring 40 to 50 tables from Excel and he dont want to change
anything. This is the reason Im calculating in RS.