ListBox doesn't refresh

[Robert]

I have a list box (lstCustomers) that displays records
from a table (rowsource = select * from table1).

I have a command button (Delete) that has code to delete a
record from Table1.

I then issued a "lstCustomers.Requery" to "refresh" the
data in the listbox, however, it doesn't "refresh" right
away.

I have create two additional command buttons, Refresh and
Requery, that "me.refresh" and "lstCustomers.Requery"
respectively.

If I wait 2-3 seconds after my original "requery" and then
either use the Refresh or Requery button, the listbox is
then displayed correctly.

I have placed code in the program to determine the number
of rows (lstCustomers.ListCount) that are returned after
the original "requery" and the number of rows is correct,
it just doesn't get displayed correctly.

Any suggestions. This is Access 2002.

 

[Robert]

After further review...

After the record in Table1 has been deleted (using a
recordset - which is updated and then closed), I "requery"
the list box (lstCustomers.requery) and it returns one too
many records (lstCustomer.ListCount). I open a new
recordset just after the "requery" and the record set
returns the correct number of records.

If I wait the additional 2-3 seconds, the "requery"
returns the correct number of records.

 

[Kelvin]

You have a timing problem. What code are you using to perform the delete?
If you are using DoCmd.OpenQuery, that would explain the timing problem.
DoCmd does not wait for the query to actually finish before going to the
next line of code. You should use the exceute method instead. This will
pause the program until the code is finished before going on.

Kelvin

 

[SteveS]

Robert,

Would you mind a few questions?

1 Since you are using ADO, you must have AC2K or XP?

2 How many fields in the table where the record is being
deleted?

3 How many records in the table?

4 Is the form that has lstCustomer bound or unbound?

5 Is it necessary to set the record source for lstCustomer
to select all fields? From what I have read, list boxes
and combo boxes (same animal) are really slow when there
are a lot of fields and records. If the recordsource is
just two fields (primary key and an second field - ), it
is could be enough info to be able to find the record to
be deleted.

--
If the form is a bound form (first recordset), is there a
reason why you have to open a second recordset, delete the
record, close it and open a third recordset?

Could you use something like...:
<snip>
' if bound column is a string
strSQL = "Delete from table where field = '" &
Me.lstCustomer.selected & "';"

CurrentDB.Execute strSQL
Me.Requery
Me.lstCustomer.Requery
zz = lstCustomer.listcount

<snip>

Try reducing the number of fields in the recordsource of
lstCustomer to see if it requeries faster.


Steve

 

[Kelvin]

I have to agree with Steve. I do not see a need to create a new recordset
for performing the delete. Steve's code for a delete query using the
..execute method should work quicker and prevent this delay. Then you
wouldn't need your loop routine.

Kelvin