DELETE in SQL seems to leave empty rows

[Laurel]

I execute this statement in the form_open event. After it is executed, if I
look at the table, it appears to have no records. But instead of having no
rows in my form, I have multiple rows with "#Deleted" in all of the fields.
The number of rows displayed matches the number of rows that had been last
created. How can I get my form not to display these spurious rows when it
is first opened and loaded?

CurrentDb.Execute "DELETE * FROM tblClassSummaryTemp;"

 

[Randy Harris]

I execute this statement in the form_open event. After it is executed, if I
look at the table, it appears to have no records. But instead of having no
rows in my form, I have multiple rows with "#Deleted" in all of the fields.
The number of rows displayed matches the number of rows that had been last
created. How can I get my form not to display these spurious rows when it
is first opened and loaded?

CurrentDb.Execute "DELETE * FROM tblClassSummaryTemp;"

Add this after the DELETE:

Me.Requery

 

[Van T. Dinh]

Randy already suggested the immediate solution but I think it may be more
efficient to delete the Records FIRST before you open the Form. I presume
that you use code to open the Form then you should move the deletion to
before the OpenForm command.

 

[Laurel]

It seems, then, that the form is actually loading in the form_Open event....
that it doesn't wait till the form_Load event? True?

 

[Randy Harris]

It seems, then, that the form is actually loading in the form_Open event....
that it doesn't wait till the form_Load event? True?

For a Form there isn't much difference between the Open and Load events.
Open happens first, of course, but the biggest difference is that the Open
event can be cancelled. So, if you wish to open a Form conditionally, the
code should go in the Open procedure.

For a Form, the underlying recordset is constructed before either Open or
Load occurs. That is the reason that Van suggested that you delete the
unwanted records before issuing the OpenForm command. If it is done in the
Open procedure, you run the query, then delete the records, that's why you
see the "#Deleted". You would need to then Requery to update the data
displayed on the Form. It works, but is rather inneffiecient.

Randy