Alternate Rows in Forms - The easy way (1 Viewer)

[muppetgeoff]

Hi,

After a *lot* of searching, and looking at various code samples, I finally figured out a *very* easy way to get alternate Row colours (colors) in continuous forms.

I can't post a sample just yet, but the instructions are:

1) Create the Public function:

'(Create the Variable first)

Public varRowState As Boolean   '(This may need to go at the top of the Code module)

'(Now the toggle function)
Public Function fcnRowState() As Boolean

    varRowState = varRowState + 1
    fcnRowState = varRowState

End Function

2) Create a hidden unbound text box in the form, name it "txtRowState" , and set its control source to "=fcnRowState()"

3) Set its Back color to the first of the 2 colors you want to alternate between, and make sure you set the Text color to the same (so the text doesn't show up)

4) Set it's 'Locked' property to 'Yes', and its 'Enabled' property to 'No'.

5) Set a Conditional format for it. Set the 'Condition1' to [Field Value Is - Equal To - "0"], again set the background and text colors to the same (so the text doesn't show up)

6) If required, Create a label with just a space, and expand it to cover the unbound text box. This will just sheild users from interacting with the conditional formatting

That's it, Job done!


This method relies on a quirk with the Boolean Data type, where adding 1 to a 0, results in -1 ! and of course adding 1 to -1 gives a 0

I spent a few hours tonight scouring the Net for solutions, and they all seemed clunky or limited.

This solution was inspired by CodeWidgets.com

Now you can sort your forms any way you like, and the row colors will always alternate

Kind regards,

Geoff

 

[WayneRyan]

Geoff,

I couldn't resist trying ...

Wayne

 

[muppetgeoff]

Sweet

The problems I found with my original implementation (hence the addition of the Label 'Sheild') were:

1) A user moving the mouse rapidly could interfere with the results of the GetNumber stage

2) After formatting (bearing in mind I was referring to alternate 'Row' colors not 'Field' colors) a user could click on the background color and it would blot out the whole Row.

With your example, the formatting is applied as the row is created?

Also, why haven't you posted this before?

Looking at the SQL, I pressume the '0' '-1' is calculated from the Row number of the returned data. Yeah, I can see how that's neat

Side note; it's handy to know I can do that with SQL Queries! All my SubForms are set (ReordSource) by VBA, so with that, I can inject stuff into them

Kind regards,

Geoff

[Note] I did say the *easy* way My method required no SQL knowledge. You could have the Form source as a Table and it would still work

 

[WayneRyan]

Geoff,

There is VBA involved! Look at the function GetNumber in Module1 (and the
global variable). Any form/report based on a query can include the:

NewField: GetNumber([SomeField])

and get alternating 1s and 0s.

I had to include a valid field parameter, because otherwise Access will only
evaluate the function once (Access is so smart).

Didn't post it before, because I just made it. Need a hobby while I'm home
sick.

Wayne

 

[muppetgeoff]

Hi Wayne,

I like your solution a lot Thank you!

I just made it. Need a hobby while I'm home

Nice! I spent hours on this problem! hehe

Still, this is my first Access DB and VBA project

I wanted to do some kind of Hover function, to change the color of the row the mouse is over. However, whilst a command button on the form will display the correct info for the row it's on, I can't figure out how to do the equivalent 'select row' with a hover. Any tips?

Kind regards,

Geoff

 

[benjames_au]

Alternate Rows - Help

Hi Geoff/Wayne/Anyone?,

I found this posting very useful for the db I'm working on. I started with Geoff's suggestion then moved over to waynes where the alternating 1 & 0 is handled at a query level.

In the form in question, I have an unbound control on the master form, with criteria that filter results in a linked subform. The fields in the subform are alternating as I would like theme to, even after another filter has been applied.

The problem is, that the first row in the subform is selected and blanked out(not visible). I find that selecting any other record, makes the top row visible.

I tried various configurations with the Visible/Enabled properties, none of which seemed to address my problem. I've included a screen cap demonstrating the problem.

The text (displayed field) is calculated, and the shaded control sits behind the text that is underneath. It seems that the shaded control is selected by default, thereby blocking the visibility of the text.

Any suggestions???

Much appreciated.

Cheers

 

[Kafrin]

varRowState = varRowState + 1

Or alternatively, varRowState = Not varRowState

 

[Catalina]

Wayne,

I download your sample DB and it works great but only with one Field.
I added another field to the table, query and form and set the Conditional Formatting on the form the same as the existing field.
However, now the rows in the colums are all the same color.

Am I overlooking something?
I would like to get it to work.

Thanks,
Catalina

 

[HAMMAMABUARQOUB]

wayne,

i download your sample db and it works great but only with one field.
I added another field to the table, query and form and set the conditional formatting on the form the same as the existing field.
However, now the rows in the colums are all the same color.

Am i overlooking something?
I would like to get it to work.

Thanks,
catalina

the same thing i did!
I suggest using conditional formating alon with a fild that each time take odd then even numbers and apply the condion on every field so all fields in the same record are colored with the same color, then the next record will change..
Thus..the mod thing will work better,,,but you need first to detrmine the colore,,
see this condion...
For color1

[id] mod 2 =0

for color 2

[id] mod 2> 0

to be put in the condion for each field,, and it worked,,,
hammam

 

[Catalina]

Thanks Hammam.

I will give it a try.

Catalina

 

[CEH]

I think the "Easy way" for alternate Row colors is Access 2007

 

[Catalina]

Hammam,

I tried but can't get it to work properly.
Likely I'm overlooking something.

Catalina