Synthax error in Conditional formatting

[Ericks]

I want to apply Conditional Fomatting to a sub form's date control when it's of earlier date than a date in another table (form). I use following code in Expression is: DateDiff("s",DLookUp("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded])>0
but keep getting a syntax error message. Any idea what I'm doing wrong?

[ADezii]

I want to apply Conditional Fomatting to a sub form's date control when it's of earlier date than a date in another table (form). I use following code in Expression is: DateDiff("s",DLookUp("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded])>0
but keep getting a syntax error message. Any idea what I'm doing wrong?

Your syntax, is in fact, incorrect. Try:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!frmFormName![Date Field] & "#") > 0
 

[Ericks]

Your syntax, is in fact, incorrect. Try:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!frmFormName![Date Field] & "#") > 0
 

I typed in

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!CompoundObservations![DateOfInfoAdded] & "#") > 0

but still keep getting the syntax error message. Some more info:

The main form is called Compounds and contains 2 subforms:
LastMeeting (Form view) with a date control called LastMeetingDate and
CompoundObservations (Datasheet view) with a date control called DateOfInfoAdded.

I want the text of the DateofInfoAdded control become red when it's later than LastMeetingDate. For this I want to use the Conditional Formatting tool on the DateOfInfoAdded control. And it should work immediately after update of either dates.

[nico5038]

The " on the second line needs to be removed.:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & Forms!CompoundObservations![DateOfInfoAdded] & "#") > 0

Also be sure that your application works on a computer with the US regional settings. Having a default computer date format of dd-mm-yyyy will cause trouble...

Nic;o)

[Ericks]

The " on the second line needs to be removed.:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & Forms!CompoundObservations![DateOfInfoAdded] & "#") > 0

Also be sure that your application works on a computer with the US regional settings. Having a default computer date format of dd-mm-yyyy will cause trouble...

Nic;o)

Ok, getting there. I changed the setting to US and took out that ". Now I don't get the syntax error. But it doesn't change the color either.

[nico5038]

Hmm, could be because of a "not found" that will return Null as value for the DCOUNT(), try adding the NZ() function like:

NZ(DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & Forms!CompoundObservations![DateOfInfoAdded] & "#")) > 0

Nic;o)

[ADezii]

I typed in

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!CompoundObservations![DateOfInfoAdded] & "#") > 0

but still keep getting the syntax error message. Some more info:

The main form is called Compounds and contains 2 subforms:
LastMeeting (Form view) with a date control called LastMeetingDate and
CompoundObservations (Datasheet view) with a date control called DateOfInfoAdded.

I want the text of the DateofInfoAdded control become red when it's later than LastMeetingDate. For this I want to use the Conditional Formatting tool on the DateOfInfoAdded control. And it should work immediately after update of either dates.

In the AfterUpdate() Event of the [DateOfInfoAdded] Field on the CompoundObservations SubForm, place the following code. Similar, but different, code would also be placed in the [LastMeetingDate] Field on the LastMeeting Sub-Form:

Private Sub DateOfInfoAdded_AfterUpdate()
If Not IsNull(Me![DateOfInfoAdded]) And Not IsNull(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  If CDate(Me![DateOfInfoAdded]) > CDate(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
    Me![DateOfInfoAdded].ForeColor = vbRed
  Else
    Me![DateOfInfoAdded].ForeColor = vbBlack
  End If
End If
End Sub

[Ericks]

In the AfterUpdate() Event of the [DateOfInfoAdded] Field on the CompoundObservations SubForm, place the following code. Similar, but different, code would also be placed in the [LastMeetingDate] Field on the LastMeeting Sub-Form:

Private Sub DateOfInfoAdded_AfterUpdate()
If Not IsNull(Me![DateOfInfoAdded]) And Not IsNull(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  If CDate(Me![DateOfInfoAdded]) > CDate(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
    Me![DateOfInfoAdded].ForeColor = vbRed
  Else
    Me![DateOfInfoAdded].ForeColor = vbBlack
  End If
End If
End Sub

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

[Ericks]

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

It's amazing what a challenge this is. In another discussion posted earlier no solution was found. It seems to easy actually. Have the date control of one subform change colour depending on a date control on another subform.

[ADezii]

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

Glad you were successful, but it should work equally well on a Single as opposed to Continuous Form View.

[ADezii]

It's amazing what a challenge this is. In another discussion posted earlier no solution was found. It seems to easy actually. Have the date control of one subform change colour depending on a date control on another subform.

It's all a matter of code context (where the code is executing from). The reference to the Field on the 2nd Sub-Form would require a fully qualified Reference to the Control as I displayed, or use of the Parent Property, which I explicitly chose not to use. In any event, Good luck!

[Ericks]

It's all a matter of code context (where the code is executing from). The reference to the Field on the 2nd Sub-Form would require a fully qualified Reference to the Control as I displayed, or use of the Parent Property, which I explicitly chose not to use. In any event, Good luck!

Thanks for helping out. As said, it still doesn't work but I will keep working on it till it does.

[ADezii]

Thanks for helping out. As said, it still doesn't work but I will keep working on it till it does.

Ericks, are you sure you are referencing the name of the Sub-Form Control and not the Sub-Form itself? This is a common mistake.

[puppydogbuddy]

Eric,
I see you are still struggling with this. Let me ask a couple of questions that may help clear things up.
1. You have a form and two subforms. Is each of the subforms set up as a child of the main form, or is subform1 set up as a child of the main form and subform2 set up as the child of subform one?
2. List the fields you have as the master and child links for each of the subforms.

[Ericks]

Eric,
I see you are still struggling with this. Let me ask a couple of questions that may help clear things up.
1. You have a form and two subforms. Is each of the subforms set up as a child of the main form, or is subform1 set up as a child of the main form and subform2 set up as the child of subform one?
2. List the fields you have as the master and child links for each of the subforms.

Yep, still struggling with something that I'm sure is easy but that Im apparently doing wrong.

The form Compounds has 2 subforms:
1. LastMeeting (in Form view), which is not a child of anything ( I think this is called an unbound form) and which has a date control called LastMeetingDate.
2. CompoundObservations (in Datasheet view), which is a child of the Form Compounds and which has a date control called DateOfInfoAdded.

The Compounds form and CompoundObservations subform are linked through a linked field called [Compound id] . The Lastmeeting form is not linked to anything. It only serves the purpose to have data entered after this date being highlighted with red letters.

After changing the regional settings to US (my settings are European since I live in Switzerland) the later dates changed colour, but again, only in form mode (no problem, I dont insist on the subform being in datasheet view). And it changes all the dates, even the onces that are to remain of black text because they are of earlier date than the last meeting.

[puppydogbuddy]

Yep, still struggling with something that I'm sure is easy but that Im apparently doing wrong.

The form Compounds has 2 subforms:
1. LastMeeting (in Form view), which is not a child of anything ( I think this is called an unbound form) and which has a date control called LastMeetingDate.
2. CompoundObservations (in Datasheet view), which is a child of the Form Compounds and which has a date control called DateOfInfoAdded.

The Compounds form and CompoundObservations subform are linked through a linked field called [Compound id] . The Lastmeeting form is not linked to anything. It only serves the purpose to have data entered after this date being highlighted with red letters.

After changing the regional settings to US (my settings are European since I live in Switzerland) the later dates changed colour, but again, only in form mode (no problem, I dont insist on the subform being in datasheet view). And it changes all the dates, even the onces that are to remain of black text because they are of earlier date than the last meeting.

So, is LastMeeting really a subform....or is it just a popup form that you are using to enter the last meeting date?

[Ericks]

So, is LastMeeting really a subform....or is it just a popup form that you are using to enter the last meeting date?

LastMeeting is a subform. I can either enter a date by typing it in the field or by using that little Access calendar.

[puppydogbuddy]

LastMeeting is a subform. I can either enter a date by typing it in the field or by using that little Access calendar.

Eric,
is there some reason you don.t want to bind your LastMeeting subform to the underlying table? I think that is your problem. Try setting the record source property of the LastMeeting Subform to the LastMeeting table. Then try your application and see how it works.

[Ericks]

Eric,
is there some reason you don.t want to bind your LastMeeting subform to the underlying table? I think that is your problem. Try setting the record source property of the LastMeeting Subform to the LastMeeting table. Then try your application and see how it works.

Yes, the lastmeeting record source on the subform is bound to the lastmeeting table. It stores the last meeting date in that table which contains just one record: the last meeting date. The subform sits on the Compounds form and is not linked to that as is the CompoundsObservation form. Is there a way I could send you part of this database for you to take a look at?

[puppydogbuddy]

Yes, the lastmeeting record source on the subform is bound to the lastmeeting table. It stores the last meeting date in that table which contains just one record: the last meeting date. The subform sits on the Compounds form and is not linked to that as is the CompoundsObservation form. Is there a way I could send you part of this database for you to take a look at?

Yes, go to my profile and download my VCard. It contains my email address. I have Access 2000, so your file has to be versions 97 or 2000.

[Ericks]

Yes, go to my profile and download my VCard. It contains my email address. I have Access 2000, so your file has to be versions 97 or 2000.

Ok, thanks. I will have dinner soon so I will send it tomorrow.