Not important but not pretty (1 Viewer)

[ellenr]

I have a form set to continuous forms using alternating background colors. A click on a member name saves the memberid then opens the detail form for that member. When the detail closes, the OnActivate event (in the continuous forms) triggers a requery followed by a find record to return to the proper place in the list. The alternating colors of the records always assign white to odd numbered records and the secondary color to even numbered records. This is all lovely until the find record has to locate an even numbered record for a member not on the first page of the list. In this case, it puts the found record as the top of the list and insists on painting the background white. It colors the background of all fields on that line with the appropriate alternate color, resulting in a confusing checkerboard effect, even though I have each field set to Back Style transparent. All lines on the screen continue in this confused action; once I begin to scroll up or down, it begins to right itself.

 

[GinaWhipp]

Have you tried adding a...

Me.Repaint

Might be the screen just needs to *refresh* itself.

 

[vbaInet]

Conditional formatting works on controls and not on the Detail section. What you've shown us uses the Alternate Background property of the detail section and Conditional Formatting on the controls so of course it will get mixed up.

If you want a consistent alternate back colour you need to remove the Conditional Formatting completely from each and every control and let the Alternate Background property do its work. Conditional Formatting will attempt to overwrite the Transparent Back Style property.

 

[ellenr]

I decided to try Lebans code to save the record # of the top record, then after the requery he goes back to that same "seltop" on the screen. If only I could make that work! The problem seems to be it won't let me set a user-defined class variable: Private SR As clsSetRow

Private Sub Form_Load()
Set SR = New clsSetRow
End Sub

"User-defined type not defined" is the error msg.

I am using Access 2010, 32 bit. References include:
Visual Basic Applications
MS Access 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Access database engine Object Library

 

[vbaInet]

I suppose what I mentioned about using the Alternate Background property of the form wasn't helpful?

 

[jdraw]

It sounds like you do not have a copy/ or a reference to clsSetRow which seems to be a ClassModule from Lebans.

see this link for source (caution he recognizes a missing piece in post 6 (I think))

I think Gina and vbaInet were posting simpler solutions that may solve your issue.

 

[ellenr]

Gina's me.repaint didn't help.
Lebans solution would be more elegant, as it both solves the alternate row color problem and leaves the user at the same place on the screen. I have his entire code from his .zip file. The moment it hits the On_Load for the form, I get the error message. It isn't allowing the user-defined type.

 

[ellenr]

Problem solved! I feel rather dumb: all I had to do was change requery to refresh. Thank you to all of you who joined the discussion. This wouldn't be the way to go if the underlying query needed to be reordered, but in this case all I wanted was for the actual record in question to be updated. I would still love to know why it wouldn't let me do a user-defined type.

 

[vbaInet]

Requery and Refresh don't do the same thing and in most cases you need Requery. So no you've not solved the problem.

You still haven't acknowledged any of my responses.

 

[GinaWhipp]

Re the User-Defined Function, did you remember to Declare it at the beginning of the Module?

 

[ellenr]

Gina, after Option Explicit, I entered the line: Public SR As clsSetRow. When that didn't work I changed Public to Private. Neither worked.

VBAInet, I have a couple of instances of conditional formatting in the form: I have a text box behind and slightly larger than a check box in two places. If the check box has been checked, the little text box conditionally changes colors.

Edit: hmmm...I had set all fields to transparent, assuming that if the background color changed, you needed to be able to see it. Now, thanks to you, I have tried setting fields' background to normal and Back Color to Background 1. Works like a dream. Should have listened to you earlier! Thank you.

 

[vbaInet]

Stephen Lebhan's solution was developed before the introduction of Alternating Background in 2007 so his code is for pre 2007. The error you got is most likely because you didn't save clsSetRow as a Class Module. You probably saved is as a Module. Only classes can be instantiated using the New keyword.

Good to hear you got it working.

 

[Tom Robinson]

ellenr I just had the same problem with using "Alternate Back Color" on the Detail.

Repaint and Refresh didn't fix it.

The problem occurs sporadically when you do a DoCmd.FindRecord in code, or use the Find feature on the ribbon. The solution is to replace FindRecord with:

Echo False
DoCmd.FindRecord x
Echo True

Here is a sample of the glitch in Access 2013, after scrolling down a few rows. As you discovered, scrolling fixes the problem for the scrolled-in rows.

 

[EngOx]

Hi - I know this thread is a bit old but it took me quite a bit of time trying different thing to answer the original question. I have a form set to continuous forms using alternating background colors... "an event results in" ... a confusing checkerboard effect, even though I have each field set to Back Style transparent.

Repaint, Refresh and Requery did nothing to resolve the issue and enclosing the event inside echo on - echo off had no effect.
I found that simply changing the form detail's alternate backcolor to a different color and then back to the original color solved the problem.

Dim longBackColor As LongPtr
longBackColor = Me.Detail.AlternateBackColor
Me.Detail.AlternateBackColor = RGB(255, 255, 255)
Me.Detail.AlternateBackColor = longBackColor
Me.Repaint

I tested it on a form with over 400 detail lines and there was no flickering.

 

[NauticalGent]

Those pesky Access Gnomes and the mischief they get into...