Good Day
I have a continuous bound form to table ‘tSalesDetails’, normally there will
be 7 to 10 records for every Invoice. This form is working fine, adding
records to bound table. But for some reports I use to re-enter those sales in
another table (tSalesReports), this is duplicate job and I want to avoid this
by writing code I tried with the following but not adding to that table pls
check my code
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tSalesReports WHERE
InvoiceNo = " & InvoiceNo & "")
Do Until rst.EOF
If Not rst.EOF Then
rst.Edit
Else
rst.AddNew
End If
rst!InvoiceNo = InvoiceNo
rst!ProductCode=ProductID
rst!Cartons = Quantity
rst!ItemPrice = Price
rst!Sale = ProductSales
rst.Update
rst.MoveNext
Loop
rst.Close

Pls help me
thanks and regards
wahab

Why are you trying to store the data redundantly? Can't you just use a query
for your report?

If you have a legitimate reason (which I doubt), using a query will almost
always be signicantly faster than use VBA.

Dim strSQL As String

strSQL = "UPDATE tSalesDetails LEFT JOIN tSalesReport " & _
"ON tSalesDetails.InvoiceNo = tSalesReport.InvoiceNo " & _
"SET tSalesReport.InvoiceNo = tSalesDetails.InvoiceNo, " & _
"tSalesReport.ProductCode = tSalesDetails.ProductCode, " & _
"tSalesReport.Cartons = tSalesDetails.Quantity, " & _
"tSalesReport.ItemPrice = tSalesDetails.Price, " & _
"tSalesReport.Sale = tSalesDetails.ProductSales"

CurrentDb.Execute strSQL, dbFailOnError


That will add new records to tSalesDetails for invoices that don't already
exist, and update ones that do.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Wahab" <(E-Mail Removed)> wrote in message
newsE3E8E4E-6F83-4F9A-8FC4-(E-Mail Removed)...
> Good Day
> I have a continuous bound form to table 'tSalesDetails', normally there
> will
> be 7 to 10 records for every Invoice. This form is working fine, adding
> records to bound table. But for some reports I use to re-enter those sales
> in
> another table (tSalesReports), this is duplicate job and I want to avoid
> this
> by writing code I tried with the following but not adding to that table
> pls
> check my code
> Set rst = CurrentDb.OpenRecordset("SELECT * FROM tSalesReports WHERE
> InvoiceNo = " & InvoiceNo & "")
> Do Until rst.EOF
> If Not rst.EOF Then
> rst.Edit
> Else
> rst.AddNew
> End If
> rst!InvoiceNo = InvoiceNo
> rst!ProductCode=ProductID
> rst!Cartons = Quantity
> rst!ItemPrice = Price
> rst!Sale = ProductSales
> rst.Update
> rst.MoveNext
> Loop
> rst.Close
>
> Pls help me
> thanks and regards
> wahab