Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Progress Meter (Updating Form Labels In Form View)

Posted on 2004-09-23
7
423 Views
Last Modified: 2012-06-27
Hello,

I have made several scripts using VB that process various data in MSAccess- check some boxes here, add notes to this text box, and so on based on conditions from a query.  They all work exactly how I want them to, but some of them run for a rather long time and I would like to be able monitor the progress of the scripts.

My first thought was to create a Form right before the process starts and after every iteration of the process, update a label on the form that says, "12 out of 99,456 records processed." or possibly even draw a small blue rectangle and keep increasing the size of it to illustrate the progress like other programs often do.

I have relatively little experience in VB programming, but I know this would be a piece of cake in Java.  When I tried implementing this in VB I found that I can't make any modifications to a Form while in Form View.  Is there a way around this?

I also considered using a TextBox (surely this can be changed in FormView) and disguising it as a Label since I think TextBoxes look really tacky to display info that can't be modified.  I ran into a problem here too, though.  Here's what I did:

Dim strFormName As String
Dim frmProgress As Form
Dim cl As Control
Set frmProgress = CreateForm()
frmProgress.NavigationButtons = False
frmProgress.RecordSelectors = False
strFormName = frmProgress.name
Set cl = CreateControl(strFormName, acTextBox, acDetail, "", "", 100, 100, 5000, 300)
DoCmd.OpenForm (strFormName) ' open FormView
cl.Text = "Connecting to Database"

I get a: Run-time error '2467' Application-defined or object-defined error on the last line listed here.

Surely there is someone out there who has done this kind of thing before, and surely there is a way to implement this.  I'm very close to scrapping everything I've done using the MS Access VB and just starting over with the front end with a platform that allows more control.  Am I getting frustrated because I don't know what I'm doing or is it because MS VB is just not very flexible?

Thanks for any ideas you may have.
-Shannon
0
Comment
Question by:rhinoceroshead
7 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 100 total points
ID: 12137959
> Is there a way around this?

You cannot create or delete controls if the formis not in design view. Most other properties can be changed while the form is in any view.

Just create a label on the form manually. Save the form and then change the label's Caption property in code.

VBA is extremely flexible, but it has a different design methodology from Java. More or less anything that can be achieved in any event dirven, design language can be achieved in VBA but creating controls on the fly is something that you don't generally do with VBA. You usually use pre-created controls.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12138029
If you need a control on the form to be displayed under certain circumstances, create the control in Design view, giving it a FALSE value for the Visible property.  This gives you the opportunity to set the value of the control before the user sees it, and to determine if and when the user will see it through code.  This is the closest you will get to dynamic controls.

If you would like some examples, create a switchboard in Access using the Switchboard Manager utility, and examine the code it generates.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12138289
this is what i use for feedback on record processing  (export in this case) It updates a label on the form based on total to process and already processed...might be adaptable for you...

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim x, y
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = pubstrConnect
    strSQL = "EXEC dbo.maintFindUnExportedOrders " 'find orders for export
    cnn.Open
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseClient
        .Open Source:=strSQL, _
                ActiveConnection:=cnn, _
                CursorType:=adOpenDynamic, _
                LockType:=adLockOptimistic

       .MoveFirst
       While x < .RecordCount
       pubOrderID = !OrderID
       Call ExportEm 'a procedure which does the export
       .MoveNext
          x = x + 1
          y = .RecordCount - x
          Me!lblProcessed.Caption = y & " Records Left In Queue"
          Repaint
          Refresh
          Wend
       
          .Close
    End With

    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
   

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:rhinoceroshead
ID: 12146355
I'm awarding the points to shanesuebsahakarn because the solution to my problem was contained in that reply.  The code that I posted was not working because I had not yet saved the form, and apparently you can't change the Captions on a label that has not yet been saved.

I still don't think VB is very flexible.  Java is much more consistent about how to create and update objects and VB is extremely restrictive in that you can't add controls to existing forms.  VB just doesn't seem intuitive to me.  I feel like I have to look up somebody else's code to learn how to do anything.  For example, to create a new form with a control on it, I would assume you could just do this:

Dim newForm as Form
Dim newControl as Control

Set newForm = new Form
Set newControl = new Control
newForm.addControl(newControl)

But that won't work at all.  You have to use Application.CreateControl() to make the control.  What's the point of the 'new' keyword if you can't universally create a new object with it?  Some objects you can and some you can't.  If it were consistent then the learning curve wouldn't be nearly so steep.

Thanks for the help, and I'm really impressed by the quick responses!

Shannon
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12146460
welcome to the wonderful wolrd of vba and vb...

" If it were consistent then the learning curve wouldn't be nearly so steep."...and vb and vba wouldn't be Number 1 and number 2 in questions asked at EE ;)

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12146522
Again, it's a design methodology and understanding. The New keyword does work with forms, but it is used to create a new *instance* of an *existing object*. There should almost never be a situation where you ever need to create a form "on the fly". The keywords are not necessarily analogous to an action you would perform in design view.

Language and application design environments don't all work in the same way, and you can't expect them to. Many of us here could achieve almost anything in VBA that can be achieved with other languages and you'll learn the appropriate methods as you progress with it.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12146536
I should really get my terminology right:
a new instance of an *existing object definition* (or class, if you prefer)
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question