Add records to to 2 tables

[Wahab]

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

 

[Douglas J. Steele]

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.