Recordset Problem

[Guest]

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

 

[Guest]

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

working

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?

 

[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 & " #;"

 

[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]