Datasheet: Different back colors for columns and alternate row colors (1 Viewer)

[sonic8]

Is it possible to change the back color for some columns in a datasheet form but still keep alternate row colors?

I’ve got a datasheet (sub) form. The first (leftmost) columns are read-only (Enabled=False,Locked=True) and the user is not supposed to edit data there. The last (rightmost) columns are open for editing and the user is supposed to enter data there. I want to visually indicate which columns can/should be edited and which are read-only by using different back colors for each set of columns.

The only option I’m aware of for assigning the back color in a datasheet on column level is by using a format condition. This basically works, but I lose the alternate row color which is helpful for the user to visually identify the individual records.

Please note: Datasheet look&feel is an explicit requirement. The user is allowed to filter and sort the datasheet as he sees fit. Building a continuous form mimicking datasheet behavior is too much effort for the back color feature.

 

[arnelgp]

you can try to create 2 conditional format.
the first is "Has Focus" and the second is expression to color the column (Expression: 1=1).

the "Has focus" must be the first condition for this to work.

 

[sonic8]

you can try to create 2 conditional format.
the first is "Has Focus" and the second is expression to color the column (Expression: 1=1).

I don't understand.
HasFocus only applies to a single control in the datasheet grid.
I ask about alternate row colors for all rows in the grid.

 

[arnelgp]

see Table1 form in design view.
there are CF on alll textbox and again 1 CF for ID only.

 

[sonic8]

see Table1 form in design view.

Thank you for the example.
I do understand how to implement your suggestion but I do not understand how it can help to solve my problem.
I do not ask about highlighting the current record but about how to apply alternating row colors.

Your example produces this result:

I want to achieve something like this:

For this screenshot, I achieved the result by adding two different format conditions with different colors:

• 1=1 And [Id] Mod 2=0
• 1=1 And [Id] Mod 2=1

But this does only work as long as there are no gaps in the ID value and as long the user does not sort/filter the form in a way that changes the sequence of ID values. - The latter must be explicitly possible for the user to do.

 

[CJ_London]

as long as there are no gaps in the ID value and as long the user does not sort/filter the form in a way that changes the sequence of ID values.

you would need to include a rownumber column in your data and reference that rather than the ID. This requires a rownumber function.

See this link - does a lot more than you want but has a rownumber function

Making a form look like a report

As a result of this post https://www.access-programmers.co.uk/forums/threads/emulate-reports-grouping-levels-with-forms.326697/ I thought I would diddle around and come up with some examples. This is a simple format - only shows the customer once and uses conditional formatting to change the...

www.access-programmers.co.uk

review the queries to see how it is called. It may be you will need to do some stabilising as user sorts and filters

 

[arnelgp]

did you open Table1 form?
this is what it like

 

[sonic8]

did you open Table1 form?

I posted a screenshot of it. So, yes, I opened it.

Highlighting the current row is nice and it would be stopgap solution if everything else fails.
However, as stated before, my question is about alternate row colors for all rows.

 

[arnelgp]

alternate row colors for all rows.

i don't have any solution to that, except to remove the Alternate color completely.

 

[sonic8]

you would need to include a rownumber column in your data and reference that rather than the ID. This requires a rownumber function.

I had the idea of using an artificial row number to detect the alternating rows but discarded that idea because correctly computing row numbers for arbitrary sort orders is somewhat complex.

However, your simple RowNumber function from the linked download does not really produce a reliable row number but rather a counter of the times a record was internally accessed. - Luckily, this flaw is irrelevant in the context of my specific problem in this thread.
It works to use a "RowNumber" generated by this function to determine the alternating state of the rows!

The only drawback is that it takes almost 2 seconds between applying a new sort order until the correct conditional formatting is visually applied. - This is not ideal, but I hope the users can live with that.

 

[CJ_London]

I'm tied up with other stuff right now but think I have an alternative which is more 'stable', I'll see if I can find it later (be a couple of days). But probably can't do much about the time delay as that is a factor of conditional formatting.

 

[isladogs]

Are either of these ideas any use to you?
a) Highlight a range on a datasheet

b) Freeze & lock columns, sort & filter on a continuous form
I decided not to have alternating colors on the locked columns as I thought it would look too 'busy'