If .eof not excecuting

[Alpf]

Hi Everyone,

I am reading through a recordset to delte all the records.

The code that tests for EOF doesn't execute.
When I use debug it shows the EOF to be true.

Thanks.

Set HDRdb = CurrentDb
Set HDRtdf = HDRdb.TableDefs("Delete_Table")
Set HDRrst = HDRdb.OpenRecordset("Delete_Table")
With HDRrst
 .MoveFirst
 .Edit
 MsgBox (!Name)
 .Delete
End With
Do_until_loop:
 Do Until HDRrst.EOF
 If HDRrst.EOF = True Then
    GoTo Records_Deleted
    End If
 With HDRrst
 .MoveNext
 MsgBox (!Name)
 .Delete
If HDRrst.EOF = True Then
    GoTo Records_Deleted
    End If
End With
Loop
Records_Deleted:
MsgBox ("All Records Deleted")

 

[theDBguy]

Hi. Welcome to AWF! Did you try stepping through the code?

 

[Alpf]

Yes. In debug show the EOF to be true but it goes to ENDIF and doesn't excecute the next next statment.

 

[theDBguy]

Yes. In debug show the EOF to be true but it goes to ENDIF and doesn't excecute the next next statment.

Hi. Which "next" statement would that be?

 

[Alpf]

GoTo Records_Deleted

 

[pbaldy]

FYI I added code tags to your post. You don't need any of that, just:

CurrentDb.Execute "DELETE * FROM Delete_Table", dbFailOnError

unless you actually need the message boxes. I'd be more likely to provide a report with the records before deleting. A user might get tired of clicking OK on message boxes.

 

[Alpf]

Thank you I will try it. The msgbox was only used fir debugging.

Thanks again.

 

[pbaldy]

No problem, post back if you get stuck.

 

[gemma-the-husky]

I only use while/wend.
I would have this sort of code.

set hdrst = openrecordset (whatever)

while not HDRrst.EOF  'this won't execute if there are no records in the recordset.
  With HDRrst
      .Delete  'not sure about this syntax
      .movenext
      'increment a counter
   end with
wend

MsgBox (counter & "Records Deleted")

 

[pbaldy]

I would have this sort of code.

You would delete all records via a recordset loop rather than SQL?

 

[gemma-the-husky]

@paul.
I would do what you suggested, but its worth knowing the syntax to iterate a recoordset, depending on exactly what you want to do.