Triggering a Progress Bar 4

[pcgenie]

I have a database audit form module that takes a few seconds to search through a linked table and then insert the information into the main db table. I've seen a lot of newsgroup discussions on progress bars and I have a few examples, but they either use a timer or counting records. I'll probable use the simple progress bar with the growing rectangle, but I need help on how to trigger each increment of growth of the popup progress bar, coordinate it with the main form and then close the popup. The code in the main form that executes the database audit is as follows:

Code:
====================================================
Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Proc_Err

Dim frmCurrentForm As Form
Dim strUser As String
Dim strFirstName As String
Dim strLastName As String

Set frmCurrentForm = Screen.ActiveForm
frmCurrentForm![LastModified] = NOW

strUser = User()
strFirstName = DLookup("[First]", "Global Address List", "[Account] ='" & strUser & "'")
strLastName = DLookup("[Last]", "Global Address List", "[Account] ='" & strUser & "'")
Me!EditedBy = strFirstName & " " & strLastName
Me!EditedWhen = NOW()

Exit Sub

Proc_Err:
MsgBox "The following error occured: " & Error$
Resume Next
End Sub
====================================================
Thanks,
PC

 

[Frink]

Hallo,

Is it the two DLookups which are taking the time? Do you want the progress bar to somehow increase while it waits for them to complete?
If it is and you only call this routine once, then the best you can do is indicate 50% after the first DLookup and 50% after the second. Anything else would require you to know how long it was going to take, and I'm not sure you can do any processing while a DLookup is going on anyway.

- Frink

 

[PHV]

To speed up, you may replace this:
strUser = User()
strFirstName = DLookup("[First]", "Global Address List", "[Account] ='" & strUser & "'")
strLastName = DLookup("[Last]", "Global Address List", "[Account] ='" & strUser & "'")
Me!EditedBy = strFirstName & " " & strLastName
By this:
Me!EditedBy = DLookup("[First] & ' ' & [Last]", "Global Address List", "[Account] ='" & User() & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya pcgenie . . . . .

The ProgressBar is one of the greatest attributes afforded Access since it first came out! It has pazaz and always gets attention. People seem to love watching it run its course!

As for operations, there are three main attributes:

[blue]Min[/blue], which defaults to zero and usually needs no setup.

[blue]Max[/blue], the full count of the bar, usually set by the programmer . . . AKA . . . [purple]You![/purple]

[blue]Value[/blue] (and the most important), the current value of the bar, >= min & <= max that is [purple]setup/enumerated by you![/purple]

[blue]I've seen a lot of newsgroup discussions on progress bars and I have a few examples, but they either use a timer or counting records.[/blue]

No matter how you do it (despite the above), you have to enumerate the bar through its full length.

See Ya! . . . . . .

 

[RoyVidar]

On linked tables, you might try opening a recordset in stead of using one of the Domain Aggregates:

[tt]dim rs as dao.recordset
dim strSql as string
strSql="select first & ' ' & last as FullName " & _
"from [Global Address List] " & _
"where [Account] ='" & strUser & "'"
set rs=currentdb.openrecordset(strSql,dbopenforwardonly)
if not rs.eof then
Me!EditedBy = rs("FullName").value
end if
rs.close
set rs=nothing
Me!EditedWhen = NOW[/tt]

Even better, why not get those values when opening the form, store it in either (hidden) form controls, or in form public variables, so you don't need to look them up on every update.

Roy-Vidar

 

[TheAceMan1]

pcgenie . . . . .

I was in the middle of my prior post and had to run out on an emergency . . . . To continue:

To setup the Form & ProgressBar, in a new form in design view, on the menubar click Insert - ActiveX Control..., scroll to and select [purple]Microsoft ProgressBar Control, version 6.[/purple] Click OK.

Size the bar to your liking then, right-click the bar - ProgCtrl Object - Properties. At the bottom you'll see the combobox for the [purple]Scrolling[/purple] property. Select [purple]Smooth[/purple]. Click OK.

Now set the following properties for the form:

ScrollBars [purple]Neither[/purple]
Record Selectors [purple]No[/purple]
Navigation Buttons [purple]No[/purple]
Auto Resize [purple]Yes[/purple]
Auto Center [purple]Yes[/purple]
Border Style [purple]Dialog[/purple]
Min Max Buttons [purple]None[/purple]
Caption [purple]Empty/No Value[/purple]
PopUp [purple]Yes[/purple]
Modal [purple]Yes[/purple]

Save/close the form

[blue]but I need help on [purple]how to trigger each increment of growth[/purple] of the popup progress bar[/blue]

There's no triggering of increments. All you do is update the current value of the control as in the following sample code:

[blue]   Dim frm As Form, ctl As Control, n As Long
   
   DoCmd.OpenForm "[purple][b]YourFormName[/b][/purple]"
   DoEvents 'Allows ActiveX Control to fully refresh.
   
   Set frm = Forms!frmprogressBar
   Set ctl = frm![purple][b]ProgressBarName[/b][/purple]
   
   frm.Caption = "Down Loading Data!"
   ctl.Max = 1000 [green]'Set max count for the bar
   
   'Sample enumeration. Your enumeration code would go here.[/green]
   For n = 1 To 1000
      ctl = n
   Next
   
   DoCmd.Close acForm, "[purple][b]YourFormName[/b][/purple]"
   
   Set ctl = Nothing
   Set frm = Nothing[/blue]

[blue]I have a database audit form module that takes a few seconds to search through a linked table and then insert the information into the main db table.[/blue]

Unless you use [blue]RoyVidars'[/blue] suggestion [purple]there's no real way to enumerate and unknown amount of time[/purple]. Some users could be at the beginning, others at the end of the table. . . . . . .

See Ya! . . . . . .

 

[pcgenie]

Thank you PHV, TheAceMan1, and RoyVidar. I appreciate you effort in suggesting some solutions. I think all of them have merits that I can use in this and future applications.

Gratefully,
PC Genie

 

[chiefvj]

Can you please explain where the code goes. Which event will trigger this code?? What should we replace Forms!frmprogressBar with???
thanks

 

[TheAceMan1]

How are ya chiefvj . . . . .

[blue]Can you please explain where the code goes.[/blue]

The choice is yours, but usually where ever the code is that opens the form.

[blue]Which event will trigger this code??[/blue]

If you read this thread you'd know its not triggered. The Bar is enumerated thru its length. How its enumerated is up to you . . .

[blue]What should we replace [purple]Forms!frmprogressBar[/purple] with???[/blue]

Forms![purple]YourProgressBarFormName[/purple]

See Ya! . . . . . .

 

[chiefvj]

TO: TheACEMAN1,
Sorry I'm a "show me how it done kind of user". I'm still not sure how to use the progrees bar in a form. How do I "enumerate" this progress bar.
Thanks

 

[TheAceMan1]

chiefvj . . . . .

Including this post, 5 posts back is an example. Here's another:

Lets say you have a [purple]table[/purple] with a field [purple]State[/purple]. When data was entered all users abbreviated State except the ones from Georgia who spelled it out, and the majority of users were from your Georgia office. Your going to [purple]abbreviate all records with Georgia[/purple] and [blue]run the Progress Bar as a visual.[/blue] When you click a button you run the following code (anything to do with the ProgressBar is in [purple]purple[/purple]):

[blue]Public Function AbbrevState(State)
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim frm As Form, [purple][b]ctl[/b][/purple] As Control   
 
  Set db = CurrentDb()
   [green]'Set object reference to the Table.[/green]
   Set rst = db.OpenRecordset("TableName", dbOpenDynaset)
   rst.MoveLast
   rst.MoveFirst
   
   [green]'Open the ProgressBar form.[/green]
   DoCmd.OpenForm "[purple][b]ProgressBarFormName[/b][/purple]"
   [green]'Allow ProgressBar form to fully refresh.[/green]
   [purple][b]DoEvents[/b][/purple]
   [green]'Set object reference to the ProgressBar form.[/green]
   Set frm = Forms![purple][b]ProgressBarFormName.[/b][/purple]
   [green]'Set object refernce to the ProgressBar.[/green]
   Set ctl = frm![purple][b]ProgressBarName[/b][/purple]
   [green]'Set the Caption of the form.[/green]
   [purple][b]frm.Caption[/b][/purple] = "Abbreviating Georgia! . . ."
   [green]'Set the Max Count of the bar.[/green]
   [purple][b]ctl.Max[/b][/purple] = rst.RecordCount
   
   [green]'Update 'Georgia' and enumerate the ProgressBar![/green]
   Do
      If rst.State = "Georgia" Then
         rst.Edit
         rst.State = "GA"
         rst.Update
      End If
      
      [green]'ProgressBar is enumerated here![/green]
      [purple][b]ctl = ctl + 1[/b][/purple]
      
      rst.MoveNext
   Loop Until rst.EOF
   
   [green]'Close the ProgressBar form . . . Done![/green]
   DoCmd.Close acForm, "ProgressBarFormName", acSaveNo
   
   [green]'return used resources to the database (close out)[/green]
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

[purple]Cheers![/purple]

See Ya! . . . . . .

 

[chiefvj]

tHANK yOU vERY mUCH..THAT IS WHAT I HAVE BEEN LOOKING FOR OUTSTANDING.
CHIEFVJ