Getting an EOF?? in my data table - huh? Why?

[NateofMT]

The setup:

I have a Form that is attached to a Table.

I can enter information into the form and then go look at the table and see that the information is there (this is while the form is open).

When I close the form, I can go look at the table and see the information is still there. For example (note that this table has 82 fields in it, only 3 are being shown for reference):

Book1
	A	B	C	D
1	Job_Number	Name	Ticket_Number
2	1	John	1
3	2	Mary	2
4	3	Tom	3
Sheet1

Here is the problem:
When I open the form up, it shows no records. I open up the table, and it shows another line above the original records. For example:

Book1
	A	B	C	D
1	Job_Number	Name	Ticket_Number
2
3	1	John	1
4	2	Mary	2
5	3	Tom	3
Sheet1

My thought is that there is an EOF in there after the top record...here is why.

There is a routine that I run onLoad of the form:

Private Sub CleanupDB()

Dim RecClone As DAO.Recordset
Dim x As String
On Error GoTo exit_CleanupDB

Set RecClone = Me.RecordsetClone
RecClone.MoveFirst
Do Until RecClone.EOF
If RecClone("Name") = "" Or IsNull(RecClone("name")) Or RecClone("ticket_number") = "" Then
RecClone.Delete
End If
RecClone.MoveNext
Loop
RecClone.Close
Set RecClone = Nothing
'DoCmd.GoToRecord , , acLast, 1
Me.Form.Refresh

exit_CleanupDB:

End Sub

When it runs (and it doesn't make a difference whether or not it is ran, it still shows the same table info) it will go to the Do....Until and drop into it with the topmost record. It will run the procedure on it, then go to the Loop line and swing back up into the Do Until EOF. Then it drops out of the loop like it found an EOF and finishes the routine. It never scans through the other records in it.

Why is this? Help me please.

Thanks

 

[Norie]

Replace your loop with a while wend.

e.g.

While Not(RecClone.EOF)
If RecClone("Name") = "" Or IsNull(RecClone("name")) Or RecClone("ticket_number") = "" Then
RecClone.Delete
End If
RecClone.MoveNext
Wend

 

[NateofMT]

Thanks for the quick reply. However, it didn't make a difference as it is still adding the weird record to the top of the list, rather than at the * record line.

Just to clarify - this top record is getting added every time the form is opened, and if the form is opened several times, several records are being added, all at the top, not down on the bottom of the table.

 

[bat17]

Do you have any other code behind the form?
Does it only happen with this form?
Does it do the same if you base it on an intermediary query?

peter

 

[NateofMT]

Well the solution very much caught me off guard.

Here is what I did to try and fix it:

Rebuilt the data table from scratch
Spent hours stepping through the code
Spent equal amount of time rewriting code
Repeatedly comtemplated tracking down Bill Gates

None of which worked. My level of frustration was quite high - the obvious was not working - it simply wasn't the code as it was all commented out, and the data table wasn't it either, since it was a brand new table.

Here is what did work:

Created a new form based on the table
Copied all of the controls from the old form (CTRL A, CTRL C)
Cleared all controls off of the new form (CTRL A, Delete)
Pasted the Controls back into the new form (CTRL-V)
Copy the VBA Code from the old form into the new form

And it works. So it appears that the form itself was corrupted. I don't know why - all I know is that after sitting for 12 hours trying to figure out what went wrong with everything else, making the new form and copying in the old forms controls fixed it.

I definitely would be interested in anyone's opinion or expertise why this happened so.

Thanks for the suggestions and queries.