Help With Progress Bar!

[NewbieUK]

Hi,

I have a form which loads containg a sub form based on a query. The query is definded in a previous form by the user who selects options from a list box. I've got the query defs VBA sorted and all works fine. However, the form that loads showing the sub form which displays the query results can vary in the lentgh of time it takes to run. So what I wanted to do was open a form to show a progress bar.

i.e.

FrmDataFieldSelection opens, user selects fields to display and clicks a 'View Data'.

'View Data' is a label which runs code to define the query defs and then opens FrmQueryResults which contains sub form linked to qryDataAppend.

Whilst FrmQueryResults is running qryDataAppend, I would like FrmProgressBar to open and display the progress of the query. and then close once the FrmQueryResults has opened.


If this isn't possible, maybe the FrmQueryResults could open with a blank subform, then qryDataAppend could run in the background then once complete Sub Form is updated with results of qryDataAppend???

Any help would be appreciated.

Thanks

 

[tehNellie]

You can use Access' own Status Bar updates

  Dim varReturn As Variant
  
  varReturn = SysCmd(acSysCmdSetStatus, "Updating Linked Table")

Which simply puts the string value in the status bar

I have my routines (import, reporting etc) controlled via queries stored in tables so I open the table (or query) into a recordset and stepthrough it executing the queries. From that you can determine, at a basic level, how far through the process you are, using the rs.percentposition, and update the progress bar as necessary:

And obvious downside to using rs.percentposition is that it takes no account of the query's runtime or complexity, it's simply that it's the 5th of 20 and so on rather than a truly accurate display of how far along the process you actually are. i.e. that query which takes 5 minutes to run is considered equal to that query which runs in 2 seconds on the progress meter.

      'Initialise the progress bar 
      varReturn = SysCmd(acSysCmdInitMeter, "some text", 100)
      
      'update the progress  meter with current percentage.
      varReturn = SysCmd(acSysCmdUpdateMeter, intpercentage)

If you google acSysCmdInitMeter, acSysCmdUpdateMeter or acSysCmdSetStatus you'll find a stack of articles that explain in more detail how to use Access' status bar. In my experience either updating a form or the status bar in the middle of queries running often results in nothing happening but that the access status bar is slightly better at updating than a form.