ERROR: Record is deleted

[Maracay]

Hi guys,

The code below is giving me an error “Record is deletedâ€, first I generated
the data in the table tblTwoUP from tblMainData, this part is OK, the problem
is when I try to generate a TXT file, reading the table I just created, if
someone have a idea of the problem I will appreciated.

Thanks

Function TwoUP()
strFileName = Me!textLocationNameFileeXP
FileHandle = FreeFile()
Open strFileName For Output As FileHandle
On Error GoTo Err_handler
Dim db As DAO.Database
Dim rs As Recordset
Dim rsc As DAO.Recordset

Dim stDocName As String 'Errors Report
Dim nFirstHalf As Integer
Dim nSecondHalf As Integer
Dim nIndex As Integer

nFirstHalf = 1
nSecondHalf = Int(DCount("*", "tblMainData") / 2) + 1

nIndex = 0

Set rs = Me.RecordsetClone
Set db = CurrentDb

Set rsc = db.OpenRecordset("tblTwoUP", dbOpenDynaset)
CurrentDb.Execute "DELETE FROM tblTwoUP", dbFailOnError

nIndex = 1
IndexHalf = nSecondHalf
rs.MoveFirst
Do Until rs.EOF

rsc.AddNew
If rs!indx < nSecondHalf Then
rsc!indx = nIndex
Else
rsc!indx = nIndexHalf
End If
rsc!listorder = rs!listorder
rsc!Fname = rs!Fname
rsc!LName = rs!LName
rsc!Title = rs!Title
rsc!Company = rs!Company
rsc.Update

Print #FileHandle, strLine
rs.MoveNext

If Not rs.EOF Then
If rs!indx < nSecondHalf Then
nIndex = nIndex + 2
Else
nIndexHalf = nIndexHalf + 2
End If
End If
Loop

rsc.MoveFirst
Do Until rsc.EOF
strLine = Left(Mid((" " + rsc!Listorder), 2) & Space(40), 40) & _
Left(Mid((" " + rsc!Fname), 2) & Space(40), 40) & _
Left(Mid((" " + rsc!Lname), 2) & Space(40), 40) & _
Left(Mid((" " + rsc!Title), 2) & Space(60), 60) & _
Left(Mid((" " + rsc!company), 2) & Space(60), 60)
Print #FileHandle, strLine
' MsgBox rsc!Fname
rsc.MoveNext
Loop


MsgBox ".2UP file was generated "

TwoUP = True

sExit:
Close #FileHandle
rsc.Close
Set rsc = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Err_handler:
MsgBox Err.Description
Resume sExit

End Function

 

[Douglas J. Steele]

Try reversing the order of the statements

Set rsc = db.OpenRecordset("tblTwoUP", dbOpenDynaset)
CurrentDb.Execute "DELETE FROM tblTwoUP", dbFailOnError

What exactly are you trying to do with the value of indx? It's seldom a good
idea to loop through a recordset to populate a table.