Conditional Formatting not working on Subform

[boblarson]

You are missing the right parentheses from the DSum function and then you also have a strange calculation there with the equal sign and all. I changed to;

If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2)[COLOR=red])[/COLOR] [COLOR=red][B]+ Me.PlannedHours[/B][/COLOR] > 34.15 Then

 

[Arvin]

Hi Bob,

thank you ... but now I'm getting a Compile Error: Expected: list separator or )

I have tried but no luck ....

 

[Arvin]

Here is the code that gives me the error

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If (Me.PlannedHours) <= 0 Or IsNull(Me.SubActivityDetails) Then
      MsgBox ("You must enter your planned hours and provide a description of the activity"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
'If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) =  " & Me.CurRptWeekendDatetxt) + Me.PlannedHours > 34.15 Then
 If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2))  + Me.PlannedHours > 34.15 Then
          MsgBox ("You have exceeded your planned hours limit of 34.15"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
 
End Sub

 

[Arvin]

Just tried this and got rid of the syntax error and now I'm getting a type mismatch ....

If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2))" + Me.PlannedHours > 34.15) Then

 

[Arvin]

ITS ALIVE ! ! ! ! ! muhahahaha

If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) = " & Me.CurRptWeekendDatetxt + Me.PlannedHours > 34.15) Then

did the trick!

Thank you for all your help Bob ... I really appreciate!

 

[Arvin]

Final working code is ......

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If (Me.PlannedHours) <= 0 Or IsNull(Me.SubActivityDetails) Then
      MsgBox ("You must enter your planned hours and provide a description of the activity"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
 If DSum("[PlannedHours]", "EmployeeWeeklyActivityCommentstbl", "[CommentsDateTimeStamp] Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) = " & Me.CurRptWeekendDatetxt + Me.PlannedHours > 34.15) Then
          MsgBox ("You have exceeded your planned hours limit of 34.15"), 0, "Weekly Schedule Control System"
      Cancel = True
  End If
 
End Sub