Private Sub saveProjectDetails_Click()
DoCmd.Close acForm, Me.Name, acSaveNo
incrementMonthNumAndCreateEP
End Sub
Private Sub incrementMonthNumAndCreateEP()
Dim strtDt As Date, EPNum$
Dim SQLcomm1$, SQLcomm2$
Dim monthNum As Integer, r As Recordset
monthNum = DCount("*", "formatDate", "[YM] = '" & Format(Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]")), "yyyymm") & "'")
SQLcomm1 = "UPDATE ProjectList SET MonthCount = " & monthNum & " WHERE ProjectID =" & DMax("ProjectID", "ProjectList") & ";"
DoCmd.SetWarnings (False)
DoCmd.RunSQL SQLcomm1
strtDt = Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]"), 0)
If (strtDt) = 0 Then
EPNum = ""
Else
EPNum = "EP" & Format(Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]"), 0), "yymm") & "-" & Format(monthNum, "00")
End If
If EPNum = DLookup("EPNumber", "ProjectList") Then
EPNum = "EP" & Format(Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]"), 0), "yymm") & "-" & Format(monthNum + 1, "00")
End If
SQLcomm2 = "UPDATE ProjectList SET EPNumber ='" & EPNum & "'WHERE ProjectID = " & DMax("ProjectID", "ProjectList") & ";"
DoCmd.RunSQL SQLcomm2
DoCmd.SetWarnings (True)
End Sub