CODEWITHSUNDEEP
ms-accesslistboxvba
Gregor
Gregor

Reputation: 133

Access 2007 VBA Listbox lag after changing DB data or Requery

Ive been having an extremely annoying problem that recently started to happen.

My Listbox are not updating right away. For example on my main form "A" has a listbox with an sql statement (Simple... returns 5-20 rows). I have an edit button underneath that when clicked opens a new form to change the data. That form submits an update query and then calls a global function which requiries all of the related listboxes (the one on Form A). The only problem is the listbox doesnt change and seems to hang/lag. if i select the listbox and continuously hit f5, after a few seconds it will randomly refresh correctly.

Does anyone have any idea of what the problem is?

Ive been troubleshooting this for two days now and know its not a network problem as it also happens locally, using a recordset and looping it to manually set the listbox values works fast, but for some reason all my listboxes in my application is doing this.

Is it a setting I accidentally changed or if you define to many relationships does this happen (I recently added another table)?

EDIT: Forgot to mention, the DB is on a shared drive and is only 2MB, only recently has it started to do this.

Upvotes: 1

Views: 857

Answers (1)

Gregor
Gregor

Reputation: 133

I eventually found the problem, hopefully this helps someone in the future.

It had to do with using:

CurrentDB.Execute

this method runs synchronous in the background, therefore the listbox was being refreshed before the sql UPDATE/INSERT/DELETE was complete.

To fix this I instead used:

Docmd.RunSQL

This has solved the problem, however I noticed this method only works on Microsoft Access Databases.

Upvotes: 2

Related Questions