Help writing an expression for a form

[jedost77]

I am creating a database at my job. The database tracks how many hours a CONSULTANT spent with a CLIENT vs. the number of hours the CLIENT has bought. The hours from the CONSULTANT are entered into three different subforms (because there are a few "types" of hours). I want the total number of hours bought (entered/shown in the parent form) to subtract the hours the consultant worked (hours entered/shown in the sub forms). I half-way got it to work but I am making several entries in each subform and I need to calculate ALL hours. If someone could please help, I would greatly appreciate it. I have attached a copy of the database so to give you a better idea as to what I am talking about.

Thank you.

 

[Calvin]

In your Parent form use the forms OnCurrent event as your trigger and within it use a DLookup to get what you need, pass it to a variable and then do what ever math you want to it.
Example:

Private Sub Form_Current()

    'get the hours worked for the current program number
    Dim lngHoursWorked As Long
    lngHoursWorked = DLookup("Sum(EventHrsWorked)", "[Event Logger]", "ProgramNumber='" & Me.ProgramNumber & "'")

    'do the math
    Me.txtHoursRemaining = (Me.DaysPurchased * 8) - lngHoursWorked
    
End Sub

In the example above I also added a textbox to the form named txtHoursRemaining.