Conditional Formatting not working on Subform

[Arvin]

Hello,

I am trying to use the following vba to generate a msgbox popup but its not working and no errors appear either ...

Private Sub SumOfPlannedHours_BeforeUpdate(Cancel As Integer)
   If Me!SumOfPlannedHours > "34.15" Then
      MsgBox "You have exceeded 34.15 hrs work duration limit", vbOKOnly
   End If
End Sub

i have also tried ...

Private Sub SumOfPlannedHours_BeforeUpdate(Cancel As Integer)
   If (SumOfPlannedHours) > "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

Any suggestions ... please and thank you in advance

 

[boblarson]

How about:

Private Sub SumOfPlannedHours_BeforeUpdate(Cancel As Integer)
   If [COLOR=red]Me.SumOfPlannedHours > 34.15[/COLOR] Then
       MsgBox "You have exceeded your planned hours limit of 34.15"), 0, "Weekly Schedule Control System"
       Cancel = True
   End If
End Sub

 

[Arvin]

Hi Bob,

Just tried it and same thing ... no errors and no popups ... strange ...

 

[boblarson]

Is SumOfPlannedHours manually entered or is it done some other way? If it is not done manually then the After Update event doesn't fire so you would need to call it explicitly from wherever it does do the update.

 

[Arvin]

Is SumOfPlannedHours manually entered or is it done some other way? If it is not done manually then the After Update event doesn't fire so you would need to call it explicitly from wherever it does do the update.

Hi Bob,

The SumOfPlannedHours is based on a "Totals" query ... ...

I have another form ... and within that ..I have trying the following and I get the same thing ... no errors and no popups...

Private Sub Form_AfterUpdate()
Forms!ActivityDetailsfrm!ActivityCommentsListBox.Requery
Forms!Mainfrm!EmployeeDashboardSubfrm.Requery
   Exit Sub
   If Forms!Mainfrm!EmployeeDashboardSubfrm.SumOfPlannedHours > 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

This form is called UpdtWklyActCmntsfrm

 

[boblarson]

If the sum is happening in the query then there is no after update to occur. So it will never fire.

 

[Arvin]

Okay ... so how would I get around that ?...

This issue is that ... I need to let the user know that they have exceeded their "Planned hours" limit which is 34.15 ... so that they can adjust their time accordingly.

I've been reading around and it seems that dcount or dsum might work ... i have found this on the web .... and tried it but no luck ...

Private Sub Form_Open(Cancel As Integer) 
Dim Msg As String 
Dim intX As Integer 
  
intX = DCount("[PrimaryKeyField]", "YourQueryName") 
If intX < 1 Then 
Msg = "No Records are Returned by the Query" 
MsgBox Msg, vbOKOnly + vbInformation 
DoCmd.Close acForm, "YourFormName" 
End If 
End Sub
[/code
 
***Note** This is just example code ... 
 
or ....do you mean to say that because the sum is done by a query ...nothing will fire ? 
 
Thank you in advance

 

[boblarson]

Where are they entering their time. It should be there that you do the check, in the Before Update event of the Form.

 

[Arvin]

Where are they entering their time. It should be there that you do the check, in the Before Update event of the Form.

Thank you for that tip Bob ...

in that form (AddActivityCommentsfrm) ... I already have a BeforeUpdate event which is

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If (Me.PlannedHours) <= "0.00" 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
End Sub

This pertains to the form it self ...

I have tried the following but I get an Runtime error # 438 ... Object doesn't support this property or object ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If (Forms!Mainfrm!EmployeeDashboardSubfrm.SumOfPlannedHours) > "34.15" Then
       MsgBox ("You have exceeded your planned hours limit of 34.15"), 0, "Weekly Schedule Control System"
       Cancel = True
   End If
   Exit Sub
   If (Me.PlannedHours) <= "0.00" 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
End Sub

 

[boblarson]

Something like this (make sure to change the applicable names to your proper field and table names):

[FONT=Times New Roman][SIZE=3]Private Sub Form_BeforeUpdate(Cancel As Integer)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  If (Me.PlannedHours) <= "0.00" Or IsNull(Me.SubActivityDetails) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      MsgBox ("You must enter your planned hours and provide a description of the activity"), 0, "Weekly Schedule Control System"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Cancel = True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  End If[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]If DSum(“[PlannedHours]”, “tblWhateverYourTableIsNamed”, “[IDofPerson] = “ & Me.YourIDOfPersonOnForm) + Me.PlannedHours > 34.15  Then[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]          MsgBox ("You have exceeded your planned hours limit of 34.15"), 0, "Weekly Schedule Control System"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Cancel = True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  End If[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[Arvin]

Good morning ...

After reviewing the suggested code, I understand what its trying to do but and that is what I need however, there is more then 1 table involved.

I have the following tables:

Employeetbl - this stores the employee data (such as working hours duration 34.15)

EmployeeActivitiestbl - this stores the employee's activities

EmployeeActivitiesCommentstbl - this stores comments associated to the Activity is the EmployeeActivitiestbl

each table has a foreign key that links it to the appropriate table.

 

[boblarson]

Which form/forms are you looking to use this message and cancel with? I'm not too clear just looking at the database. Which form is where the actual hours entry is made where if they put in too much it should flag them as you've wanted?

 

[Arvin]

Hi Bob,

There are 2 forms that the users will use to enter there time ...

1st form - AddActivityComments - In this form ... they can enter their "Planned Hours" when they are planning their week and the "Planned Hours"

2nd form - UpdtWklyActCmntsfrm - In this form ... they can revise their "Planned Hours" and enter their "Actual Hours" spent on the activity.

At the end of the day ...the thought is that the employee cannot plan for more than 34.15 hrs of week in a week ....

 

[Arvin]

Hello,

I've been battling this for awhile now ... and I'm starting to wonder if this even the approach ...as in the right fields ... should a not based on the current weekending date ?

 

[boblarson]

I just looked again and I do think you are right. I think your DSum needs to use a Date parameter for which ever your date in the table is applicable. Something which takes the same date from your Current Report Weekending Date and subtracts 7 days and then uses this as the criteria (or something like it).

Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2)

 

[Arvin]

I just looked again and I do think you are right. I think your DSum needs to use a Date parameter for which ever your date in the table is applicable. Something which takes the same date from your Current Report Weekending Date and subtracts 7 days and then uses this as the criteria (or something like it).

Between ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2) - 7 And ([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2)

Question ....

There are 2 forms that the users will use to enter there time ...

1st form - AddActivityComments - In this form ... they can enter their "Planned Hours" when they are planning their week and the "Planned Hours"

2nd form - UpdtWklyActCmntsfrm - In this form ... they can revise their "Planned Hours" and enter their "Actual Hours" spent on the activity.

and on both of these form I have a unbound field for the weekending date (CurRptWeekendDatetxt) with the defualt value set to
( =([CommentsDateTimeStamp])+7-Weekday([CommentsDateTimeStamp],2))

Can I ref that field in the DSUM Code? ... if so how? ... the reason I ask is beacause I'm think I would need to have a weenending date in a table?

Or am I over thinking this ?

PLease advise and thank you in advance,

Arvin

 

[Arvin]

Okay ...this is wierd ..or maybe its me

I'm using this code ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If (Me.PlannedHours) <= "0.00" 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.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

And I get no errors ... but when I test it I don't get the msgbox ....

 

[Arvin]

Update ....

If I use this code ... I get a 3075 syntax (missing operator) in qry expression error

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If (Me.PlannedHours) <= "0.00" 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

 

[boblarson]

Firt part =

(Me.PlannedHours) <= "0.00"

Should be

(Me.PlannedHours) <= 0

not text so no quotes.

 

[Arvin]

Firt part =

(Me.PlannedHours) <= "0.00"

Should be

(Me.PlannedHours) <= 0

not text so no quotes.

Changed it ... but still getting the same error I have attached a screenshot ....

It has to do with the dates parameter ....