Record is deleted

[John Sh]

I have a simple table that acquires a few records during the operation of a module.
After acquiring 4 or 5 records this table is read to indicate where action is required.
My problem is that I am getting an error indicating the "record is deleted."
Prior to running the module I delete all records with the delete * sql string, for obvious reasons, but this seems to be the root cause of my dilemma.
If you run the sample db as supplied you will see the error.
Comment out the first msgbox and you get the desired result.
I have used this construct many times without any problems so what is different this time.
Obviously closing and reopening the recordset is a viable workaround but why now, why this time?
In the working module this recordset, along with a couple of others, is accessed in a number of procedures, hence the private declarations.

 

[tvanstiphout]

It's a really bad idea to have a table without a Primary Key. However, just adding one does not fix the problem.
Moving the line "Set rs1 = db.OpenRecordset("listtables", dbOpenDynaset)" to below "DoCmd.SetWarnings True" does solve the original problem.
You then have a new problem: Error 3021 = No current record.
This is because after .Update the record pointer is not necessarily on the record that was just inserted. You can prove that by executing this in the Immediate window:
?rs1.EOF
True
If you want the value of the just-inserted record you have to move to it:
rs1.Move 0, rs1.LastModified

 

[Mike Krailo]

Yep, a simple rs1.MoveFirst just before the first msgbox is what is needed to get the desired results. @tvanstiphout explained it well already.

 

[John Sh]

moving the line "Set rs1 = db.OpenRecordset("listtables", dbOpenDynaset)" to below "DoCmd.SetWarnings True" does solve the original problem.
You then have a new problem: Error 3021 = No current record.

And that is where the original problem started.
I would, traditionally, delete before opening the recordset, but when it came to a .movefirst in the code, is when that error appeared so I have been wrestling with various configurations to try and solve this. The sample I posted was the last attempt at a cure.
Ultimately closing and reopening the recordset seems to be the easiest way to correct the problem.

 

[Mike Krailo]

And that is where the original problem started.
I would, traditionally, delete before opening the recordset, but when it came to a .movefirst in the code, is when that error appeared so I have been wrestling with various configurations to try and solve this. The sample I posted was the last attempt at a cure.
Ultimately closing and reopening the recordset seems to be the easiest way to correct the problem.

Makes no sense at all. There is no problem at all in your sample you provided, just that you tried to display the current record while it was at the EOF. Why are you closing and opening the record set?

 

[John Sh]

Makes no sense at all. There is no problem at all in your sample you provided, just that you tried to display the current record while it was at the EOF. Why are you closing and opening the record set?

It depends entirely on where you place the "set rs1".
If it is placed before the delete * you will have to close and reopen the recordset. A .move first or last will throw an error.
Incorporating the "rs1.Move 0, rs1.LastModified" construct will allow the last record to be seen but a subsequent .movefirst still throws an error 3167 "Record is deleted"
If it is placed after the delete * then, as you say, a .move will allow the table to be read normally.

Try shifting the comment from before the delete to after and the step through the if statement.

 

[Mike Krailo]

If you look at each time you get the error carefully, you will see that you are always on a record that doesn't contain any data. You force the current record to record zero which has no data in it and you get the record deleted error. Simply move to a record that has data, any record, and you won't get that error.

Try moving to the next record at the place where the error occurs and you will see that record 1 does have data and won't throw the error.

To avoid confusion about that move command.

Moves the position of the current record in a Recordset object.
Syntax
recordset.Move rows, start
The Move method syntax has these parts.
Part Description recordset An object variable that represents the Recordset object whose current record position is being moved. rows A signed Long value specifying the number of rows the position will move. If rows is greater than 0, the position is moved forward (toward the end of the file). If rows is less than 0, the position is moved backward (toward the beginning of the file). startbookmark Optional. A Variant (String subtype) value identifying a bookmark. If you specify startbookmark, the move begins relative to this bookmark. Otherwise, Move begins from the current record.

So rs1.move 0 doesn't move anywhere, unless that second optional argument for the bookmark object is used. But in this case, it does not do as you expected, because the lastmodifed record is no record at all. It's confusing for sure, but if you rs1.MoveFirst that first record has no data or anything in it. Moving forward one record rs1.MoveNext gets to the actual record you expected showing "new record number 1" in the msgbox. Moving next again gets the real record 2 "new record number 2", etc...

 

[Mike Krailo]

I have used this construct many times without any problems so what is different this time.
Obviously closing and reopening the recordset is a viable workaround but why now, why this time?

Please take a look at the reworked demo code that you provided. Hopefully, you will see why this was happening if your still interested. I'm sure you decided to just use the workaround, but if you want to know why it was throwing the error in question. Take a look at it. I changed the events to buttons so you can test both conditions easily and see that no error occurs either way you described. The error handler takes care of the problem in either case.