Link to home
Start Free TrialLog in
Avatar of prosit
prositFlag for United States of America

asked on

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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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).
Avatar of prosit

ASKER

It's the value of the progress bar I'm trying to update.
What version of Office are you running, I'm not aware of a Progress Bar control.
Avatar of prosit

ASKER

it's an activex - Microsoft progress bar.
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)
Avatar of prosit

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of prosit

ASKER

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
Avatar of prosit

ASKER

I created a test database, the code in the module is what gives me the error.

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

http://www.helenfeddema.com/Files/accarch181.zip
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

Avatar of prosit

ASKER

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

Thank you anyway...
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?
Avatar of prosit

ASKER

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