Microsoft Access screen flashing on update

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
peispudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
peispudAuthor Commented:
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
peispudAuthor Commented:
By the way,  .... This is a continuous form in Access 2010
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, if that doesn't work, then there's another way we can go at it.

Jim.
0
peispudAuthor Commented:
Nope,  That doesn't work..  Screen still flashing with this continuous form.
0
Jeffrey CoachmanMIS LiasonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peispudAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
<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
Jeffrey CoachmanMIS LiasonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.