Query Progression Bar

Topic

New Reply

When I run a long query, MS Access displays a progression bar at the bottom of the screen by default.

Is there a way to use this intrinsic functionality in a query run with all the features in the startup menu unchecked (so only the form shows unless you hit F11, if I’m making sense). Further, is there a way to create a progression bar in the form that will function with a query execution?

Reply | Quote

Replies

There are actually several ways to create a progress bar, although I’m not clear on whether you want to create it from form code or from somewhere else.

One way is to use the SysCmd method to display a progress bar like the one you see. Here’s a snippet of code lifted from on-line help on SysCmd:

 If lngCount Then
  strMsg = "Reading " & UCase$(strTableName) & "..."
  varReturn = SysCmd(acSysCmdInitMeter, strMsg, lngCount)
  ' Display message in status bar.
  For lngX = 1 To lngCount
    varReturn = SysCmd(acSysCmdUpdateMeter, lngX)
    ' Update meter.
    ' Do something with record.
    .
    .
    rst.MoveNext                ' Go to next record.
  Next lngX
  varReturn = SysCmd(acSysCmdClearStatus)

Another way is to use an ActiveX control built for that purpose, and I’ve also seen unbound forms with a series of square colored labels whose visible property is set to true sequentially based on a timer or a calculation of some sort.

Reply | Quote

Thanks for your reply, Charlotte.

I don’t think I’m doing a very good job of explaining this.

When you run a query normally, it displays a progression bar by default. I guess Access figures out the upper limit of the query and then is able to increment the progression bar it displays with whatever process it does to run it.

My problem is that when I deliver an application, I go into start-up and tell it to open a default form. I also uncheck all the boxes so they don’t have the Access interface, only my form. When I do that, the default Access progression bar doesn’t display when a query is run.

At the least, if it is possible, I would like to display that progression bar that Access displays normally in the corner – and at most, I would like to display my own progression bar in the form when the query is run.

I know that the SysCmd is used to display that progression bar in the application manager, but when you execute a query that is stored in Access’s query collection, how do you tell it to display that and increment it?

As a real basic example, you have a query “SELECT * FROM Customers” and you save it as “qryCustomers”. You double click on qryCustomers in the queries tab, and it displays a progression bar in the corner as it queries the million or so records. When you do what I described above with the start-up settings, that no longer displays. I want to turn that back on when a query is executed. Using the SysCmd function, I don’t know what to pass to lngCount (unless I calculate the recordcount separately which would seriously extend the query execution for the end user), and I don’t know how to increment somthing that Access is doing on its own.

Reply | Quote

If you want to just display the built in progress bar for your query go back into the startup options and check the box next to Display Status Bar.

Reply | Quote