Solved

Microsoft Access screen flashing on update

Posted on 2012-03-21
10
524 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 58
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 58
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 58
ID: 37750457
BTW, if that doesn't work, then there's another way we can go at it.

Jim.
0
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 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