Updating Records.....Show Progress in a form error

[PhilOwens]

Morning/Afternoon all,

Having slight problem with showing users the progress of a query.....

Bit of background....The database allows users to view the status of documents i.e. Completion date, revision etc. The project manager has an MS Project file in which he changes the estimated date of comepletion of each document.

I currently have Ms Project auto generate a database table everytime it is saved. This table is linked into my database and is updated when the filter form is opened (see code below).

My problem is that as the information is being passed across the local LAN it can take a bit of time (15-20 secs atm, but I anticipate that this will grow as the database expands), and as users are usually so patient I want to inform them that the database hasnt crashed by displaying some information

I have tried several different methods (one below is probably the most simple of them yet) but I keep ending up with the same error....."2001 - You canceled the previous operation" Or

I know that the problem is caused because records are being modified because if I take out anything to do with modifying the records, the status is shown perfectly.

Open to any suggestions? Thanks in advance!

Private Sub Form_Load()

fixeddate = "24/05/2007" 'DO NOT CHANGE THIS VALUE!!!!!!!!

imax = DMax("[unique_id]", "[tbl_Projectimport]")

tbox_OutOf = DCount("[unique_id]", "[tbl_Projectimport]")

i = 1

RecordNo = 1

Do While i <= imax

ProjectDate = DLookup("[finish_date]", "[tbl_Projectimport]", "[Unique_ID] = UniqueID") ' & Me.Unique_ID)

If IsNull(ProjectDate) Then
Exit Do
End If

ProjectDate = Left(ProjectDate, 10)

UpdatedDate = ProjectDate

filterdate = DateDiff("d", fixeddate, ProjectDate)

DoCmd.GoToRecord , , acNext

tbox_CurrentNo = RecordNo

i = i + 1

RecordNo = RecordNo + 1

Loop

DoCmd.Close

DoCmd.OpenForm "DRL_Filter"

End Sub

[MMcCarthy]

Sorry Phil

Without Access to your database this code is out of context and I can't actually tell what it is doing.

What operation is being preformed? Technically I mean.

You say you want the users to be informed of the status of this operation with some kind of notice. This is usually done by tying the code of the operation to a form load event of a simple form with a sign on it informing users of the status. The form can then be closed at the end of the process.

Without a better understanding of what is going on I can't really help further.

[ADezii]

Morning/Afternoon all,

Having slight problem with showing users the progress of a query.....

Bit of background....The database allows users to view the status of documents i.e. Completion date, revision etc. The project manager has an MS Project file in which he changes the estimated date of comepletion of each document.

I currently have Ms Project auto generate a database table everytime it is saved. This table is linked into my database and is updated when the filter form is opened (see code below).

My problem is that as the information is being passed across the local LAN it can take a bit of time (15-20 secs atm, but I anticipate that this will grow as the database expands), and as users are usually so patient I want to inform them that the database hasnt crashed by displaying some information

I have tried several different methods (one below is probably the most simple of them yet) but I keep ending up with the same error....."2001 - You canceled the previous operation" Or

I know that the problem is caused because records are being modified because if I take out anything to do with modifying the records, the status is shown perfectly.

Open to any suggestions? Thanks in advance!

Private Sub Form_Load()

fixeddate = "24/05/2007" 'DO NOT CHANGE THIS VALUE!!!!!!!!

imax = DMax("[unique_id]", "[tbl_Projectimport]")

tbox_OutOf = DCount("[unique_id]", "[tbl_Projectimport]")

i = 1

RecordNo = 1

Do While i <= imax

ProjectDate = DLookup("[finish_date]", "[tbl_Projectimport]", "[Unique_ID] = UniqueID") ' & Me.Unique_ID)

If IsNull(ProjectDate) Then
Exit Do
End If

ProjectDate = Left(ProjectDate, 10)

UpdatedDate = ProjectDate

filterdate = DateDiff("d", fixeddate, ProjectDate)

DoCmd.GoToRecord , , acNext

tbox_CurrentNo = RecordNo

i = i + 1

RecordNo = RecordNo + 1

Loop

DoCmd.Close

DoCmd.OpenForm "DRL_Filter"

End Sub

I agree with Mary and an also very confused. In General Terms, if you want a visual indicator to give the User some idea of the progress of an operation, you can use the built-in Progress Meter within Access. It's not much, but it is better than no indicator at all. Here is the general idea:

'----------------- Code Prior to Loop ------------------
Dim varRet As Variant, strShow As String

strShow = "Stay tune while I do some calculating..."

'Initialize Meter with the Maximum Value (imax)
varRet = SysCmd(acSysCmdInitMeter, strShow, imax)
'----------------------------------------------------------------


'--------------- Code Placed Within Loop ---------------
'Progress Meter updated with each Increment of RecordNo
varRet = SysCmd(acSysCmdUpdateMeter, RecordNo)
'----------------------------------------------------------------


'------------------ Code Placed After ------------------
varRet = SysCmd(acSysCmdRemoveMeter)
'----------------------------------------------------------------