matthewlorin7
asked on
MS Access Progress Meter/Bar During Query
I know this question has been answered many times in many different forms, but I cannot seem to make my code work. In short, I want to have a status bar form displayed while I open up another form, which will in turn display the results of a query; this takes anywhere from 10 to 40 seconds (depending on what computer it's ran from). The query referenced in the opening form will only have one or two records, which are the matched records between two very large queries. Ideally, I would like to have a status bar match the time it takes to open up the query, but I'm not sure this is possible. At the very least, I would like to have a form with a moving bar, which I can either attempt to match based on the average query time or just loop over and over again. Right now I'm doing the latter using the "on timer" event to trigger the bar...I'm attempting to open up my final form (frmCompare_Results) that will display the query when the status form opens, then have frmCompare_Results close the status form (frmStatus). However, when frmCompare_Results is in the process of opening, it just locks everything up, closing frmStatus several seconds later with no action. My code is below. I am a beginner, so please construct any suggestions accordingly. Thank you in advance for the help!
Private Sub Form_Timer()
Dim Counter As Long
DoCmd.OpenForm "frmCompare_Results"
Me.Status.Caption = "Reading"
Screen.MousePointer = 11
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.Source = "tblNumber"
.Open
While Not .EOF
ProgressBarC.Visible = True
CurrentRecordID = .Fields("Number")
ProgressBarC.Width = (ProgressBarA.Width / .RecordCount) * .AbsolutePosition
Me.Repaint
.MoveNext
For lCounter = 1 To 107000: Next
Wend
End With
If rs.EOF Then
CurrentRecordID = ""
Me.Status.Caption = "Done"
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Screen.MousePointer = 0
End Sub
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I found an old EE question/answer that is close to what I want to do:
"One thing that I did once for a query was that I passed a value to the progress meter that represented full scale, and then incremented a variable for each record to update the progress in terms of percent of the initially passed value. The query ran slower, (just a little), but the progress meter finished when the query finished."
Any idea how I can do this, either in conjunction with or independetn of my code above?
"One thing that I did once for a query was that I passed a value to the progress meter that represented full scale, and then incremented a variable for each record to update the progress in terms of percent of the initially passed value. The query ran slower, (just a little), but the progress meter finished when the query finished."
Any idea how I can do this, either in conjunction with or independetn of my code above?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER