Need assitance with an expression for a validation rule

[Beltramo64]

I need an expression for a validation rule for each week, ie... Field AS1_18, that will not allow input if the total of this field exceeds 40 hours. 3 tables, hence 3 fields are involved here, the TBL_Assigned Calendar ie...field AS1_18, TBL_ManpowerID ie.. ManpowerID (Separate table linked to the Assigned Calendar table) and the TBL_Customer ie... field CustomerID;









Thanks in advance,

[ranman256]

in the form textboxes: txtSumCol1, txtSumCol2, etc...
you would put checks in the afterUpdate event

sub txtSumCol1_afterupdate()
    CheckTot40 txtSumCol1
end sub

sub txtSumCol2_afterupdate()
    CheckTot40 txtSumCol2
end sub

'etc...for every total box

sub CheckTot40(txtBx as textbox)
    if txtBx>40 then
        msgbox "The total cannot be > 40"
    endif
end sub

[Beltramo64]

Thank you so much for the quick response, please note that I am a beginner when it comes to coding. I think I understand what you saying but I am a bit confused on where I should input this code and specifically how. My text box is 48. Please clarify

[Beltramo64]

Can an expression be used in the validation rule instead of the code? I cannot get the code suggested to work. I want to use the Sum of week field(s) ie... [AS1_18]. I will put in words what I require: if the sum of a particular week ie.. [AS1_18] of a particular person ie... ManpowerID for a Particular CustomerID exceeds 40 hours then do not allow to input in the form.

Here is what I tried, in the validation rule, but it does not work
DSum("[AS1_18]","TBL_AssignedCalendar","manpowerID=" & [manpowerID] & " or [AssignedcalendarID]=" & [AssignedCalendarID] & " or [CustomerID]=" & [CustomerID])<=40

[aytee111]

In the AfterUpdate event of each field, you can say:
If (Me!AS1_18 + Me!nameof totalfield) > 40 then
MsgBox "Total is > 40"
Me!AS1_18=0
End If

[Beltramo64]

Thanks
But I can not get it to work, it did this
I am unsure on what to put what is in the red below. When I go to load the number in AS1_18 it just gives me the message Total is >40, no matter what number I put in and it then goes to 0.

Private Sub Text48_AfterUpdate()
If (Me!AS1_18 + Me!ManPowerID) > 40 Then
MsgBox "Total is > 40"
Me!AS1_18 = 0
End If
End Sub

Any thoughts, thanks in advance

[ssanfu]

If I remember your previous posts, you were advised having 53 fields for the 52 weeks in a year is the wrong way to go because it is not a normalized design structure.

Looking at the images in your posts, I am not sure I am following what you are trying to do.
Maybe this is close???

[Micron]

Looks to me that in post 1 the detail section controls are in a subform on the main form. Not sure, because I'm not using your version of Access. If that's the case, you can't simply refer to subform controls as Me!Something. If it's a split form, there are some oddities about them and I'm not sure if they come in to play here. I don't use split forms, so I'm not certain. Manpower id looks like it's your combo, which is showing names, so how can the id be a number around 40? I agree the images don't tell the whole story.

[orange]

As others have hinted, I recommend you step back and tell us what you are trying to accomplish in plain English. Forget any jargon and tell us as you would tell an 8 year old. Your table(s) are not normalized and that will cause you problems. If you are going to use database, then get your requirements clarified and readers will offer more focused suggestions on how they may be resolved with Access.
Good luck with your project.

[Beltramo64]

The data I have inputted works fine, in the tables I have established. As I indicated before, I have 3 tables involved here a Manpower table (Has all the employees who can charge to a job ie... Primary Field ManpowerID), CustomerTable (Who they will be charging to, Primary Field CustomerID) and the AssignedCalendar table for the hours for each week they will be charging too (ie.. fields AS1_18, AS2_18, etc...) Inputting these hours in the form for each employees works great, no issues. All I need is a way to validate the total hours for a particular employee, so the inputter will know when he has gone over the 40 hour per week total a person can work. It's pretty easy when there is one Customer being charged to for that week ie... AS1_18 input 40 hours). It is when you want an employee to work on more than one customer per week, where I think and validation rule will come in handy. Can't exceed the sum of 40 hours per week (ie... Field AS1_18, AS2_18). I feel you can do this in the validation rule, in each one of the fields for the weeks ie... AS1_18, AS2_18, AS3_18 etc... But I am struggling to create that expression.