Hello All

Can anybody help?

I have a database running on a SQL server with Access as the front end. The table is displayed and accessed as a continuous form. One of the fields in the table is a date. I need to check this date and if it is more than 30 days old change the colour of the record on the continuous form.

I have looked at Conditional formatting but cannot work out the options to put in to make this work.

My question is does anybody have any idea how I can do this either using Conditional formatting of using VBA code?

Many Thanks for your help in advance

You're on the right track with Conditional Formatting. You'll need to select "Expression is".

Then your expression will be something like:

iif(DateDiff("d",[Your_field],Now())>30,1,0)

I'm not sure about that DateDiff syntax. You might have to reverse the order of [Your_field] and Now().

The point is:

iif = immediate if
1 = means the condition is true so it will highlight
0 = means the condition is not true

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

It has been awhile since I have thought about this, but if I remember right, there is no way to do this.

Bound controls (for instance) can have a different value for each record—MS having gone to the trouble to make this so. But if at any time you set an [u]un</u>bound control’s value in one row, all rows’ textboxes will receive that value. If you set a certain unbound textbox to "10", you will have a column of textboxes all having "10" in them.

I believe the properties of the formatting of parts of the form are in the same general class of behavior.

You can do this sort of thing with a report, as each row can be individually formatted, but not with a form (if I remember correctly...).

Brian, I'm not sure why you answered this way.

For one thing, I think Michael indicates the form is bound.

The other is something I didn't learn about in my "early years". And I get the impression you're in your "early years".

Try this:
Pick a control on a form (in design mode). Select the menu option "Format" | "Conditional Formatting...". This formatting applies to only that field, not thw whole column... even if the field is displayed in a continuous form. So you can color a single cell in a continous form (or a whole row if you apply the conditional formatting to each field in the row). Which BTW, everyone should learn about "Format Painter". Format Painter will paint the format of the selected field to the next field selected, including conditional formatting!!!

At any rate, your reference to repeating 10 in an unbound field doesn't really apply here. But I think you're thinking about that every other row "colouring" thing you responded to where you suggested writing the function "if A then A = False else a=true" (clever idea, BTW). You could put that function in the query for the form thus making a field that can be bound. Then every other row would have the opposite value (as long as A is either Static or Global). The trouble is, I don't think the record set is updatable if that function is used in the query. But if you don't want to allow updates...

And BTW, this technique could be applied to "colouring" the report.

Me thinks me does think too much.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Oops. Just thought of one problem. If you sort your query, I'm not sure the "if A then A=False else A=True" thing will work. I'm pretty sure that will set the every other row as the data is read from the table, not after the data is sorted.

I wonder if that would work if the query used a sorted subquery?

Okay, stop thinking!!!

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Randall,

A form can be bound or not without changing the answer I made.

Binding the form establishes a recordsource, but it does not bind the client area of the form. The appearance of a row in a continuous form (as opposed to the controls in the row) is an unbindable domain.

You can change the color of individual controls, but I have the impression that Michael is asking about mimicking the appearance of printer paper that has alternating rows of color, but doing that coloring on the basis of the data.

I see that you indeed give a means of working on a control, as you start out your answer with “Pick a [u]control</u> on a form...” So i believe that you and I are sort of answering different issues.

I pointed out the behavior of putting “10” in an unbound control only to serve as an example of readily-duplicatable behavior, a concept which then applied adductively to the client area of a form.

I am not in my early years. I have been programming professionally (meaning actually getting paid to do it), full time, for 10 years, 7 of it as a contractor. (I must be doing something right if I have been employed on a contract basis without a break for 7 years...) The first 4 years of that was spent almost exclusively in Access (a little C, a little Excel & Word programming work). At one time I was one of the top 15 experts in the Access area of Experts Exchage.
I have been using VB and Oracle almost exclusively now for the last 5 years, and my sharpness on Access has atrophied some.

To maintain credibility, I am careful to imply uncertainty when I can't quite remember. That way, when I am decisive in my answer it can be trusted. (Well... that's my intention anyway; it is often lost on others, but what are ya gonna do? Right?)


Michael,

Something that emerges to me in this discussion I'm having with Randall:
You could add a control to the form that would be formattable according to the criterion you mentioned. If that control were prominent enough it would serve to draw your eye to the records that need to be noticed.
Perhaps you could even put a really large textbox behind all the other controls on the form—a textbox that could not have the focus set to it—so that when its color was changed it would give the appearance of coloring the whole row...

Brian

Brian. When I said "early years" I wasn't implying a lack of skill. Clearly you have skill. It's just that there are some coding techniques that apply particularly to Access that I wasn't challenged with in my "early years" or perhaps it was more that I was challenged but couldn't figure out the solution. This was one of them. I'm sorry if you felt slighted by my comment.

As to coloring a "record"... what's the difference between coloring the record versus coloring all of the controls that are used to display the record? If you put the "Conditional Formatting" with the expression that I suggested on all of the controls, the row will be highlighted... especially if the form is displayed in datasheet view. (BTW, this is why I mentioned the "Format Painter" tool.)

We may be talking about different things. But the technique you suggested about the text box is still going to require using Conditional Formatting. If you try to change color in the OnCurrent event by setting the control's .BackgroundColor property, it won't work. And it won't work for the same reason 10 repeats in an unbound control. (Which is why I thought you were talking about unbound controls.) Setting the control's property applies to every row, even if you set that property in the OnCurrent event. Things are only different row by row when Access can make the adjustment according to the data in the current row (record). It really made me mad when I discovered this years and years ago. And it surpised me when 5 years later I learned there was a way to do what I wanted. And it was called "Conditional Formatting".

Besides, putting a text box behind the other controls is going to leave those controls uncolored. Which may be okay if they are transparent.

As to my expertise. I have none. I just do the best I can to share techniques I've learned. OTOH, I've been writing code and building data processing systems since 1978. Oh, and I managed to write three chapters and one appendix for "Access 2003 VBA Programmer's Reference" for Wrox. Which is the reason I'm on this forum.

When I see what some people do with Access, I still feel like I'm in my "early years". Which is why when I see someone else's suggestion, I only dispute it when I know it won't work or when I believe there is an alternative that is actually better.

This technique absolutely will work. And if Michael has to have the background colored too, he can either go with datasheet view or do the text box as you suggested.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Actually, I wasn't slighted, but rather wanted you to not have a misapprehension. That’s all.

Coloring the record (as I would use the phrase) would color the client area of that record’s part of the form, as if the form's background color had been changed for that row.

That conditional formating capability is indeed a new one on me. I played around with it a little; it is interesting and has a lot of potential.
You are right that conditionally formatting all of the controls would effectively color the row in datasheet view. (of course, the concept of a ‘continuous form’ is mutually exclusive to datasheet view, but maybe switching to datasheet view with conditional formatting would be a better approach for Michael.

It is irritating that unbound controls, et al., change in every row if they are changed in any row...

Welcome to P2P! If you read this thread, you may find the answer you need if I understand you correctly.

http://p2p.wrox.com/topic.asp?TOPIC_ID=210



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division