SysCmd(acSysCmdSetStatus, message): Status Bar goes Blank during MakeTable

[SteveApa]

Hi All!

I have code that displays a message during a macro run (I like doing it so the user can see stuff is happening!) But, during a "Make Table", the status goes blank and then returns after the make table is complete???

        Status_Message_Open True, "Creating Heat Map... Running Query" 'this function uses SysCmd(acSysCmdSetStatus, message)
        DoCmd.SetWarnings False
        ' run make table query
        DoCmd.OpenQuery "9d 2c7b) Heat Map"
        DoCmd.SetWarnings True

The message "Creating Heat Map... Running Query" pops up properly before the query runs and while the query is running, but while the query is actually writing the results to the table, the status bar goes blank. Then, when the make table is done writing, the message pops back up??

Thanks.
Steve
Harrisburg, PA

[davegri]

Try reversing the first two lines.

[SteveApa]

Reversing the 1st 2 lines did not work.. The "docmd.setwarnings false" is so the make table query runs without any user needed input

DoCmd.SetWarnings FalseStatus_Message_Open True, "Creating Heat Map... Running Query"
' run make table query
DoCmd.OpenQuery "9d 2c7b) Heat Map"
DoCmd.SetWarnings True

[isladogs]

Revert to original code in post #1.
Insert the line DoEvents after the first line to give the CPU time to run the previous task before continuing
I think you will need code to clear your message after the query has completed.

Two other points
1. You really shouldn't use spaces or special characters like ) in your object names.
2. I refer to have a message on the form when code like this runs.
I use a label with 20pt bold italic magenta text so it stands out. Make it visible just whilst the code runs

[SteveApa]

It looks like when the query runs, the "Run Query" message in the same status bar overwrites... Any way to turn that off?? #1 message showing, #2 "Run Query" Showing...
#1

#2

[isladogs]

I have my own status bar message function which doesn't get overwritten IIRC.
If you want I'll dig it out later.
However status bar messages are not very obvious and therefore easy to miss.
Forthat reason, I do recommend my alternative approach using a label with your message as it's caption

[ssanfu]

Just curious:

1) Is this a multi-user dB?

2) Why are you executing a Make Table query? Constantly recreating tables is a good method to introduce corruption into the dB. You should create the table once, then delete the RECORDS and execute an Append Query to add records each tome you need new data.

[isladogs]

Hi Steve

2) Why are you executing a Make Table query? Constantly recreating tables is a good method to introduce corruption into the dB. You should create the table once, then delete the RECORDS and execute an Append Query to add records each tome you need new data.

Whilst I agree with every word of that, one reason for doing so is that if you have a very large number of records to add, a make table query is far faster than an append query.
Also despite many articles to the contrary, file bloat using append queries is just as great as using make table queries.

However, that all has to be factored against the undeniable fact that if you keep doing it your database will become unstable & may well get corrupted as you rightly say.

[Micron]

I don't have a big enough table to test the status bar but what about

On Error GoTo errHandler

DoCmd.SetWarnings False
Status_Message_Open True, "Creating Heat Map... Running Query"
Application.Echo False " "
' run make table query
DoCmd.OpenQuery "9d 2c7b) Heat Map"

exitHere:
DoCmd.SetWarnings True
Status_Message_Open True, " "
Application.Echo True " "
Exit Sub/Function

errHandler:
handle errors with message box here
Resume exitHere

End Sub/Function

I presume Status_Message_Open is a UDF call (user defined function). You'd have to employ an error handler and to be almost certain that warnings and echo won't be left turned off if the code fails. I'd never turn off warnings without it. Even then, I mostly use .Execute method.

[ssanfu]

@Colin
Hi, I question the use of make table queries because I have had a few dBs (the FE) get so corrupt that I had to go back 5 mods to get a FE that I could recover.
Once is OK to help create the table, but over and over (IMHO) is bad.



-----------------------------------------------------------------------------------
About the sys message....

All I use is (multiple exports in same sub)

     Application.SysCmd acSysCmdSetStatus, "Exporting: Cash outs"
        'the Export Code

     Application.SysCmd acSysCmdSetStatus, "Exporting: Statement Monthly Summary"
        'next Export Code

     Application.SysCmd acSysCmdSetStatus, "Exporting: Expanded Monthly Statement"
        'Last Export Code


    ' clear status bar message
    Application.SysCmd acSysCmdClearStatus

And I am with Micron - I use .Execute method - cannot remember if I ever used the .OpenQuery or .RunSQL commands.



It would be interesting to see the "Status_Message_Open" UDF code.

[isladogs]

Normally I avoid them even though append queries are much slower
However, I have one app that has to use make table queries due to its special nature.
This is my JSON app whereby JSON files are imported, analysed, transformed & added to newly designed/created tables.
All automated with almost zero user intervention
Whilst doing this repeatedly during development work for testing purposes, it occasionally became unstable as a result.
Thankfully decompiling fixes that every time
In everyday use, no client who has purchased it has ever reported it an issue although the user guide states it as a potential issue (together with the solution)

I also use db.Execute though DoCmd.RunSQL or DoCmd.OpenQuery shouldn't matter in this context.

FWIW, here is my status bar code (place in a standard module)

Public Sub StatusBar(Optional msg As Variant)

On Error GoTo ErrHandler
Dim temp As Variant


' if the Msg variable is omitted or is empty, return the control of the status bar to Access


    If Not IsMissing(msg) Then
        If msg <> "" Then
            temp = SysCmd(acSysCmdSetStatus, msg)
        Else
            temp = SysCmd(acSysCmdClearStatus)
        End If
    Else
        temp = SysCmd(acSysCmdClearStatus)
    End If
    
ExitHandler:
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " in StatusBar procedure : " & Err.Description
    Resume ExitHandler
End Sub

Typical usage:
StatusBar "Updating all grade averages . . ." ' at start of procedure
StatusBar "" 'at end of procedure

BUT I still MUCH prefer using a large brightly coloured label on the form so users will notice it.
Status bar messages are too easily missed IMO
If the task has multiple parts, I'd also use a progress bar
Finally, I also use the Hourglass as another visual sign something is happening during long procedures

[ssanfu]

BUT I still MUCH prefer using a large brightly coloured label on the form so users will notice it.

I do the same thing. I have a text box (bold, red text -> INITIALIZING) when an import CSV routine starts because it takes a while to do some validation before it starts processing. When processing starts, the text box is hidden.




BTW.... I'm also going to steal your StatusBar code....

[isladogs]

You're welcome though I also stole it from someone else. Can't remember who as it was years ago.