Solved Datasheet: Different back colors for columns and alternate row colors

[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'

 

[Pat Hartman]

@isladogs looks like the first one leaves the alternate row colors

@sonic8 I understand wanting to retain the alternate row coloration for updateable columns but what does that matter for columns that are not updateable?

 

[isladogs]

Yes. The first example has alternate row colours on the highlighted range

 

[sonic8]

@sonic8 I understand wanting to retain the alternate row coloration for updateable columns but what does that matter for columns that are not updateable?

Alternate row colors are visual guardrails to help the human eye to visually follow one row in a list of rows. Whether this is updatable data or read-only and whether it is on screen or on paper is irrelevant.

In my case, the leftmost 5 read-only columns allow the user to identify a record and provide some info on its current state. The 4 rightmost columns are for updating the most recent status, which is in most cases just entering a date and an amount. - It is crucial for the user to easily identify a distinct whole row in that list.

 

[sonic8]

Are either of these ideas any use to you?

Thank you Colin for the examples. I actually looked on your website first before posting here.

The first one is just a temporary selection(?) and thus unsuitable for my requirements.

The second one is visually close to what I want to achieve. But for now, I design just a first prototype and do not want to make it overly complex. Thus I prefer to go with a real datasheet that has most of what I need right out of the box, instead of enhancing a continuous form to suit the requirements.

 

[isladogs]

Hi Philipp
The first example is designed to be temporary but you could easily change it to set the values of the 4 variables in the Form Load event and remove the combos

If you want to investigate this example, the web page is:

Datasheet Row / Column Select & Spell Check

Datasheets allow you to select columns from the column header and rows using the record selector. Surprisingly there appears to be no simple direct command available to do this programmatically. A no-code approach is to add the Spelling command to the right click context menu for datasheet...

isladogs.co.uk

 

[AHeyne]

Hi Philipp,

unfortunately there seems to be no built-in indicator/constant in conditional formatting syntax representing the row to be formatted.

But if you could provide a column with an ascending number somehow in your forms recordsource, then you could use this approach.
Given a column "MyAscendingNumberControl" you could apply these two conditional formating rules of type "Expression is" to all locked controls

[MyAscendingNumberControl].[Value] Mod 2=0

[MyAscendingNumberControl].[Value] Mod 2<>0

and set the proper background color for both rules.

For example in Colins form "fsubProducts" you could do so for the control "ProductID".

But this "MyAscendingNumberControl" column should be somehow artificial to ensure ascending numbers without gaps.