Solved

Microsoft Access screen flashing on update

Posted on 2012-03-21
10
470 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

13 Experts available now in Live!

Get 1:1 Help Now