Getting a ProgressBar to work!

[LMcleod]

Hi,

I can't find how to get my ProgressBar to work on my Access form. I want it to show the progress of the queries I have running behind a button. It's not essential, just aesthetic, so if anyone thinks it might be a struggle please let me know!

I am a VBA beginner, so any help would be appreciated.

Thanks.

Lou

 

[illini]

LMcLeod,

Here's a simple sub I have in a module.


Sub Update()
With Form_Frm_Main
.LB_Clients.Requery
.Progress_Bar.Value = 20
.LB_Contacts.Requery
.Progress_Bar.Value = 40
.LB_Online.Requery
.Progress_Bar.Value = 60
.LB_Corresp.Requery
.Progress_Bar.Value = 80
.LB_Contacts.Requery
.Progress_Bar.Value = 100
.Progress_Bar.Value = 0
End With
End Sub

When the user clicks a command button on a form, it calls this sub-routine.

Hope this helps.
-illini

 

[LMcleod]

Illini,

Thanks for this - since I'm not too clued up on VBA, could you tell me where I can put this code amongst what I already have?
Here's what I have behind the button:

Private Sub Command39_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryQueryList", dbOpenDynaset)
rs.MoveFirst
DoCmd.SetWarnings False
Do
DoCmd.OpenQuery rs("QueryName&quot
rs.MoveNext
Loop Until rs.EOF
DoCmd.SetWarnings True
rs.Close
db.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Team Stats_04_final", "H:\Team Stats.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Transaction Code by Agent Totals", "H:\Team Stats.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Transaction Code by Agent", "H:\Team Stats.xls"
End Sub

 

[illini]

LMcLeod,

That's really up to your discretion. I guestimated mine based on the length of time I found it took to requery each listbox I had. Here's a stab at yours

Private Sub Command39_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryQueryList", dbOpenDynaset)
rs.MoveFirst
DoCmd.SetWarnings False
Do
DoCmd.OpenQuery rs("QueryName&quot
rs.MoveNext
Loop Until rs.EOF
DoCmd.SetWarnings True
rs.Close
db.Close
Your_Form.Your_Progress_.Value = 25
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Team Stats_04_final", "H:\Team Stats.xls"
Your_Form.Your_Progress_.Value = 50
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Transaction Code by Agent Totals", "H:\Team Stats.xls"
Your_Form.Your_Progress_.Value = 75
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "Transaction Code by Agent", "H:\Team Stats.xls"
Your_Form.Your_Progress_.Value = 100
'you'll need to reset the progress bar after this or else it will remain at 100
Your_Form.Your_Progress_.Value = 0
End Sub
-illini