You're my last chance. Progress bar with make-table query

[StefanScheidegger]

Hi there

I have been trying hard to create a progress bar for a make-table query. Unfortunately, it is not working yet (progress bar appears but does not show the progress, however, the Microsoft Access status bar down the screen shows the progress of the make-table query).

Here is the current code I have.

________________________________

Private Sub ZuLieferant_Click()
Response = MsgBox("This may take some time." _
& Chr(10) & Chr(13) & "Push OK to continue, and Cancel to quit", 1)
If Response = 1 Then
DoCmd.Hourglass True
call acbInitMeter("Report Lieferant", true)
DoCmd.SetWarnings False
DoCmd.Hourglass False
DoCmd.OpenQuery ("Mktblqry_BasisFürLieferant&quot
DoCmd.SetWarnings True
DoCmd.OpenReport "Lieferant", acPreview
Reports("Lieferant&quot.ZoomControl = 50 ' 50%
End If
End Sub

________________________________


The line "call acbInitMeter("Report Lieferant", true)" calls the progress bar which I created based on the following web-page:

http://www.oreilly.com/catalog/accesscook/chapter/excerpts.html#t3

I appreciate your help. Thanks you so much.

regards,

Stefan

 

[nat1967]

Hi Stefan,

To my knowledge, a self made progress bar will not work with a query.

When you create a status/progress bar, it must update itself based on certain values that the code must pass to it.

For instance, if you use a Do..Loop, you could update the bar +1 each time the loop occurs.

The other way is to hard code the number to be used by the progress bar in your code somewhere. For instance, if your code was calling several functions, you could set the progress bar status after each function was complete.

call function 1
progressbar = 25
call function 2
progressbar = 50
etc...

There is not a way that I have found to use a query as the variable. The query is a self contained operation and does not allow variables to be assigned or retrieved. The Syscmd progress bar is the only readable status that is given.

The chapter you referred to in O'reilly shows the examples as I mentioned above.

Maybe there is a way and someone will stop by and share with us.

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[wkendrvr]

A thought, you could do a make table query and create just the structure with the query, then mimic the append through vba. You will still need to have a number increment, (take the count of the total recs that you are adding to use for the bar) but that should work. This may slow things down, but it will keep users from thinking its dead.

using progressbar here is a sample (make sure you reference microsoft windows common controls)

Private Sub Form_Timer()
Dim start
Dim timer_int
Dim current
Dim elapsed
Dim time_left

Me.TimerInterval = 0

ProgressBar3 = 100
timer_int = 60

start = Fix(Timer())

Me!Text3 = "Application will close in 30 seconds."

timer_loop:

current = Fix(Timer())

elapsed = Fix(current - start)

time_left = timer_int - elapsed

ProgressBar3 = 100 - ((100 / timer_int) * elapsed)

If (time_left / 5) = (Fix(time_left / 5)) Then
Me!Text3 = "Application will close in " & time_left & " seconds."
Text11.SetFocus
Form.Repaint
End If


If elapsed >= timer_int Then DoCmd.Quit

GoTo timer_loop
End Sub