Updating .CAPTION property

[ghalewood]

Hi,

I have the following code that seems to be working properly, except that the caption property is only updated at the end of the procedure, basically telling the users that it is 100% complete. Can anyone show me how I would get the update everytime a record is processed. I am basically after a process bar, so updating the .CAPTION property might not be the best way to go. Any tuning tips would be welcome.

Thanks
Graham

Option Compare Database
Dim vapercent As Integer
Option Explicit

Sub openupdateprices()

Dim mydb As Database
Dim rsAscomp As recordset
Dim rsCatalist As recordset
Dim strQuote As String
Dim varSite As String
Dim vaReply As String
Dim vaTotal As Long
Dim vaRead As Long




Set mydb = CurrentDb()
Set rsCatalist = mydb.OpenRecordset("catalist_file&quot

rsCatalist.MoveLast
vaTotal = rsCatalist.RecordCount
rsCatalist.MoveFirst


Do Until rsCatalist.EOF

vaRead = vaRead + 1
show_Percent

Set rsAscomp = mydb.OpenRecordset("SELECT * FROM [ascomp] _ WHERE [site_ref] = " & rsCatalist!site_id & "&quot

If rsAscomp.RecordCount = 0 Then
rsAscomp.addnew
rsAscomp.fields("site_ref&quot = rsCatalist!site_id
Select Case rsCatalist!grade
Case 4
rsAscomp.fields("leaded_price&quot = rsCatalist!retail
rsAscomp.fields("leaded_date&quot = rsCatalist!date_priced
Case 2
rsAscomp.fields("unleaded_price&quot = rsCatalist!retail
rsAscomp.fields("unleaded_date&quot = rsCatalist!date_priced
Case 6
rsAscomp.fields("derv_price&quot = rsCatalist!retail
rsAscomp.fields("derv_date&quot = rsCatalist!date_priced
Case 1
rsAscomp.fields("super_price&quot = rsCatalist!retail
rsAscomp.fields("super_date&quot = rsCatalist!date_priced
Case Else
vaReply = MsgBox("Grade other than 1,2,4,6 received. Amend openupdateprices then rerun", vbOKOnly, "Attention&quot = vbOK
End Select
rsAscomp.update
Else
rsAscomp.edit
Select Case rsCatalist!grade
Case 4
rsAscomp.fields("leaded_price&quot = rsCatalist!retail
rsAscomp.fields("leaded_date&quot = rsCatalist!date_priced
Case 2
rsAscomp.fields("unleaded_price&quot = rsCatalist!retail
rsAscomp.fields("unleaded_date&quot = rsCatalist!date_priced
Case 6
rsAscomp.fields("Derv_price&quot = rsCatalist!retail
rsAscomp.fields("Derv_date&quot = rsCatalist!date_priced
Case 1
rsAscomp.fields("super_price&quot = rsCatalist!retail
rsAscomp.fields("super_date&quot = rsCatalist!date_priced
Case Else
vaReply = MsgBox("Grade other than 1,2,4,6 received. Amend openupdateprices then rerun", vbOKOnly, "Attention&quot = vbOK
End Select
rsAscomp.update
End If
rsCatalist.movenext
vapercent = (vaRead / vaTotal) * 100


Loop

Set mydb = Nothing
Set rsAscomp = Nothing
Set rsCatalist = Nothing


End Sub



Public Sub show_Percent()

Forms!import_form!Percent_complete.Caption = "Prices update " & vapercent & "% Complete"


End Sub

 

[TrojanRabbit]

Use DoEvents when it should update. When you work with recordsets, Access seems to like finish the recordset work before doing anything with the display. DoEvents says, "Let everything else finish before continuing."

There is a FAQ about DoEvents concerning progress bars.