Recirdset for unqual table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody
In my DB I have one table stockMaster table with fields Brand as text field
and closingStock as number field. Another table tSstockReport table with
only certain Brand ont complete list of brands, In this table I want write
brand's closingstock from table StockMaster, due to unequal records, records
set not work How I will tackle this my recordset is like this:
Set rst = CurrentDb.OpenRecordset("SELECT SUM(Closing) AS ClosingStock,
Brand FROM StockMaster GROUP BY BRD")

Set rTarget = CurrentDb.OpenRecordset("SELECT Brand, ClosingStock FROM
tSstockReport WHERE Brand = '" & rst!Brand & "' ")
If Not rTarget.EOF Then
rTarget.Edit
rTarget!closingstock = rst!closingstock
rTarget.Update
rst.MoveNext
Loop
rst.Close
rTarget.Close
End Sub

Please rectify the above so I can find tthe first records from tSstockReport
and get its ClosingStock Value from StockMater Table.

Thnaks in advance.
 
you had no loops! crazy... do you write this or is it mangled from
someone else's work? anyway, the fix is below. it should work as
intended now - but it's aircode, so exercise caution! and also, it's
total rubbish! I have spend years replacing stuff like this with
proper queries. this stuff is insanely slow and has rediculous over-
head. do yourself a favour and learn sql - you'll be glad you did.

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

if rst.eof then err.raise 1,,"no stock!"
rst.movefirst

do while not rst.eof

Set rTarget = CurrentDb.OpenRecordset("SELECT Brand, ClosingStock FROM
tSstockReport WHERE Brand = '" & rst!Brand & "' ")
If Not rTarget.EOF Then
rTarget.movefirst
rTarget.Edit
rTarget!closingstock = rst!closingstock
rTarget.Update
end if
rst.MoveNext

Loop

rst.Close
rTarget.Close

End Sub
 
Back
Top