Solved

Microsoft Access screen flashing on update

Posted on 2012-03-21
10
483 Views
Last Modified: 2012-03-22
Hi

The code below works fine execpt that the screen flashes when the code executes.

Please make it stop!


Private Sub MyTag_Click()
Dim rs As DAO.Recordset: Set rs = GetRecordSet(Me.[Stock #])
If rs.RecordCount = 0 Then Exit Sub
rs.Edit

If LCase(Nz(rs![MyTag])) = "y" Then
rs![MyTag] = "":
Else: rs![MyTag] = "Y": End If

rs.Update: rs.Close: Set rs = Nothing
MyTag.SelLength = 0: MyTag.SelStart = 0
Refresh
End Sub
0
Comment
Question by:peispud
  • 4
  • 3
  • 3
10 Comments
 
LVL 57
ID: 37750419
Well you've got a refresh in there, so the form will be repainted.  Try commenting that out.  If that does it, then I would just refresh MyTag and not the whole form.

Jim.
0
 

Author Comment

by:peispud
ID: 37750430
If I comment out the refresh, then the screen doesn't flicker.  But the data in the field / column [MyTag] does not represent the table data.

Can I just refresh that column?  If so how?
0
 

Author Comment

by:peispud
ID: 37750439
By the way,  .... This is a continuous form in Access 2010
0
 
LVL 57
ID: 37750453
Sorry, my brain is really not engaged today.

Do this at the end and see if this does it for you:

Application.Echo False
Me.Refresh
Application.Echo True
Me.Repaint

The continuous form may be a problem though... I remember something eith echo off doesn't quite work right with continuous forms.

Jim.
0
 
LVL 57
ID: 37750457
BTW, if that doesn't work, then there's another way we can go at it.

Jim.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:peispud
ID: 37750475
Nope,  That doesn't work..  Screen still flashing with this continuous form.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37750682
First, ...lets clean up the code...

a. I am not quite sure why the NZ() is in there... because the way you have it,...if it is null, you will insert a Zero "0"
b. I am not sure how SelText and SelLenght are working if you are not setting focus to the control first

Dim rs As DAO.Recordset
Set rs = GetRecordSet(Me.[Stock #])
If rs.RecordCount = 0 Then
    Exit Sub
End if

rs.Edit

If LCase(rs![MyTag]) = "y" Then
    rs![MyTag] = "":
Else
    rs![MyTag] = "Y"
End If

rs.Update
rs.Close
Set rs = Nothing
MyTag.SelLength = 0
MyTag.SelStart = 0
Refresh

OK, ...now that the code follows the normal structure, it is more readable and indented....

1. My first question would be if using Requery instead of Refresh fixes the flashing?

2. Why all the recordset stuff just to change one value in one record?
Consider using SQL and updating the table more directly.
In most cases, SQ is always much faster than using a recordset...
Something like this roughly:

CurrentDb.Execute "UPDATE YourTable SET YourTable.MyTag = IIF(LCase(YourTable.MyTag)='y','','Y')", dbFailOnError
'MyTag.SetFocus
MyTag.SelLength = 0
MyTag.SelStart = 0
Refresh


or
CurrentDb.Execute "UPDATE YourTable SET YourTable.MyTag = IIF(LCase(YourTable.MyTag)='y','','Y')", dbFailOnError
'MyTag.setFocus
MyTag.SelLength = 0
MyTag.SelStart = 0
Requery


3. Do you mean "Flashes", ...meaning it flashes *once*
...or do you mean it "Flickers" (flashes more than once)?

4. is the "Flashing" a dealbreaker?
(If someone hands you 400 oz Gold bar, do you care if their fingernails are dirty?)
;-)

JeffCoachman
0
 

Author Comment

by:peispud
ID: 37754688
Mr JeffCoachman

"Requery"  satisfies my requirements.   Now, only the affected row flashes (almost unnoticeable)   at times.   That's 99% of what I want.  

You made my day!!

You answered my question AND you went the extra mile.  I will definitely look into your other  advice.  I will try it this evening.

Thank you sir.

Ps.. Please do not feel embarrassed to hand over 400 oz of Gold bar just because your fingernails are dirty.  I can handle that. :)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754743
<Please do not feel embarrassed to hand over 400 oz of Gold bar just because your fingernails are dirty.  I can handle that. :)>
LOL!
Now you made my day...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754773
Oh and to be sure, you can see the help files on:
Requery
Refresh
Repaint

These commands are all commonly confused...
No one command is always "Better" under all circumstances...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now