Can't Get Echo To Work

[Neil]

I need to temporarily remove the recordset from my form while I run some
queries (have locking problems). So I turned Echo off before removing the
recordset to avoid getting #Name? in the bound controls. However, even
though I turned Echo off, I'm still getting #Name? until I reset the
recordset. Here's the code I'm using:

DoCmd.Hourglass True
Application.Echo False
Me.RecordSource = ""
<run queries here>
<reset recordsource here>

Anything I'm doing wrong here? (I've also tried DoCmd.Echo False, even
though OLH recommends using Application.Echo.)

Thanks,

Neil

 

[Allen Browne]

Instead of removing the recordset, just force a save or undo:
If Me.Dirty Then
Me.Dirty = False ' Me.Undo
End If
That should solve the locking problem.

 

[Neil]

No, it won't. I do do a save before running my queries, but to no avail.
These are SQL queries run as pass-throughs, and, for some reason, this form,
which is a form and a subform, is putting a lock on the table and not
allowing the SPs to run.

So, back to this situation, Echo is supposed to freeze screen updating, and
removing the recordset after Echo = False should not result in any updating.
And I seem to remember using it in the past this way with good results. But
can't get this to work for some reason.

Neil

 

[Trevor Best]

I need to temporarily remove the recordset from my form while I run some
queries (have locking problems). So I turned Echo off before removing the
recordset to avoid getting #Name? in the bound controls. However, even
though I turned Echo off, I'm still getting #Name? until I reset the
recordset. Here's the code I'm using:

DoCmd.Hourglass True
Application.Echo False
Me.RecordSource = ""
<run queries here>
<reset recordsource here>

Anything I'm doing wrong here? (I've also tried DoCmd.Echo False, even
though OLH recommends using Application.Echo.)

Thanks,

Neil

Echo only refers to the status bar text AFAIK.

The code (below my sig) is from a test form with a few textboxes and two
buttons (button names in _click procedures). You should be able to adapt
that to your needs, basically it saves all the controlsources of the
controls befor unbinding the form, the second button puts them all back
on again.

--
[OO=00=OO]


Option Compare Database
Option Explicit

Dim mstrRecordSource As String
Dim mstrCtlSrc() As String

Private Sub Form_Open(Cancel As Integer)
mstrRecordSource = Me.RecordSource
End Sub
Private Sub cmdUnBound_Click()
Dim i As Long
ReDim mstrCtlSrc(Me.Controls.Count)

On Error Resume Next
' cause some of these WILL error unless you
' check their type, etc, long winded
For i = 0 To Me.Controls.Count - 1
mstrCtlSrc(i) = Me.Controls(i).ControlSource
Me.Controls(i).ControlSource = ""
Next
Me.RecordSource = ""

End Sub

Private Sub cmdBound_Click()
Dim i As Long

On Error Resume Next
Me.RecordSource = mstrRecordSource
For i = 0 To Me.Controls.Count - 1
Me.Controls(i).ControlSource = mstrCtlSrc(i)
Next
End Sub

 

[Trevor Best]

No, it won't. I do do a save before running my queries, but to no avail.
These are SQL queries run as pass-throughs, and, for some reason, this form,
which is a form and a subform, is putting a lock on the table and not
allowing the SPs to run.

I generally find moving to a new record is sufficient, have you tried
that before anything more drastic?

 

[Neil]

Nope, that didn't work. For some reason, the form itself is putting a lock
on the whole table. Moving to the previous record didn't help. (Plus, it
seems confusing to the user that, while the queries are being run related to
the current record, that they're seeing a different record in the form, and
they might think that they clicked the button to run the queries while in
the wrong record.)

Someone else recommended setting the recordsource to a non-existent record
(ID=0), and that seems to work OK. I end up with a blank screen, but there
are no locks and no #Name?'s.

Thanks,

Neil

 

[Neil]

Echo only refers to the status bar text AFAIK.

No, I believe it refers to the whole screen. MS Help talks about being
locked out of an app if you turn echo off, but don't turn it back on, and
recommend having a shortcut key to turn it back on in case your code exits
without doing so. So I'm pretty sure it's a whole screen thing (and my use
of it in the past agrees with that, though I haven't used it in years).

The code (below my sig) is from a test form with a few textboxes and two
buttons (button names in _click procedures). You should be able to adapt
that to your needs, basically it saves all the controlsources of the
controls befor unbinding the form, the second button puts them all back on
again.

Hmm, well that's interesting. That would certainly be the best way to go.
Thanks for taking the time to do that!

Neil

--
[OO=00=OO]


Option Compare Database
Option Explicit

Dim mstrRecordSource As String
Dim mstrCtlSrc() As String

Private Sub Form_Open(Cancel As Integer)
mstrRecordSource = Me.RecordSource
End Sub
Private Sub cmdUnBound_Click()
Dim i As Long
ReDim mstrCtlSrc(Me.Controls.Count)

On Error Resume Next
' cause some of these WILL error unless you
' check their type, etc, long winded
For i = 0 To Me.Controls.Count - 1
mstrCtlSrc(i) = Me.Controls(i).ControlSource
Me.Controls(i).ControlSource = ""
Next
Me.RecordSource = ""

End Sub

Private Sub cmdBound_Click()
Dim i As Long

On Error Resume Next
Me.RecordSource = mstrRecordSource
For i = 0 To Me.Controls.Count - 1
Me.Controls(i).ControlSource = mstrCtlSrc(i)
Next
End Sub

 

[Ken Snell [MVP]]

Be sure that the form's Record Locking property is set to No Locks.

 

[Trevor Best]

No, I believe it refers to the whole screen. MS Help talks about being
locked out of an app if you turn echo off, but don't turn it back on, and
recommend having a shortcut key to turn it back on in case your code exits
without doing so. So I'm pretty sure it's a whole screen thing (and my use
of it in the past agrees with that, though I haven't used it in years).

In that case it's broken then

 

[Neil]

It's a SQL back end, so the record locking property has no effect. But,
FWIW, it is set to no locks.

 

[RD]

Echo only refers to the status bar text AFAIK.

<snip>

That's true of the DoCmd object but the Echo method of the Application object is
supposed to freeze screen repainting.

Example from Help:

The following example uses the Echo method to prevent the screen from being
repainted while certain operations are underway. While the procedure opens a
form and minimizes it, the user only sees an hourglass icon indicating that
processing is taking place, and the screen isn't repainted. When this task is
completed, the hourglass changes back to a pointer and screen repainting is
turned back on.

Public Sub EchoOff()

' Open the Employees form minimized.
Application.Echo False
DoCmd.Hourglass True
DoCmd.OpenForm "Employees", acNormal
DoCmd.Minimize
Application.Echo True
DoCmd.Hourglass False

End Sub

 

[Trevor Best]

<snip>

That's true of the DoCmd object but the Echo method of the Application object is
supposed to freeze screen repainting.

Ah. I see.

I would have called it Application.ScreenUpdating like the rest of
Office VBA.

There's nothing like consistency, and that's nothing like consistency