Create Message in Status bar

[Laetitia]

Hi All

Please can anyone advise whether it is possible to display messages in
the status bar.

I have a number of update queries which will be run and need to find a
way to identify which field is currently being updated so that I can
monitor the progress.

I know that I can use InputBox to display the variable table and field
names, but do not want to be prompted to confirm each time, so hope that
it is possible to display this in the status bar without interrupting
the processing.

This is the code :-

Function ReplaceCurrency()
DoCmd.SetWarnings False

On Error GoTo Error

Static dbs As Database, rst1 As Recordset, rst2 As Recordset, rst3 As
Recordset, strSql1 As String, strSql2 As String, strSql3 As String
Dim VPlan As Long
Dim VRec, VRec3 As Long
Dim tblCurrent As TableDef
Dim intCounter As Integer
Dim vFlag As String
Dim strField, strTable, strSql, strXref As String

Set dbs = CurrentDb

strSql1 = "SELECT tblFields.FileCode, tblFields.TableName,
tblFields.IsamFieldName, tblFields.ColumnName, tblFields.Description " &
_
"FROM tblFields;"

strXref = "tblXrefAlrode"
'"tblXrefElands"
'"tblXrefMidrand"
'"tblXrefUtilities"
'"tblXrefHoldings"

Set rst1 = dbs.OpenRecordset(strSql1)
If rst1.RecordCount 0 Then
rst1.MoveFirst
strTable = "dbo_" & rst1!TableName
strField = rst1!ColumnName

strSql = "UPDATE " & strTable & " INNER JOIN " & strXref & " ON " &
strTable & "." & strField & " = " & strXref & ".Old SET " & strTable &
"." & strField & " = " & strXref & "![New];"
DoCmd.RunSQL "UPDATE " & strTable & " INNER JOIN " & strXref & " ON
trim(" & strTable & "." & strField & ") = " & strXref & ".Old SET " &
strTable & "." & strField & " = " & strXref & "![New];", 0

NextRec:
rst1.MoveNext

While (Not (rst1.EOF))
strTable = "dbo_" & rst1!TableName
strField = rst1!ColumnName
strSql = "UPDATE " & strTable & " INNER JOIN " & strXref & " ON " &
strTable & "." & strField & " = " & strXref & ".Old SET " & strTable &
"." & strField & " = " & strXref & "![New];"
DoCmd.RunSQL "UPDATE " & strTable & " INNER JOIN " & strXref & " ON
trim(" & strTable & "." & strField & ") = " & strXref & ".Old SET " &
strTable & "." & strField & " = " & strXref & "![New];", 0
rst1.MoveNext
Wend

End If
rst1.Close
DoCmd.OpenQuery "updDescription", acViewNormal

MsgBox "All Done - Thank You"
Exit Function

Error:
MsgBox Err.Description
strTable = InputBox("Table Name", , strTable)
strField = InputBox("Field Name", , strField)
strSql = InputBox("String", , strSql)
Resume NextRec

End Function

Thanks for your interest.
Laetitia

*** Sent via Developersdex http://www.developersdex.com ***

[paii, Ron]

Dim s As Variant

s = SysCmd(acSysCmdSetStatus, "Some Message") ' Display message

.....

s = SysCmd(acSysCmdClearStatus) ' Clear the message
ps
Why not use currentdb.execute instead of DoCmd.RunSQL?
"Laetitia" <no****@devdex.comwrote in message
news:12**************@news.newsfeeds.com...

Hi All

Please can anyone advise whether it is possible to display messages in
the status bar.

I have a number of update queries which will be run and need to find a
way to identify which field is currently being updated so that I can
monitor the progress.

I know that I can use InputBox to display the variable table and field
names, but do not want to be prompted to confirm each time, so hope that
it is possible to display this in the status bar without interrupting
the processing.

This is the code :-

Function ReplaceCurrency()
DoCmd.SetWarnings False

On Error GoTo Error

Static dbs As Database, rst1 As Recordset, rst2 As Recordset, rst3 As
Recordset, strSql1 As String, strSql2 As String, strSql3 As String
Dim VPlan As Long
Dim VRec, VRec3 As Long
Dim tblCurrent As TableDef
Dim intCounter As Integer
Dim vFlag As String
Dim strField, strTable, strSql, strXref As String

Set dbs = CurrentDb

strSql1 = "SELECT tblFields.FileCode, tblFields.TableName,
tblFields.IsamFieldName, tblFields.ColumnName, tblFields.Description " &
_
"FROM tblFields;"

strXref = "tblXrefAlrode"
'"tblXrefElands"
'"tblXrefMidrand"
'"tblXrefUtilities"
'"tblXrefHoldings"

Set rst1 = dbs.OpenRecordset(strSql1)
If rst1.RecordCount 0 Then
rst1.MoveFirst
strTable = "dbo_" & rst1!TableName
strField = rst1!ColumnName

strSql = "UPDATE " & strTable & " INNER JOIN " & strXref & " ON " &
strTable & "." & strField & " = " & strXref & ".Old SET " & strTable &
"." & strField & " = " & strXref & "![New];"
DoCmd.RunSQL "UPDATE " & strTable & " INNER JOIN " & strXref & " ON
trim(" & strTable & "." & strField & ") = " & strXref & ".Old SET " &
strTable & "." & strField & " = " & strXref & "![New];", 0

NextRec:
rst1.MoveNext

While (Not (rst1.EOF))
strTable = "dbo_" & rst1!TableName
strField = rst1!ColumnName
strSql = "UPDATE " & strTable & " INNER JOIN " & strXref & " ON " &
strTable & "." & strField & " = " & strXref & ".Old SET " & strTable &
"." & strField & " = " & strXref & "![New];"
DoCmd.RunSQL "UPDATE " & strTable & " INNER JOIN " & strXref & " ON
trim(" & strTable & "." & strField & ") = " & strXref & ".Old SET " &
strTable & "." & strField & " = " & strXref & "![New];", 0
rst1.MoveNext
Wend

End If
rst1.Close
DoCmd.OpenQuery "updDescription", acViewNormal

MsgBox "All Done - Thank You"
Exit Function

Error:
MsgBox Err.Description
strTable = InputBox("Table Name", , strTable)
strField = InputBox("Field Name", , strField)
strSql = InputBox("String", , strSql)
Resume NextRec

End Function

Thanks for your interest.
Laetitia

*** Sent via Developersdex http://www.developersdex.com ***

[rkc]

On Aug 13, 2:54 pm, Laetitia <[e-mail]:

Hi All

Please can anyone advise whether it is possible to display messages in
the status bar.

I have a number of update queries which will be run and need to find a
way to identify which field is currently being updated so that I can
monitor the progress.

I know that I can use InputBox to display the variable table and field
names, but do not want to be prompted to confirm each time, so hope that
it is possible to display this in the status bar without interrupting
the processing.

This is the code :-

<snip>

SysCmd acSysCmdSetStatus, "Look a status bar! Didn't know that was
there."

writes to the status bar. You probably won't see much more than the
last
text sent though. If I wanted to monitor something like that I would
consider writing the info to a textbox on the form the code is run
from.
You'd probably have to call Repaint after each write to see it before
the
loop ends though.

[Laetitia]

Hi Ron.

Thanks. I will give it a bash.

ps
Why not use currentdb.execute instead of DoCmd.RunSQL?

Force of habit, I guess. Old dogs do not learn new tricks too easily :)

*** Sent via Developersdex http://www.developersdex.com ***

[Laetitia]

Hi rkc.

Thanks. I am running the code from a macro, but will consider creating a
form as you suggested.

Thanks again.
*** Sent via Developersdex http://www.developersdex.com ***

[Laetitia]

Hi again, rkc.

The form works a treat. I should have thought of that solution but plead
sleep deprivation in my defence.

You were quite correct about the status bar message. The standard
"running query" overwrites my text anyway, but at least I know how to
create the text now :)

Thanks again for your kind assitance.

*** Sent via Developersdex http://www.developersdex.com ***