Hi ifoundgoldbug,
I have a similar db for Generator Service, where some customers get service
monthly, some annually, some twice a year etc, and the way I set it up is
with a field for the optional service periods(contract_type) (lookup table
keeps data clean here). Then on the data entry form, the user inputs the
contract type, and sets the first due date. After that, on the subform,
every time they complete a service, I do a calculation based on the service
period, to populate teh next due date - basically it's a select case which
provides the date add factor, then a DateAdd function to determine the next
due date, a SQL statement to insert the new service due record, and a
requery to refresh the form. Below is the code, which is set on the after
update event of the field. It may not be very clear, but hopefully it's
something you can work with:
''''' start code
Dim dt As Date
Dim rsp As String
Dim gen As Long
Dim ctEnd As Date
Dim last As Date
Dim due As Date
Dim ct As String
Dim m1 As Integer
Dim svc As Integer
Dim ann As String
Dim mo As String
gen = Me.GenID
ctEnd = Forms!frmcontracts.fldEndDate
last = Me!fldDue
ct = Forms!frmcontracts.fldType
svc = DMax("svcID", "tblsvccalls", "genID = " & Me.GenID)
If svc > Me.SvcID Then
Exit Sub
Else
If Me.cmbEmp <> "" Then
rsp = InputBox("When was the maintenance done?")
If rsp = "" Then
Me.cmbEmp = ""
Me.Requery
Exit Sub
Else
If ctEnd = Date Or ctEnd < Date Then
MsgBox "Contract Expired or Complete."
Else
dt = Format(rsp)
sql = "INSERT INTO tblSvcCalls " _
& "(GenID,fldLast) " _
& "VALUES (" & gen & ", #" & dt & "#)"
DoCmd.RunSQL sql
svc = DMax("svcID", "tblSvcCalls")
Select Case ct
Case "annual"
m1 = 12
Case "bi-annual"
m1 = 6
Case "monthly"
m1 = 1
Case "quarterly"
m1 = 3
Case "tri-annual"
m1 = 4
Case Else
m1 = 0
End Select
due = DateAdd("m", m1, last)
sql = "UPDATE tblSvcCalls " _
& "SET fldDue = #" & due & "# " _
& "WHERE svcID = " & svc
DoCmd.RunSQL sql
ann = Forms!frmcontracts!frmGenerators.Controls!fldAnnualDue
mo = MonthName(Month(due))
If mo = ann Then
sql = "UPDATE tblSvcCalls " _
& "SET fldAnnualSvc = True " _
& "WHERE svcID = " & svc
DoCmd.RunSQL sql
End If
End If
End If
Else
dt = DMax("fldLast", "tblSvcCalls", "GenID = " & gen)
If dt = Me.fldLast Then
Exit Sub
Else
sql = "DELETE * FROM tblSvcCalls " _
& "WHERE GenID = " & gen & " " _
& "AND fldLast = #" & dt & "#"
DoCmd.RunSQL sql
End If
End If
Me.Requery
End If
''''''''''''end code
