update after adding a record

  • Thread starter Thread starter Rover
  • Start date Start date
R

Rover

I have a form that reads a table (DynSetIN) and parses the data and
writes multiple records out to two keyed tables (DynSetOut1 and
DynSetOut2). The problem I'm having is: if I get a duplicate key error
(3022) I want to add the invoice amounts (the key is invoice number).
I'm not sure how to do that.

I know I should Edit then Update but in all my attempts is says "No
current record. Do I have to do this in multiple passes?

=======================
<snip>
Set dynSetIN = dbs.OpenRecordset(AcctRecSet, dbOpenDynaset)
Set dynSetOut = dbs.OpenRecordset("Invoice", dbOpenDynaset)
Set dynSetOutPay = dbs.OpenRecordset("Payments", dbOpenDynaset)

With dynSetOut
dynSetIN.MoveFirst

Do Until dynSetIN.EOF

If Not IsNull(dynSetIN![App# 1 date]) Then
.addNew
!InvoiceNo = dynSetIN![Inv# No# 1]
!invoicePrintDate = dynSetIN![App# 1 date]
!AppType = "app1"
!Amt = Nz(dynSetIN![Amount 1]) + Nz(dynSetIN![Bal 1])
'=== I'm missing the tax % and Tax amount
.Update ' Invoce table
end if
<snip>
=============================
 
Hi,

Well, I won't loop at all, in such case, I won't even open any
recordset. Do it directly:


dbs.Execute "INSERT INTO Invoice(InvoiceNo , invoicePrintDate,
AppType, Amt )
SELECT [Inv# No# 1], [App# 1 date], "app1",
Nz([Amount 1], 0)+Nz([Bal 1], 0)
FROM AcctRecSet; "


Not only that single line of code replaces all the lines you shown, but it
also run faster (touching the hard disk "once", rather than looping trying
to touch it again and again, without speaking of the time spent filling
recordsets structures... in memory).


Your actual error is that you miss a MoveNext, trying to add the same record
forever... Kind of error you don't have using an SQL approach, as suggested.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top