Status Bar Text 1

[OrWolf]

I'm attempting to run a query and have the status bar just display an informative message during the run. However, my message keeps getting replaced by the 'Run Query . . ' message. As you can see from my code below, I've been trying everything I can think of to solve this. Any ideas?


RetVal = SysCmd(acSysCmdSetStatus, "Searching for duplicate scenarios...&quot

Echo False
DoCmd.SetWarnings False
RetVal = SysCmd(acSysCmdSetStatus, "Searching for duplicate scenarios...&quot
DoCmd.OpenQuery "RA_ScenarioFindDups"
RetVal = SysCmd(acSysCmdSetStatus, "Searching for duplicate scenarios...&quot
DoCmd.SetWarnings True
Echo True

 

[ghubbell]

Hi OrWolf,
This might go:

On Error Goto NeedErrorHandler

DoCmd.SetWarnings False
RetVal = SysCmd(acSysCmdInitMeter, "Searching for duplicate scenarios", 100)
RetVal = SysCmd(acSysCmdUpdateMeter, 40)
DoCmd.OpenQuery "RA_ScenarioFindDups"
RetVal = SysCmd(acSysCmdUpdateMeter, 100)
DoCmd.SetWarnings True

AnExit:
RetVal = SysCmd(acSysCmdRemoveMeter)
Exit Sub

NeedErrorHandler:
MsgBox Err.Number
Resume AnExit

I put the error handler in so the status bar will never get "stuck", you play with the numbers in between. This is really impressive when you do it with a whole bunch of queries or what-have-you, and you really see the progress. Hope it's to your liking, Gord
ghubbell@total.net

 

[OrWolf]

Now shows shows the meter quickly then nothing while the query runs, and then the meter at 100%. The query is a make-table query that is required in order to check for, and report on, duplicate records. If that sheds any light on the subject? If you have any other ideas throw them my way. Thanks for the help!

 

[ghubbell]

Wellll.... that's the problem showing the meter when you just have it doing one thing. Let me see here.
You could do this with an SQL statement, count the records, have it calculate this versus 100%, as the SQL runs, increment the meter based on the record count. Might be too far away from your desire just to show a message. Let me know if you'd like to do this but for your case I guess it would be best to simply:

DoCmd.SetWarnings False
RetVal = SysCmd(acSysCmdSetStatus, "Searching for duplicate scenarios...&quot
DoCmd.OpenQuery "RA_ScenarioFindDups"
DoCmd.SetWarnings True

AnExit:
SysCmd(acSysCmdClearStatus)

Sorry if I confused you/us but do let me know if you want to do the progress meter idea. Gord
ghubbell@total.net

 

[OrWolf]

I would love to know how to have the status bar meter work with an SQL statement. I would include my SQL statement here, but it's fairly complex, thus lengthy. If you can provide a sample, that'd be grand!

 

[ghubbell]

I started to prepare you something, forgot a term, searched (F1) Visual Basic help for SysCmd and found an example ready to go. I'll paste it here without MS permission, however I don't believe they would mind:

SysCmd Method Example

The following example creates a snapshot-type Recordset object, reads each record, and displays a progress meter showing the current relative position in the snapshot.

Function ReadRecords(strTableName As String) As Integer
Const conBadArgs = -1
Dim dbs As Database, rst As Recordset
Dim lngCount As Long, strMsg As String
Dim varReturn As Variant, lngX As Long

ReadRecords = 0
If strTableName <> &quot;&quot; Then
DoCmd.Hourglass True
Set dbs = CurrentDb
On Error Resume Next
Set rst = dbs.OpenRecordSet(strTableName)
' Get record count.
rst.MoveLast
rst.MoveFirst
If Err Then
ReadRecords = conBadArgs
End If
lngCount = rst.RecordCount
On Error GoTo 0
If lngCount Then
strMsg = &quot;Reading &quot; & UCase$(strTableName) & &quot;...&quot;
varReturn = SysCmd(acSysCmdInitMeter, strMsg, lngCount)
' Display message in status bar.
For lngX = 1 To lngCount
varReturn = SysCmd(acSysCmdUpdateMeter, lngX)
' Update meter.
. ' Do something with record.
.
.
rst.MoveNext ' Go to next record.
Next lngX
varReturn = SysCmd(acSysCmdClearStatus)
GoSub CloseObjects
ReadRecords = lngCount ' Return number of records.
Exit Function
End If
End If
' Not found or contains no records.
strMsg = &quot;Table '&quot; & strTableName & &quot;'not found or contains no records.'&quot;
MsgBox strMsg, vbInformation, &quot;ReadRecords&quot;
GoSub CloseObjects
Exit Function
CloseObjects:
On Error Resume Next
rst.Close
dbs.Close
On Error GoTo 0
DoCmd.Hourglass False
Return
End Function

That saves some typing!
Again, if you need some help in implementing this for your situation let me know.
Gord
ghubbell@total.net