Repaint Method Stops Working On Form

[JonMulder]

Greetings,

I have a routine that loops through about 100,000 records for water wells over a time frame of 36 years. For each two month interval, it checks for the Well Type (Domestic, Irrigation, etc.) and does a running total for each Well Type for the two month interval. Then those running totals for the two month interval are added to another summary table.

All of that works fine, but I have another form ("Form--ProcessingStatus") that I open up to inform the user of the progress. It tells the user what County it is working on and what the Current Year is (from 1977 to 2013). It also has a progress bar whose width grows based on the Current Year it is processing. For some reason, the form freezes up on the first County I'm processing after the progress bar has gone through about 18 to 22 years (not always freezing on the same year). I invoke the "Repaint" method every time after I resize the progress bar.

The routine continues to loop through my counties and finishes up after about ten minutes with no problem. But the "Form--ProcessingStatus" remains frozen on the first County. In other words, the Repaint method does not continue to work. I've attached an image of what it looks like.

Can anyone figure out why it hangs up? It generally works in my other routines and is a good visual way to inform the user of progress. Or maybe I need to rethink how to inform the user. I've searched around about progress bars and found some threads that talk about "DoEvents" but don't really understand that.

My code is posted below so you can see where I invoke the Repaint method.

Thanks for any help you can supply!

Jonathan Mulder
Engineering Geologist
California Department of Water Resources

'Processing NRO data.
Set rstCounties = dbs.OpenRecordset("NRO_Counties", dbOpenDynaset)
'Create query of NRO Logs sorted by County and DrillDate.
strSelect = "SELECT NRO_tblLog.County, NRO_tblLog.Use, NRO_tblLog.DrillDate, NRO_tblLog.ReceivedDate, NRO_tblLog.FileDate, NRO_tblLog.LogNumber"
strFrom = "FROM NRO_WellUse RIGHT JOIN NRO_tblLog ON NRO_WellUse.Use = NRO_tblLog.Use"
strWhere = ""
strOrder = "ORDER BY NRO_tblLog.County, NRO_tblLog.DrillDate"
Call CreateQuery("NRO_Logs", strSelect, strFrom, strWhere, strOrder)
Set rstNRO_Logs = dbs.OpenRecordset("NRO_Logs", dbOpenDynaset)
DoCmd.OpenForm "Form--ProcessingStatus", acNormal, , , , acWindowNormal
Forms("Form--ProcessingStatus").LblTitle.Caption = "Creating Bi-Monthly Well Counts for NRO."
Forms("Form--ProcessingStatus").BoxBackground.Visible = True
Forms("Form--ProcessingStatus").BoxProgress.Visible = True
Forms("Form--ProcessingStatus").Repaint
rstCounties.MoveFirst
Do While Not rstCounties.EOF
   strCurrentCounty = rstCounties("County")
   For intCurrentYear = 1977 To 2013
      Forms("Form--ProcessingStatus").LblCurrentProcess.Caption = "Analyzing " & strCurrentCounty & " County for Year " & CStr(intCurrentYear) & "."
      Forms("Form--ProcessingStatus").BoxProgress.Width = ((intCurrentYear - 1977) / 36) * 6480
      Forms("Form--ProcessingStatus").Repaint
      For intCurrentMonth = 1 To 11 Step 2
         rstTarget.AddNew
         rstTarget("CountyCode") = rstCounties("CountyCode")
         rstTarget("Region") = "NRO"
         rstTarget("MonthYear") = Format(CStr(intCurrentMonth + 1) & "/" & CStr(intCurrentYear), "MM/YYYY")
         datStartDate = CStr(intCurrentMonth) & "/1/" & CStr(intCurrentYear)
         datEndDate = CStr(intCurrentMonth + 2) & "/1/" & CStr(intCurrentYear)
         If intCurrentMonth = 11 Then
            datEndDate = "1/1/" & CStr(intCurrentYear + 1)
         End If
         num_DOM = 0
         num_IRR = 0
         num_MON = 0
         num_PUB = 0
         num_IND = 0
         num_OTHER = 0
         Dim strFind As String
         strFind = "County = '" & strCurrentCounty & "' AND DrillDate >= #" & datStartDate & "# and DrillDate < #" & datEndDate & "#"
         rstNRO_Logs.FindFirst strFind
         If Not rstNRO_Logs.NoMatch Then
            Do While rstNRO_Logs("County") = strCurrentCounty And rstNRO_Logs("DrillDate") >= datStartDate And rstNRO_Logs("DrillDate") < datEndDate
               If rstNRO_Logs("USE") = "DOM" Then
                  num_DOM = num_DOM + 1
               End If
               If rstNRO_Logs("USE") = "IRR" Then
                  num_IRR = num_IRR + 1
               End If
               If rstNRO_Logs("USE") = "MON" Then
                  num_MON = num_MON + 1
               End If
               If rstNRO_Logs("USE") = "PUB" Or rstNRO_Logs("USE") = "MUN" Then
                  num_PUB = num_PUB + 1
               End If
               If rstNRO_Logs("USE") = "IND" Then
                  num_IND = num_IND + 1
               End If
               If rstNRO_Logs("USE") <> "DOM" And rstNRO_Logs("USE") <> "IRR" And rstNRO_Logs("USE") <> "MON" And rstNRO_Logs("USE") <> "PUB" And rstNRO_Logs("USE") <> "MUN" And rstNRO_Logs("USE") <> "IND" And rstNRO_Logs("USE") <> "DES" And rstNRO_Logs("USE") <> "ABD" Then
                  num_OTHER = num_OTHER + 1
               End If
               rstNRO_Logs.MoveNext
               If rstNRO_Logs.EOF Then
                  Exit Do
               End If
            Loop
         End If
         rstTarget("Domestic") = num_DOM
         rstTarget("Irrigation") = num_IRR
         rstTarget("Monitoring") = num_MON
         rstTarget("PublicSupply") = num_PUB
         rstTarget("Industrial") = num_IND
         rstTarget("OtherWells") = num_OTHER
         rstTarget.Update
         If rstNRO_Logs.EOF Then
            Exit For
         End If
      Next intCurrentMonth
      If rstNRO_Logs.EOF Then
         Exit For
      End If
   Next intCurrentYear
   rstCounties.MoveNext
   If rstNRO_Logs.EOF Then
      Exit Do
   End If
Loop
DoCmd.Close acForm, "Form--ProcessingStatus"