[RESOLVED] [Access forms] Improving user feedback / query's progress

Currently when a form query is run, it not only takes a long time, but also doesn't provide any feedback regarding when it's still running or completed.

A lot of the time it looks like it's finished, but when the user tries to click on a field access then updates a few more fields.

This is a monster database that has no place on MS Access, and will eventually be moved to a DB server. But in the meantime (possibly 6months) it needs to be used.

So the question is; are there any events that can be hooked into with VBA to improve the feedback experience?

For example, indicating that the query is still being run, indicating when it's completely finished, and not showing partial records until it is finished. Instead of the "not responding" hell we currently see.

I've just spent a few hours Googling same and looking through the events lists for forms and queries with no result, so any pointers are appreciated.:sick:

What kind of a query is it? Is there any looping going on?

Can you post up the query as maybe it can be optimized to improve performance.

Is the forms recordsource just bound to the form or are you invoking some code to load it?

There's nothing clever going on there at all. It's just a bog standard query designed by the original user in Access's Query Builder, bound to the form. One HUGE flat table, no joins and only one WHERE matching on one field OR one other.

I can't post the actual field names here due to proprietary concerns, however it takes this format:

Code:

---

SELECT {list of 57 fields}
FROM [{table's name}]
WHERE ((([{table's name}].[TED])=[Choose TED])) OR ((([{table's name}].BOB)=[choose BOB]));

---

I just noticed that there's no indexes set! The guy who created it insisted there were. There are over 75,000 records, sometimes it takes 5 seconds to return a record, other times it can take up to a minute. Now that I've indexed it, the response times are much better.

Now the question is; will this affect updates? I'm about to start a daily run so I'll see.

---
EDIT:

Indexing has improved response times dramatically (dah) and doesn't seem to have affected the update times noticibly (they always took minutes anyway). This will do for now. Thanks everyone for your responses anyway. :)