Flickering Form during record update

S

Supa Hoopsa

I am trying to stop my form from flickering during a
record update. I need to find the original record after
running a stored procedure to update the prices and
consequently running a "form.requery" to reflect the
changes. My code is as follows:

Code: 
DoCmd.Hourglass True
Application.Echo False

Dim intTrussKeySave As Double
intTrussKeySave = Me.txtTrussKey

sql = "SPPricingTrusses(" & Truss_Tag & "," & Forms
("frmTrusses").JobIndex & ")"
rst1.Open sql, Conn, adOpenForwardOnly,
adLockOptimistic, adCmdStoredProc

If CurrentProject.AllForms("FrmTrusses").IsLoaded =
True Then
Forms("FrmTrusses").Requery
Dim rs As New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.LockType = adLockOptimistic
rs.Open Me.RecordSource
rs.Find "TrussKey = " & intTrussKeySave, ,
adSearchForward, 1

If Not rs.EOF Then
Set Me.Recordset = rs
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


Application.Echo True
DoCmd.Hourglass False

As you can see in the code above in bold, I am using the
application.echo false/true to try and stop the screen
flickering while the update is taking place.
Unfortunately this is not having any effect. What I see
happening on the screen is the original values first
being replaced by #Name?, then the values of the first
record in the recordset and then the correct values after
the update has taken place.

So while the code works and returns the user to the
original record, the flickering of other values I think
will cause a bit of unease.

Can anyone give me any suggestions as to how to stop this
flickering?
 
S

Steve Jorgensen

Besides the flicker, this code should not even work because you're trying to
copy a Bookmark between 2 different recordsets. The fact that they are based
on the same recordsource is not enough. Furthermore, I presume you are
talking about an ADP because otherwise, you are actually trying to apply an
ADO bookbark to a DAO recordset. Finally, in an ADP, code like this would
have the additional problem that with more than 50 rows, it might fail because
all rows are not immediately retrieved after a requery, and the find will fail
if the row is not one of the first 50.

Since you are updating, not adding records, if you are in an MDB, you can use
<form>.Refresh, and if you are in an ADP, I think it's
<form>.Recordset.Resync. In either case, the current record pointer will not
be lost, so you won't need to find the original record again.

If you do need to requery, then in an ADP, your best bet is to retrieve the
recordset in code, find the row, then set the form's Recordset property to
that recordset rather than requerying the form and seeking to the same row
(even if that bookmark thing was reliable). That avoids the 50-row thing and
prevents having to run the same query twice, once for the form, and once for a
set to do the .Find in.

I am trying to stop my form from flickering during a
record update. I need to find the original record after
running a stored procedure to update the prices and
consequently running a "form.requery" to reflect the
changes. My code is as follows:

Code: 
DoCmd.Hourglass True
Application.Echo False

Dim intTrussKeySave As Double
intTrussKeySave = Me.txtTrussKey

sql = "SPPricingTrusses(" & Truss_Tag & "," & Forms
("frmTrusses").JobIndex & ")"
rst1.Open sql, Conn, adOpenForwardOnly,
adLockOptimistic, adCmdStoredProc

If CurrentProject.AllForms("FrmTrusses").IsLoaded =
True Then
Forms("FrmTrusses").Requery
Dim rs As New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.LockType = adLockOptimistic
rs.Open Me.RecordSource
rs.Find "TrussKey = " & intTrussKeySave, ,
adSearchForward, 1

If Not rs.EOF Then
Set Me.Recordset = rs
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


Application.Echo True
DoCmd.Hourglass False

As you can see in the code above in bold, I am using the
application.echo false/true to try and stop the screen
flickering while the update is taking place.
Unfortunately this is not having any effect. What I see
happening on the screen is the original values first
being replaced by #Name?, then the values of the first
record in the recordset and then the correct values after
the update has taken place.

So while the code works and returns the user to the
original record, the flickering of other values I think
will cause a bit of unease.

Can anyone give me any suggestions as to how to stop this
flickering?
Click to expand...
 
S

SupaHoopsa

Thanks for this advice Steve. I have used the
Recordset.Resync command as you suggested and it
certainly eliminates the #name? being displyed. The
screen does still flicker a little bit, but I can handle
that by using an hourglass & displaying a label telling
the user that prices are being updated.

One question though, do you know much about
the "application.echo" command. Reading the help, I
thought this would eliminate all flickering, but to be
honest it seems to make absolutely no difference at all.

Any ideas?
-----Original Message-----
Besides the flicker, this code should not even work because you're trying to
copy a Bookmark between 2 different recordsets. The fact that they are based
on the same recordsource is not enough. Furthermore, I presume you are
talking about an ADP because otherwise, you are actually trying to apply an
ADO bookbark to a DAO recordset. Finally, in an ADP, code like this would
have the additional problem that with more than 50 rows, it might fail because
all rows are not immediately retrieved after a requery, and the find will fail
if the row is not one of the first 50.

Since you are updating, not adding records, if you are in an MDB, you can use
<form>.Refresh, and if you are in an ADP, I think it's
<form>.Recordset.Resync. In either case, the current record pointer will not
be lost, so you won't need to find the original record again.

If you do need to requery, then in an ADP, your best bet is to retrieve the
recordset in code, find the row, then set the form's Recordset property to
that recordset rather than requerying the form and seeking to the same row
(even if that bookmark thing was reliable). That avoids the 50-row thing and
prevents having to run the same query twice, once for the form, and once for a
set to do the .Find in.

I am trying to stop my form from flickering during a
record update. I need to find the original record after
running a stored procedure to update the prices and
consequently running a "form.requery" to reflect the
changes. My code is as follows:

Code: 
DoCmd.Hourglass True
Application.Echo False

Dim intTrussKeySave As Double
intTrussKeySave = Me.txtTrussKey

sql = "SPPricingTrusses(" & Truss_Tag & "," & Forms
("frmTrusses").JobIndex & ")"
rst1.Open sql, Conn, adOpenForwardOnly,
adLockOptimistic, adCmdStoredProc

If CurrentProject.AllForms("FrmTrusses").IsLoaded =
True Then
Forms("FrmTrusses").Requery
Dim rs As New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.LockType = adLockOptimistic
rs.Open Me.RecordSource
rs.Find "TrussKey = " & intTrussKeySave, ,
adSearchForward, 1

If Not rs.EOF Then
Set Me.Recordset = rs
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


Application.Echo True
DoCmd.Hourglass False

As you can see in the code above in bold, I am using the
application.echo false/true to try and stop the screen
flickering while the update is taking place.
Unfortunately this is not having any effect. What I see
happening on the screen is the original values first
being replaced by #Name?, then the values of the first
record in the recordset and then the correct values after
the update has taken place.

So while the code works and returns the user to the
original record, the flickering of other values I think
will cause a bit of unease.

Can anyone give me any suggestions as to how to stop this
flickering?
Click to expand...

.
Click to expand...
 
S

Steve Jorgensen

Thanks for this advice Steve. I have used the
Recordset.Resync command as you suggested and it
certainly eliminates the #name? being displyed. The
screen does still flicker a little bit, but I can handle
that by using an hourglass & displaying a label telling
the user that prices are being updated.

One question though, do you know much about
the "application.echo" command. Reading the help, I
thought this would eliminate all flickering, but to be
honest it seems to make absolutely no difference at all.

Any ideas?
Click to expand...

Try this:

<form>.Painting = False
.... do the resync...
<form>.Painting = True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a Duplicate Record 5
Insert not working 2
combobox, NotInList, update form 2
Stopping new record creation 6
Update with Do Until rsTable.EOF allways leave one record "behind" 11
Autofill subform when move to new record on Main form 30
Update form on record change 1
update record in mdb.. 1

Top