Recordset Problem

W

Wahab

Hi everybody,
I writing following recordset, but everytime I'm getting error, actually not
working
can some one tell me pleaz whats wrong with this. Pleaz help me to fix this.

Dim StrSQL as String

StrSQL= " SELECT D.InvoiceNo, D.ProductID, D.ItemPrice, D.SaleAmount," & _
" D.SaleCopAmount FROM tCoptInvoices" & _
" INNER JOIN tCoptInvoicesDetails D ON tCoptInvoices.InvoiceNo =
D.InvoiceNo " & _
" WHERE Date Between # " & BeginningDate & " # And # " &
EndingDate & " # "

Set rst = CurrentDb.OpenRecordset(StrSQL)
Set rTarget = CurrentDb.OpenRecordset("tInvoicesDetails")

Do Until rst.EOF
rTarget.AddNew
rTarget!InvoiceNo = rst!InvoiceNo: rTarget!ProductID =
rst!ProductID
rTarget!Cartons = rst!Cartons: rTarget!ItemPrice =
rst!ItemPrice
rTarget!SaleAmount = rst!SaleAmount
rTarget.Update
rst.MoveNext
Loop
rst.Close
 
S

SteveS

I writing following recordset, but everytime I'm getting error, actually not


As far as I know, no one can see your error message and "not working"
doesn't help either.

Post the error message. What does "is not working" mean?

What have you tried to figure out the problem? Did you step thru the code?

On what line does the error occur?

Does the SQL (rst) return records? You don't check for rst.BOF or rst.EOF
before trying to add records.

Is this the whole Sub? It appears that you are missing some DIM statements.

Do you have "Option Explicit" at the top of the code?
 
S

Svetlana

Add a ; at the end of the sql statement and see if it works

StrSQL= " SELECT D.InvoiceNo, D.ProductID, D.ItemPrice, D.SaleAmount,"
& _
" D.SaleCopAmount FROM tCoptInvoices" & _
" INNER JOIN tCoptInvoicesDetails D ON
tCoptInvoices.InvoiceNo =
D.InvoiceNo " & _
" WHERE Date Between # " & BeginningDate & " # And # " &

EndingDate & " #;"
 
J

John Vinson

Hi everybody,
I writing following recordset, but everytime I'm getting error, actually not
working
can some one tell me pleaz whats wrong with this. Pleaz help me to fix this.

Dim StrSQL as String

StrSQL= " SELECT D.InvoiceNo, D.ProductID, D.ItemPrice, D.SaleAmount," & _
" D.SaleCopAmount FROM tCoptInvoices" & _
" INNER JOIN tCoptInvoicesDetails D ON tCoptInvoices.InvoiceNo =
D.InvoiceNo " & _
" WHERE Date Between # " & BeginningDate & " # And # " &
EndingDate & " # "

Set rst = CurrentDb.OpenRecordset(StrSQL)
Set rTarget = CurrentDb.OpenRecordset("tInvoicesDetails")

Do Until rst.EOF
rTarget.AddNew
rTarget!InvoiceNo = rst!InvoiceNo: rTarget!ProductID =
rst!ProductID
rTarget!Cartons = rst!Cartons: rTarget!ItemPrice =
rst!ItemPrice
rTarget!SaleAmount = rst!SaleAmount
rTarget.Update
rst.MoveNext
Loop
rst.Close

Some suggestions:

Be sure you have the DAO 3.6 library selected in Tools... References.
Include a line

Dim rst As DAO.Recordset

and (as suggested) be sure you have an Option Explicit set at the very
top of the module.

Trim out the extra spaces before and after the date delimiters:

" WHERE Date Between #" & BeginningDate & "# And #" &
EndingDate & "# "

And... yes... do give us a bit of help by indicating WHAT error
message you're getting and what "doesn't work".

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top