Updating progress bar on form from Module.

Hi,

I have some code that runs a long function and therefore I need a progress bar to show that something is actually going on.

So in the code (which is not the code behind the progress bar form) I have this:

   Forms!progress.lblProgress.Caption = "Updating weekly demand rows - " & RecNumber & "/" & MaxSize
   Forms!progress!PB.Value = Round((RecNumber / MaxSize) * 100)

but the last line returns an error: Property is write-only... why?

Also, in vb.net you can throw application.doevents to make the screen update, is there a similar function in Access or do I not even need it?

tnx
~j
LVL 2
prositAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Cannot tell you why that field would be read only, I generally use labels and update the Caption property rather than setting the value of a textbox (is PB a textbox)?

You can simply use:

DoEvents

To allow a pause in order for windows to perform a variety of actions.  

You might also want to do some form of repaint.

and if you want a more robust progress bar, check out me article and the associated example database.  This gives you the ability to actually control either one, or two progress bars, or to simply display a message while some other process is going on (unlike a msgbox which is a dialog and pauses all code while the dialog is open).
prositAuthor Commented:
It's the value of the progress bar I'm trying to update.
Dale FyeOwner, Developing Solutions LLCCommented:
What version of Office are you running, I'm not aware of a Progress Bar control.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

prositAuthor Commented:
it's an activex - Microsoft progress bar.
Dale FyeOwner, Developing Solutions LLCCommented:
Have never used that control.

Are you sure the control name is PB?

Are you sure it is looking for an integer (0 - 100) and not a decimal value (0.00 to 1.00)?

You might also try:

Forms("Progress").Controls("PB").Value = Round((RecNumber / MaxSize) * 100)
prositAuthor Commented:
Yes I'm positive, if I run the exact same code from the form itself it works perfectly.

I tried updating it to the line above, with same result.  I've attached the exact error dialog.

I also added a screenshot of the activex I added to get the progress bar.

PS. It's Access 2007

~j
error.png
delete.png
Dale FyeOwner, Developing Solutions LLCCommented:
sorry, I cannot replicate your problem

I added that control to a blank form, set the control name to "PB" as you seem to have, changed the view to form view, and then called the following from the immediate window, and it worked just fine with either syntax.

forms("frm_ProgressBar").PB.Value = 25
forms!frm_ProgressBar!PB.Value = 50

I then tried:

forms!frm_ProgressBar!PB.Value = 150

and I received an error similar to yours.  So I would check the value of:

Round((RecNumber / MaxSize) * 100)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
prositAuthor Commented:
So you have the code in a separate module?

See screen shot.  The code that updates the progressbar is NOT on the progress form, I am trying to update it from the Weekly module.

thanks
~j
delete.png
prositAuthor Commented:
I created a test database, the code in the module is what gives me the error.

~j
ProgressTest.accdb
Helen FeddemaCommented:
See my Access Archon article on progress bars:

http://www.helenfeddema.com/Files/accarch181.zip
Helen FeddemaCommented:
Here are some code samples for working with progress bars:

   'Initialize the progress bar (using an arbitrary division of 4 units)
   Call SysCmd(acSysCmdInitMeter, "Creating output file ...", 4)

   'Update the progress bar
   Call SysCmd(acSysCmdUpdateMeter, 1)
   Call SysCmd(acSysCmdSetStatus, "Status bar message")

====================
   'Update the progress bar
   Call SysCmd(acSysCmdUpdateMeter, 2)
   Call SysCmd(acSysCmdSetStatus, "Status bar message")


====================
   'Update the progress bar
   Call SysCmd(acSysCmdUpdateMeter, 3)
   Call SysCmd(acSysCmdSetStatus, "Status bar message")


====================
   'Update the progress bar
   Call SysCmd(acSysCmdUpdateMeter, 4)
   Call SysCmd(acSysCmdSetStatus, "Status bar message")

====================

   'Remove the progress bar
   Call SysCmd(acSysCmdRemoveMeter)

Dynamic slice numbering:

   Call SysCmd(acSysCmdUpdateMeter, i)
   i = i + 1

Iterating through a recordset:

   strArchiveTable = "tblArchived" & Mid(strTable, 4)
   'Debug.Print "Archive table: " & strArchiveTable
   Set rstArchive = dbs.OpenRecordset(strArchiveTable)
   
   'Create a recordset of data older than the archive date
   strSQL = "SELECT * FROM " & strTable _
      & " WHERE #" & dteArchive & "# > [" & strDateField & "];"
   
   'Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of records to archive in " & strTable & ": " & lngCount
   If lngCount = 0 Then
      GoTo ErrorHandlerExit
   Else
      Set rst = CurrentDb.OpenRecordset(strQuery)
      
      lngCounter = 1
      
      'Initialize the progress bar
      Call SysCmd(acSysCmdInitMeter, "Archiving " & lngCount _
         & " records from " & strTable & " ...", lngCount)
      
      Do While Not rst.EOF
         rstArchive.AddNew
         
         For Each fld In rst.Fields
            strField = fld.Name
            'Debug.Print strField
            rstArchive.Fields(strField) = rst.Fields(strField)
         Next fld
         rstArchive.Update
         rst.MoveNext
         Call SysCmd(acSysCmdUpdateMeter, lngCounter)
         lngCounter = lngCounter + 1
      Loop
   End If

...

ErrorHandlerExit:
   Call SysCmd(acSysCmdRemoveMeter)

Open in new window

prositAuthor Commented:
Neither of the answers really satisfies the question, but I have to move on...

Thank you anyway...
Dale FyeOwner, Developing Solutions LLCCommented:
Sorry that I didn't get a chance to look at this over the holiday.  The code you provided worked fine when run in my Office 2007 environment, Windows 7.

Did you take a look at my article?
prositAuthor Commented:
I did, it's using a different progress bar, and I could refit it, but I don't think the user really cares if there's a progress bar instead of just counting up the records processed.

I do appreciate the time you've taken to reply and I'll keep this in mind if I run into this in the future.  I do quite a lot of Access conversions so there's a good chance.

~j
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.