Object invalid or no longer set - aaah!

[4321z]

Hi i'm running the following sequence and i keep getting 'Object invalid or no longer set' coming up at the points indicated, for some reason access keeps forgetting that the MyCurrentValues table is open, or am i just missing something really obvious here....

Set MyDatabase = DBEngine.Workspaces(0).Databases(0)

'********************Delete values in current table***********************************
Set MyCurrentValues = MyDatabase.OpenRecordset("tblCurrentYr", dbOpenTable)
If MyCurrentValues.RecordCount > 0 Then

MyCurrentValues.MoveFirst
Do Until MyCurrentValues.EOF
This deletes 6400 records
MyCurrentValues.Delete
MyCurrentValues.MoveNext
Loop
End If
'********************Delete values in current table*************************************

'********************Copy Previous years values to current table************************
Set MyPreviousYearValuesValues = MyDatabase.OpenRecordset("tblPreviousYr", dbOpenTable)
If MyPreviousYearValuesValues.RecordCount > 0 Then
MyPreviousYearValuesValues.MoveFirst
Do Until MyPreviousYearValuesValues.EOF

MyCurrentValues.AddNew
copy previous values to new values 76 fields per record for 6400 records in total
MyCurrentValues.Update THIS IS WHERE THE PROBLEM OCCURS
MyPreviousYearValuesValues.MoveNext
Loop
End If

MyPreviousYearValuesValues.Close
'********************Copy Previous years values to current table*************************

'********************Copy Current Source data values to current table********************
Set MySourceDataTable = MyDatabase.OpenRecordset("tblDHSourceData", dbOpenTable)
If MyCurrentValues.RecordCount > 0 Then
MyCurrentValues.MoveFirst
Do Until MyCurrentValues.EOF
If MySourceDataTable.RecordCount > 0 Then
MySourceDataTable.MoveFirst
Do Until MySourceDataTable.EOF
If MySourceDataTable![UPRN] = MyCurrentValues![UPRN] Then
MyCurrentValues.Edit
edit 15 fields per record, 6400 records
MyCurrentValues.Update THIS IS WHERE THE PROBLEM OCCURS
Exit Do
End If
MySourceDataTable.MoveNext
Loop
End If
MyCurrentValues.MoveNext
Loop
End If
MySourceDataTable.Close
MyCurrentValues.Close
MyDatabase.Close

 

[KenReay]

Hi


MyCurrentValues.AddNew <--POint A
copy previous values to new values 76 fields per record for 6400 records in total
MyCurrentValues.Update <-- Point B THIS IS WHERE THE PROBLEM OCCURS

If MyCurrentVAlues has a value at point A, but not at POint B (See above), then it msut be being inset in the code "copy previous values to new values 76 fields per record for 6400 records in total" which you do not show us

By the way, this is a very slow way to do a delete:

MyCurrentValues.MoveFirst
Do Until MyCurrentValues.EOF
This deletes 6400 records
MyCurrentValues.Delete
MyCurrentValues.MoveNext
Loop

DoCmd.RunSQL "DELETE FROM YourTAbleName;"

would be much better

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK