Have a date on a subform change colour depending on another form's date

[Ericks]

I want to highlight new data that has been entered in my database since a last meeting so it is easy to see in a subform’s table what info is new.

In my database I have a table called CompoundsObservations and that contains a date field called DateOfInfoAdded.
There is another table called LastMeetingDate with only one field, a date field called LastMeeting. This table only serves the purpose to register the last meeting's date and should be the basis to highlight new information that was enetered as from.

How can I make the DateOfInfoAdded date of the CompoundsObservations subform (placed on the main form called Compounds) turn red when this date is later in time than the LastMeeting date, the latter entered on a separate Form called Lastmeeting?

I tried doing this through a Query with an Expression between the 2 tables and that actually worked but strange enough, the “Group by” option makes the memo fields of the CompoundsObservations query become only 255 characters long (text size). So actually not all text is visible. When I turn the Group by option off (so the expression is not valid anymiore) all the text is there again.

I'm very new to Access so still quite intimidated.

[puppydogbuddy]

I want to highlight new data that has been entered in my database since a last meeting so it is easy to see in a subform’s table what info is new.

In my database I have a table called CompoundsObservations and that contains a date field called DateOfInfoAdded.
There is another table called LastMeetingDate with only one field, a date field called LastMeeting. This table only serves the purpose to register the last meeting's date and should be the basis to highlight new information that was enetered as from.

How can I make the DateOfInfoAdded date of the CompoundsObservations subform (placed on the main form called Compounds) turn red when this date is later in time than the LastMeeting date, the latter entered on a separate Form called Lastmeeting?

I tried doing this through a Query with an Expression between the 2 tables and that actually worked but strange enough, the “Group by” option makes the memo fields of the CompoundsObservations query become only 255 characters long (text size). So actually not all text is visible. When I turn the Group by option off (so the expression is not valid anymiore) all the text is there again.

I'm very new to Access so still quite intimidated.

when the DateOfInfoAdded has been updated, Do a DLookup to the table to obtain last meeting date and use DateDiff function to determine if DateOfInfoAdded later than LastMeeting (in seconds).

Try this (not tested):

Private Sub DateOfInfoAdded_AfterUpdate
If DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOFInfoAdded]) > 0 Then
 Me!CompundObservations.Form!DateOfInfoAdded.BackColor = vbRed
End If
End Sub

[Ericks]

I tried it but it doesn't work. I will üplay around with it a bit more to see if I am somehow doing something wrong.

[puppydogbuddy]

I tried it but it doesn't work. I will üplay around with it a bit more to see if I am somehow doing something wrong.

Do you know how to use the debug window? if so, check what values are showing for the computations, and let me know.

If you don't know how to debug in Access, let me know and I will tell you what to do.

[puppydogbuddy]

Just a couple of thoughts:
1. I understood from your write-up that DateOfInfoAdded is on the main form. Is that correct?
2. How is DateOfInfoAdded actually added to the main form? Is it entered or is it done automatically by using a date function to calc on the system date? If it is entered, then the DateOfInfoAdded_AfterUpdate() event should fire. If it is calculated, the DateOfInfoAdded_AfterUpdate event will not fire......you would probably need to put the code I gave you in the Form_BeforeUpdate() event of the main form

[Ericks]

The main form is called COMPOUNDS. On this form are the two subforms with the dates:
1. LastMeetingDate containing the LastMeeting date field. I alter this date about 2-3 times a year, after a meeting through the popup Calendar.

2. CompoundObservations containing the DateOfInfoAdded field. It enters the system's date by default but I can alter it myself.

So when changing either of the 2 dates the DateOfInfoAdded text should turn red if more recent than the LastMeeting date.

What for me in all this is the most confusing is the name convention to be used in code for controls on forms and sub forms.

[Ericks]

I entered this code:

Private Sub DateOfInfoAdded_AfterUpdate(Cancel As Integer)
If DateDiff("s", DLookup("[LastMeeting]", "[LastMeeting]"), [DateOfInfoAdded]) > 0 Then
Me!DateOfInfoAdded.BackColor = vbRed
Else
Me!DateOfInfoAdded.BackColor = vbWhite
End If
End Sub

Nothing happens when the subform is a datasheet. But in Continuous Forms mode the field gets red, that is, all the date fields, even the once not the most recent. And when changing the LastMeeting date to a later date the DateOfInfoAdded dates just remain red.

[puppydogbuddy]

I entered this code:

Private Sub DateOfInfoAdded_AfterUpdate(Cancel As Integer)
If DateDiff("s", DLookup("[LastMeeting]", "[LastMeeting]"), [DateOfInfoAdded]) > 0 Then
Me!DateOfInfoAdded.BackColor = vbRed
Else
Me!DateOfInfoAdded.BackColor = vbWhite
End If
End Sub

Nothing happens when the subform is a datasheet. But in Continuous Forms mode the field gets red, that is, all the date fields, even the once not the most recent. And when changing the LastMeeting date to a later date the DateOfInfoAdded dates just remain red.

Ok, so the date info is on the subforms, and the DateOfInfoAdded is added by default to the system date in the compoundObservations subform. Let's delete the previous code and place the following code in the current event of the subform CompoundObservations (the current event is used because the AfterUpdate won't fire if no data entry is involved):

Private Sub Form_Current()
If DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOFInfoAdded]) > 0 ThenMe!DateOfInfoAdded.BackColor = vbRedElseMe!DateOfInfoAdded.BackColor = vbWhiteEnd If
End Sub

If you prefer the datasheet instead of the continuous form, you can try the conditional formatting that is available via the format command on the command menu. Let me know.

Referencing between forms and subforms depends on where you are (form or subform) when you issue the reference. Fortunately, there is a handy reference guide to follow:

http://www.mvps.org/access/forms/frm0031.htm

[Monroeski]

When I had to change the color of a field in individual lines in a continuous form, I just right-clicked on the text box, selected "conditional formatting," and worked out an expression that way.

I generally like to put as much stuff in the code as possible, and stay away from letting Access do stuff like that for me because inevitably it also automatically "helps" me do something I didn't want to do, but I kept having the same problem as you (the formatting affected all rows, not each one individually), so I figured that was the best way to get it done quick.

[Ericks]

Oh yes, now doubt I prefer the Conditional formatting method but I can only make it work for controls on the same Form. In this case it's controls on different forms so I don't know what the code line is that should go in "Expression is". I didn't even know one could refer here to controls on different forms. So if you have code suggestions please let me know.

[puppydogbuddy]

Oh yes, now doubt I prefer the Conditional formatting method but I can only make it work for controls on the same Form. In this case it's controls on different forms so I don't know what the code line is that should go in "Expression is". I didn't even know one could refer here to controls on different forms. So if you have code suggestions please let me know.

Did you try the new code I gave you? What happened?

[Ericks]

The code you just sent works. And it works both ways, that is, when changing either of the 2 codes. Quite cool, this is already something I have not been able to do. BUT, it only works in (continuous) forms and it changes the colour of all the date fields, even the ones with an earlier date (those should remain white). I can live with the continues forms. In principle it doesn't differ from the datasheet for the user. But it should only change the relevant background colours.

[Ericks]

I noticed something interesting. The date colours all change to red or white when I place the cursor on an earlier or later than Lastmeeting date. That's bizare....I wonder why they don't keep their color.

[puppydogbuddy]

I noticed something interesting. The date colours all change to red or white when I place the cursor on an earlier or later than Lastmeeting date. That's bizare....I wonder why they don't keep their color.

Depends on the firing of the current event ..it probably needs additional code in another event. I can look into this, but maybe you should try conditional formatting first.

For conditional formatting, highlight tthe control that you want to conditionally change color, then select Expression is (see below) and use the DateDiff expression Igave you above. You don't have to worry about form/subform because DLookup component references the table, not the form.

DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOFInfoAdded]) > 0

[Ericks]

When I paste it into Conditional formatting, Expression is I get and Invalid Syntax message.Maybe a coma, quatation mark or something like that is missing? Or maybe there is an invalid character?

[puppydogbuddy]

When I paste it into Conditional formatting, Expression is I get and Invalid Syntax message.Maybe a coma, quatation mark or something like that is missing? Or maybe there is an invalid character?

Hmmm, maybe it doesn't like the double quote "s"; try it this way:

DateDiff('s', DLookup("[LastMeeting]","LastMeetingDate"),[DateOFInfoAdded]) > 0

[Ericks]

The same error message. I played around with some comas, etc. but keep getting that message.

[puppydogbuddy]

The same error message. I played around with some comas, etc. but keep getting that message.

DateOfInfoAdded is the control you are conditionally formatting, try this:

DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),Me![DateOFInfoAdded]) > 0

[puppydogbuddy]

Guess what...I think I found the problem....the capital F in the OF s/b f as in Of :

change this:
DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOFInfoAdded]) > 0

to:
DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded]) > 0

Also: you need to add a second condition for vbWhite:

DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded]) <= 0

Hope that's got it.

[Ericks]

This is getting spooky. It still complaints about wrong syntax....

[puppydogbuddy]

This is getting spooky. It still complaints about wrong syntax....

ok, maybe the DLookup is looking for the optional where clause to pin down which LastMeeting date we are using for comparision. That may be also why you experienced the color disappearing in the code version. Just for grins, substitute the following as part of the If statement in the coded version and see if that was the problem with the code... and of course, put the following in the conditional formatting and test that one, too. Thanks.

DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate", "[LastMeeting] = " & Me![LastMeeting]),[DateOfInfoAdded]) <= 0

[Ericks]

Nope, the same message. I'm sure I'm doing something wrong here. And I got to say it, I find VB d... difficult.

[puppydogbuddy]

Nope, the same message. I'm sure I'm doing something wrong here. And I got to say it, I find VB d... difficult.

Eric,

VB does have a long learning curve, but once you catch on...its power and flexibility are awesome!

Back to you...I tested the syntax of the DateDiff expression in conditional formatting and do not get any syntax errors....so you must be doing something wrong.

Which control did you highlight for conditional formatting? Tell me what steps you followed for conditional formatting and maybe I can spot what you are doing wrong. Thanks.

[Ericks]

The control I use it on is DateOfInfoAdded. This is the control of the sub folder [Compound observations]. I copy pasted first the line
DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded]) <= 0

and when that gave the syntax error message I copy pasted your other option:
DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate", "[LastMeeting] = " & Me![LastMeeting]),[DateOfInfoAdded]) <= 0

That is, I highlighted the control in Design view, went to Format, Conditional Formatting, choose "Expression is" and copy pasted it. I got MS Access 2000. I guess that the syntax you use is valid for this version also. Can you send me the expression you tested and that didn't return a syntax error message?

[puppydogbuddy]

The control I use it on is DateOfInfoAdded. This is the control of the sub folder [Compound observations]. I copy pasted first the line
DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded]) <= 0

and when that gave the syntax error message I copy pasted your other option:
DateDiff("s", DLookup("[LastMeeting]","LastMeetingDate", "[LastMeeting] = " & Me![LastMeeting]),[DateOfInfoAdded]) <= 0

That is, I highlighted the control in Design view, went to Format, Conditional Formatting, choose "Expression is" and copy pasted it. I got MS Access 2000. I guess that the syntax you use is valid for this version also. Can you send me the expression you tested and that didn't return a syntax error message?

Sure:
DateDiff("s",DLookUp("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded])>0

I have Access 2000 also. I highlighed the control on the subform, selected conditional formatting; selected expression is; pasted the expression; clicked backcolor = red for formatting when expression is true.

[Ericks]

Sure:
DateDiff("s",DLookUp("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded])>0

I have Access 2000 also. I highlighed the control on the subform, selected conditional formatting; selected expression is; pasted the expression; clicked backcolor = red for formatting when expression is true.

Very strange. Because that same phrase pasted into the On Current event triggers the colour changes of the DateOfInfoAdded text. Still in Form mode and changing all dates.

I have come up with a solution that works. I created a query of both Compounds Observations and Lastmeeting date together and put in the expression Avg([Last meeting]-[date of info added]). Of this Query I created another query with the expression Min Of Expr1: Expr1. I then created a subform Compound Observations from that query. So whenever Expr1 is positive it triggers the Conditional Formatting event making the date red. The other dates in the datasheet remain black. The only negative thing about this is that I can't enter data directly into the query datasheet but I built in a popup with a subform that allows for data input. It's like flying from New York to Florida via Frankfurt but I get there in the end.

[Ericks]

This is in an earlier version still with slightly different names for fields and controls.

[puppydogbuddy]

Very strange. Because that same phrase pasted into the On Current event triggers the colour changes of the DateOfInfoAdded text. Still in Form mode and changing all dates.

I have come up with a solution that works. I created a query of both Compounds Observations and Lastmeeting date together and put in the expression Avg([Last meeting]-[date of info added]). Of this Query I created another query with the expression Min Of Expr1: Expr1. I then created a subform Compound Observations from that query. So whenever Expr1 is positive it triggers the Conditional Formatting event making the date red. The other dates in the datasheet remain black. The only negative thing about this is that I can't enter data directly into the query datasheet but I built in a popup with a subform that allows for data input. It's like flying from New York to Florida via Frankfurt but I get there in the end.

It sure is ugly, but if you are satisfied it works.then that is up to you.
Try this instead of DateDiff or your query expr in conditional formatting. I think that it should work:

([Last meeting]-[date of info added]) < 0 >>>>>red

([Last meeting]-[date of info added]) >= 0 >>>>>white

[Ericks]

It sure is ugly, but if you are satisfied it works.then that is up to you.
Try this instead of DateDiff or your query expr in conditional formatting. I think that it should work:

([Last meeting]-[date of info added]) < 0 >>>>>red

([Last meeting]-[date of info added]) >= 0 >>>>>white

Well, I think I'm kind of leaving it as is. In the mean time I will tinker over it. I hate the fact that I can't use the table as a subform but have to go through an uneditable query instead. But for the time being the popup helps.
I'm intrigued by this syntax error message I get.
Anyways, thanks for making time available. Highly appreciated.
Take care,
Eric