Error in midle of routine after it has worked for a few records

[SAC]

I'm adding records to a sql table using ODBC and several records add
successfully, but then in the middle of the code I get a "Record is Deleted"
error.


Here's the code idea:
Dim lngCounter as Long
Dim db as database
Dim rs as recordset
set db = currentdb
set rs = db.openrecordset("Orders", DbOpenDynaset, dbSeeChanges)
Do....

rs.addnew
lngCounter = lngCounter +1
rs!key = lngCounter
....
rs.update

rs.Movenext
loop

This occurs on a line between rs.addnew and rs.update.

I have the dbSeeChanges option set when I set the rs.

I'm incrementing a Long variable to set the Primary key field.

Any ideas?

Thanks.

 

[John Spencer]

Why are using rs.movenext when you are using rs.Addnew?

Normally if you are adding records in a loop, it would be

Do ...

rs.Addnew
lngCounter = lngCounter +1
rs!key = lngCounter
... 'set field values
rs.Update

Loop

 

[SAC]

Thanks.

I just forgot to put the rs.update line and There's a second recordset that
I'm reading the records from that I'm moving through.

Sorry for the confusion. I was not clear.

 

[SAC]

Here's most of the code:

Function TestTmp()
'On Error GoTo trap:
Dim MyError As Error
Dim intCounter As Long
Dim db As Database
Dim rs As Recordset
Dim rsQry As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTmpQuery3", dbOpenDynaset, dbSeeChanges)
Set rsQry = db.OpenRecordset("qrytblTmpQuery3OpenOrders", dbOpenDynaset,
dbSeeChanges)

rs.MoveFirst
rs.MoveLast
intCounter = rs!Key
'MsgBox (intCounter)
Do While Not rsQry.EOF
rs.AddNew
intCounter = intCounter + 1
rs!Key = intCounter
...set field values
rs.Update
rsQry.MoveNext
Loop

......

Thanks.

 

[John Spencer]

I don't see anything that would cause the error message you specified;
however, the error message may not be the correct error message even though
you are encountering an error.

Assumption:
Key is the primary key field
tblTempQuery3 is a table

Moving to the last record and grabbing its value may not give you the
largest (max) value in the table. So you could be attempting to assign a
duplicate value to the field key with your code. Why not use DMAX to grab
the largest value for rs!Key?

IntCounter = DMax("KEY","tblTmpQuery3")
and you might need to protect against there being no records in tblTmpQuery3

IntCounter =NZ( DMax("KEY","tblTmpQuery3") ,0)

The other option would be to use a query for rs that is based on the table
and order it by the Key field so as to put the maximum of the key field in
either the first record or the last record. Then you could grab the value
in the relevant record (first or last - depending on your sort order).

 

[SAC]

Thanks! I'll give DMax a try!

I don't see anything that would cause the error message you specified;
however, the error message may not be the correct error message even though
you are encountering an error.

Assumption:
Key is the primary key field
tblTempQuery3 is a table

Moving to the last record and grabbing its value may not give you the
largest (max) value in the table. So you could be attempting to assign a
duplicate value to the field key with your code. Why not use DMAX to grab
the largest value for rs!Key?

IntCounter = DMax("KEY","tblTmpQuery3")
and you might need to protect against there being no records in tblTmpQuery3

IntCounter =NZ( DMax("KEY","tblTmpQuery3") ,0)

The other option would be to use a query for rs that is based on the table
and order it by the Key field so as to put the maximum of the key field in
either the first record or the last record. Then you could grab the value
in the relevant record (first or last - depending on your sort order).