Warning email sent when the previous 7 days reach max

[Tkandy]

I have a table which we are allowed to use 35000 cubic metres of water in a 7 day period, from all the wells. listed below


[WellID][WellsDate][well4][well9][well5][well10][well12]

1. they enter usage in a form

need it to be able to

1. calculate usage (average used?0 and give error if we are likley to run out
2. email to be sent out to key personal on possible overusage

 

[dkinley]

Just swagging something here for you ....

Suppose the user clicks a button or you have this run when the record is updated or whatever trigger you want to use goes into effect ....

Create a select query that will perform the summation for your well(s) over the 7 day period (yes, this is a seperate issue and will need to ask this in the query section of the site).

Dim dWellAverage As Double
Dim strToName As String
Dim strSubject As String
 
'calculate the average
dWellAverage = Nz(DLookup("TotalofWaterUsed", "QryThatIJustMade", "WellID = " & SomeIDIWillSupplyToGetTheRightRecord),0)/DivideBySomeNumber
 
 
'determine if it exceeds something
If dWellAverage = 0 Then
    MsgBox "Could not locate a record"
 
ElseIf dWellAverage > 350000 Then
 
   strSubject = "Warning! Wells are about to do something!"
   strToName = "eljefe@work.com; imfired@work.com"
 
   DoCmd.SendObject , , , _
     strToName, , , _
     strSubject, , True, False
End If

Again .. all air code- just something to get you going.

-dK