Recordset Based on Select Query

[Guest]

Hi
Im writing RS on Query to update records to new table but it gives me error,
can some one help me to write this recrod set pls.
dim db as DAO.Database
dim rst as DAO.Recordset
dim rTarget as DAO.Recordset

Set rst = CurrentDb.OpenRecordset("QryCustCurrTransactions",dbOpenDynaset)
Set rTarget = CurrentDb.OpenRecordset("LocalTB", dbOpenDynaset)

Do Until rst.EOF
rTarget.AddNew
rTarget!TrnDate = DateSerial(Year(rst!TrnDate),
Month(rst!TrnDate) + 1, 1 - 1)
rTarget!CustomerID = rst!CustomerID
rTarget!Debit = rst!DrKD
rTarget!Credit = rst!CrKD
rTarget.Update
rst.MoveNext
Loop
rst.Close
rTarget.Close


Thanks in advance

 

[John Spencer]

Try using the Db database object. I suspect that using
currentdb().OpenRecordset means that rst and rTarget are both going out of
scope as soon as the line has finished executing.

By the way, it is helpful to tell people the exact error you are getting and
on which line it occurs. Makes trouble-shooting a lot easier.

dim db as DAO.Database
dim rst as DAO.Recordset
dim rTarget as DAO.Recordset

Set db = CurrentDB() '<<< -----

'Note the change in the next two lines currentdb to db
Set rst = db.OpenRecordset("QryCustCurrTransactions",dbOpenDynaset)
Set rTarget = db.OpenRecordset("LocalTB", dbOpenDynaset)

Do Until rst.EOF
rTarget.AddNew
rTarget!TrnDate = DateSerial(Year(rst!TrnDate),
Month(rst!TrnDate) + 1, 1 - 1)
rTarget!CustomerID = rst!CustomerID
rTarget!Debit = rst!DrKD
rTarget!Credit = rst!CrKD
rTarget.Update
rst.MoveNext
Loop
rst.Close
rTarget.Close

I would think you could do the same thing with an append query and it would
be more efficient

Dim StrSQL

StrSQL = "Insert into LocalTb(TrnDate, CustomerID, Debit, Credit)" & _
" SELECT DateSerial(Year(TrnDate),Month(TrnDate),0)" & _
", CustomerID, DrKD, CrKD" & _
" FROM qryCustCurrTransactions"

CurrentDb().Execute StrSQL, DbFailonError


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

 

[Dirk Goldgar]

In

Hi
Im writing RS on Query to update records to new table but it gives me
error, can some one help me to write this recrod set pls.
dim db as DAO.Database
dim rst as DAO.Recordset
dim rTarget as DAO.Recordset

Set rst =
CurrentDb.OpenRecordset("QryCustCurrTransactions",dbOpenDynaset) Set
rTarget = CurrentDb.OpenRecordset("LocalTB", dbOpenDynaset)

Do Until rst.EOF
rTarget.AddNew
rTarget!TrnDate = DateSerial(Year(rst!TrnDate),
Month(rst!TrnDate) + 1, 1 - 1)
rTarget!CustomerID = rst!CustomerID
rTarget!Debit = rst!DrKD
rTarget!Credit = rst!CrKD
rTarget.Update
rst.MoveNext
Loop
rst.Close
rTarget.Close


Thanks in advance

What error number and message are you getting?