keep user informed as code runs 3

[RobJDB]

Is there any way to let the user know what's happening as a lengthy piece of code is executed, but without requiring the user to interact at all?

I don't want to use a message box, because then the user would have to click 'ok' each time.

Thanks

 

[dwlerwill]

Create a new form (without the border etc) with a lable saying "code running please wait or something like that"

Then at the start of the code have

DoCmd.OpenForm ("formname&quot

and at the end

DoCmd.Close acForm, ("Form1&quot, acSaveNo


I use this and it is quite useful David Lerwill
"If at first you don't succeed go to the pub"

 

[RobJDB]

Sounds good, but can I update what's shown on this form as the code progresses, eg:

Searching field 1...
Updating field 1...

Searching field 2...
Updating field 2...

etc.?

 

[dwlerwill]

Yes have a second label on the form and in code at key points put


(assuming label called label0)

Label0.Caption = "Searching field 1..."
code
code
code
Label0.Caption = "Searching field 1..."
code
code
code
Label0.Caption = "Updating field 1..."


etc

dave David Lerwill
"If at first you don't succeed go to the pub"

 

[RobJDB]

Nice one Dave - good help!

Rob

 

[evalesthy]

If you want to use the pc status bar at the bottom of the screen like many programs do you can insert messages with the following code.

dim varReturn as variant

'insert message
varReturn = sysCmd(acSysCmdSetStatus, "Building file - please wait...&quot

'to clear status bar
varReturn = sysCmd(acSysCmdClearStatus)

Note = if your Error handling causes you to exit script then be sure to clear status bar or last message will remain visible.

You can insert as many messages as you like using this simple message.

 

[Escolar]

I used a progress bar to inform the user of progress when checking that all tables were linked.

The number of tables was in "Thisdb.TableDefs.Count"
VarReturn is a variant. strMSG is a string, intI is an integer
-------------------------------------------------------
strMSG = "Checking " & Thisdb.TableDefs.Count & " tables for external linkages ..."
varReturn = SysCmd(acSysCmdInitMeter, strMSG, Thisdb.TableDefs.Count)
intI = 0
For Each ThisTblDef In Thisdb.TableDefs
intI = intI + 1
varReturn = SysCmd(acSysCmdUpdateMeter, intI)
. . .
. . . code which checks the tables
. . .
Next ' ThisTblDef
varReturn = SysCmd(acSysCmdClearStatus) ' clear bar
-------------------------------------------------------

The user sees a message "Checking nn tables for external linkages ..." and a progress bar.
Each time round the loop the bar gets bigger with the "varReturn = SysCmd(acSysCmdUpdateMeter, intI)" instruction.
At the end of the loop the progress bar and the message is cleared.
Make sure to include "varReturn = SysCmd(acSysCmdClearStatus)" in any error handler you use.

Hope this helps

 

[RobJDB]

Thanks guys for all your help. I'll probably use a combination of all these suggestions.

 

[sdimov]

In each app I always have a main form called frmMain.
frmMain has lblStatus in navy backcolor, white forecolor.
I put the code below in a module named Status.

Then I have:

Public Sub Dummy()
Status.StatusBar "Please, wait. Importing..."
Call Import
Status.StatusBar "Please, wait. Calculating..."
Call Calc
Status.StatusBar "Please, wait. Exporting..."
Call Export
'Clear status
Status.StatusBar
End Sub


Public Function StatusBar(Optional Message As String = _
" Ready", _
Optional RedColor As Boolean = False) As String
Dim sSQL As String
StatusBar = Message
With Form_frmMain
.lblStatus.Caption = Space(3) & StatusBar
If RedColor = False Then
.lblStatus.BackColor = 7077888
Else
.lblStatus.BackColor = vbRed
End If
.Repaint
End With
End Function