query delay

[JString]

My form uses a listbox to browse through records and it works fine unless I
delete a record.

The parent form uses a persistent snapshot recordset which is requeried by a
subroutine in vba. The subroutine then uses this recordset to create a new
recordset which is applied to the listbox's recordset property so that it
will reflect any changes made.

The sub calls these procedures one right after the other like this:
AllRecords.Requery
Dim rsNew As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set myListBox.Recordset = rsNew

It usually works fine, but when I try to call it in another procedure (as
opposed to clicking a command button) after running a delete query, there
seems to be a delayed response and the listbox doesn't reflect the changes on
the first call, though after clicking a command button that calls the sub,
the changes do show up.

Am I right that there is a delayed response that I'm running into? And if
so, how might I get around this problem?

 

[Armen Stein]

My form uses a listbox to browse through records and it works fine unless I
delete a record.

The parent form uses a persistent snapshot recordset which is requeried by a
subroutine in vba. The subroutine then uses this recordset to create a new
recordset which is applied to the listbox's recordset property so that it
will reflect any changes made.

The sub calls these procedures one right after the other like this:
AllRecords.Requery
Dim rsNew As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set myListBox.Recordset = rsNew

It usually works fine, but when I try to call it in another procedure (as
opposed to clicking a command button) after running a delete query, there
seems to be a delayed response and the listbox doesn't reflect the changes on
the first call, though after clicking a command button that calls the sub,
the changes do show up.

Am I right that there is a delayed response that I'm running into? And if
so, how might I get around this problem?

It does sound like a timing problem. Try adding a DoEvents before the
rebuilding of the recordset for the listbox. You can even try it in a
few places to see what might work. Just remember to remove the
DoEvents that don't have any effect instead of leaving them all in
there, as they can cause a brief performance slowdown.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[william chalfin]

My form uses a listbox to browse through records and it works fine unless
I
delete a record.

The parent form uses a persistent snapshot recordset which is requeried by
a
subroutine in vba. The subroutine then uses this recordset to create a
new
recordset which is applied to the listbox's recordset property so that it
will reflect any changes made.

The sub calls these procedures one right after the other like this:
AllRecords.Requery
Dim rsNew As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set myListBox.Recordset = rsNew

It usually works fine, but when I try to call it in another procedure (as
opposed to clicking a command button) after running a delete query, there
seems to be a delayed response and the listbox doesn't reflect the changes
on
the first call, though after clicking a command button that calls the sub,
the changes do show up.

Am I right that there is a delayed response that I'm running into? And if
so, how might I get around this problem?