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 CompoundsObserv ations 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 CompoundsObserv ations 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 CompoundsObserv ations 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 CompoundsObserv ations 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 CompoundsObserv ations 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 CompoundsObserv ations 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_BeforeUpda te() 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. CompoundObserva tions 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(Ca ncel As Integer)
If DateDiff("s", DLookup("[LastMeeting]", "[LastMeeting]"), [DateOfInfoAdded]) > 0 Then
Me!DateOfInfoAd ded.BackColor = vbRed
Else
Me!DateOfInfoAd ded.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(Ca ncel As Integer)
If DateDiff("s", DLookup("[LastMeeting]", "[LastMeeting]"), [DateOfInfoAdded]) > 0 Then
Me!DateOfInfoAd ded.BackColor = vbRed
Else
Me!DateOfInfoAd ded.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 compoundObserva tions subform. Let's delete the previous code and place the following code in the current event of the subform CompoundObserva tions (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 Then[INDENT]Me!DateOfInfoAdded.BackColor = vbRed[/INDENT]
Else[INDENT]Me!DateOfInfoAdded.BackColor = vbWhite[/INDENT]
End 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 "conditiona l 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]","LastMeetingD ate"),[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?