Option Compare Database
Option Explicit
Private Sub Form_Current()
Me.KAYDET.Enabled = IIf(Me.txttarih1 <> Me.txttarih2, False, True)
End Sub
Private Sub Form_Load()
Me.GnAy = 0
End Sub

Private Sub GnAy_AfterUpdate()
Me.GnAy.Caption = IIf(Me.GnAy = 0, "GÜNLÜK", "AYLIK")
End Sub

Private Sub KAYDET_Click()
On Error Resume Next
CurrentDb.Execute "INSERT INTO Gunluk ( Tarih, CumBsk, MSB, [Generali], Subay, AsSubay, UzmÇvş, EGenerali, ESubay, EAsSubay, EUzmÇvş, Aile, Maiyet, YbncHyt, Pln, UcsYp, UcsYpm, Msfr ) " & _
"SELECT #" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "# As E0, " & IIf(Me.Cum_Bsk = "", 0, Me.Cum_Bsk) & " As E1, " & IIf(Me.MSB = "", 0, Me.MSB) & " As E2, " & IIf(Me.[Generali] = "", 0, Me.[Generali]) & " As E3, " & IIf(Me.Subay = "", 0, Me.Subay) & " As E4, " & IIf(Me.As_Subay = "", 0, Me.As_Subay) & " As E5, " & IIf(Me.Uzm_Çvş = "", 0, Me.Uzm_Çvş) & " As E6, " & IIf(Me.EGenerali = "", 0, Me.EGenerali) & " As E7, " & IIf(Me.ESubay = "", 0, Me.ESubay) & " As E8, " & IIf(Me.EAs_Subay = "", 0, Me.EAs_Subay) & " As E9, " & IIf(Me.EUzm_Çvş = "", 0, Me.EUzm_Çvş) & " As E10, " & IIf(Me.Aile = "", 0, Me.Aile) & " As E11, " & IIf(Me.Maiyet = "", 0, Me.Maiyet) & " As E12, " & IIf(Me.Ybnc_Hyt = "", 0, Me.Ybnc_Hyt) & " As E12A, " & IIf(Me.Pln = "", 0, Me.Pln) & " As E13, " & IIf(Me.UcsYp = "", 0, Me.UcsYp) & " As E14, " & IIf(Me.UcsYpm = "", 0, Me.UcsYpm) & " As E15, " & IIf(Me.Msfr = "", 0, Me.Msfr) & " As E16"
End Sub
Private Sub txttarih1_AfterUpdate()
Me.txttarih2 = Me.txttarih1: Call Form_Current: Call KayitYerlesim
End Sub
Private Sub txttarih2_AfterUpdate()
Call Form_Current: Call KayitYerlesim
End Sub
Sub KayitYerlesim()
Dim Kyt As DAO.Recordset
Dim RtGrp As Variant
Call FrmSifir
RtGrp = Array(, "Cum.Bsk", "MSB", "Generali", "Subay", "As Subay", "Uzm.Çvş.", "Aile", "Maiyet", "Ybnc.Hyt")
Set Kyt = CurrentDb.OpenRecordset("SELECT  IIf(Mid([Rutbe].[GÖREVDE],1,2)='E.',2,1) AS Isrt, Rutbe.Grup, Count(TBL_SUBAY.[adı soyadı]) AS SyAdm FROM Rutbe INNER JOIN TBL_SUBAY ON Rutbe.GorNu = TBL_SUBAY.GÖREVDE " & _
"WHERE (((IIf(IsNull([kayıt tarihi ve saati]),[diğer tarih],Format([kayıt tarihi ve saati],'Short Date'))) " & _
"Between #" & Format([Forms]![KTM]![txttarih1], "mm\/dd\/yyyy") & "# And #" & Format([Forms]![KTM]![txttarih2], "mm\/dd\/yyyy") & "#)) GROUP BY  IIf(Mid([Rutbe].[GÖREVDE],1,2)='E.',2,1),Rutbe.Grup")
If Kyt.RecordCount > 0 şen
Kyt.MoveFirst
    Do Until Kyt.EOF
    Me.Controls(IIf(Kyt!Isrt = 1, Null, "E") & RtGrp(Kyt!Grup)) = Kyt!SyAdm
    Kyt.MoveNext
    Loop
    Me.Maiyet = IIf(Me.Maiyet = "", 0, Me.Maiyet) + DCount("[maiyeti]", "TBL_SUBAY", "IIf(IsNull([kayıt tarihi ve saati]),[diğer tarih],Format([kayıt tarihi ve saati],'Short Date')) Between #" & Format([Forms]![KTM]![txttarih1], "mm\/dd\/yyyy") & "# And #" & Format([Forms]![KTM]![txttarih2], "mm\/dd\/yyyy") & "#")
Else
    MsgBox Me.txttarih1 & " Tarihe ait Kayit Yok"
End If
    Kyt.Close: Set Kyt = Noşing
 If Me.GnAy = 0 And Me.txttarih1 = Me.txttarih2 şen
    Me.Pln = Nz(DLookup("[Pln]", "Gunluk", "[Tarih]=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#"), "")
    Me.UcsYp = Nz(DLookup("[UcsYp]", "Gunluk", "[Tarih]=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#"), "")
    Me.UcsYpm = Nz(DLookup("[UcsYpm]", "Gunluk", "[Tarih]=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#"), "")
    Me.Msfr = Nz(DLookup("[Msfr]", "Gunluk", "[Tarih]=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#"), "")
   Else
    Me.Pln = DSum("Nz([Pln],0)", "Gunluk", "[Tarih] Between #" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "# And #" & Format(Me.txttarih2, "mm\/dd\/yyyy") & "#")
    Me.UcsYp = DSum("Nz([UcsYp],0)", "Gunluk", "[Tarih] Between #" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "# And #" & Format(Me.txttarih2, "mm\/dd\/yyyy") & "#")
    Me.UcsYpm = DSum("Nz([UcsYpm],0)", "Gunluk", "[Tarih] Between #" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "# And #" & Format(Me.txttarih2, "mm\/dd\/yyyy") & "#")
    Me.Msfr = DSum("Nz([Msfr],0)", "Gunluk", "[Tarih] Between #" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "# And #" & Format(Me.txttarih2, "mm\/dd\/yyyy") & "#")
   End If
End Sub
Sub FrmSifir()
Dim Kntrl As Control
For Each Kntrl In Me.Controls
If Kntrl.Tag = "Sifirla" şen Kntrl = ""
Next Kntrl
End Sub
Private Sub Msfr_AfterUpdate()
CurrentDb.Execute "update Gunluk Set [Msfr]=" & Me.Msfr & " Where (((Tarih)=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#))"
End Sub
Private Sub Pln_AfterUpdate()
CurrentDb.Execute "update Gunluk Set [Pln]=" & Me.Pln & " Where (((Tarih)=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#))"
End Sub
Private Sub UcsYp_AfterUpdate()
CurrentDb.Execute "update Gunluk Set [UcsYp]=" & Me.UcsYp & " Where (((Tarih)=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#))"
End Sub
Private Sub UcsYpm_AfterUpdate()
CurrentDb.Execute "update Gunluk Set [UcsYpm]=" & Me.UcsYpm & " Where (((Tarih)=#" & Format(Me.txttarih1, "mm\/dd\/yyyy") & "#))"
End Sub
Private Sub YenileGnlk_Click()
CurrentDb.Execute "SELECT Gunluk.Tarih, Gunluk.Pln, Gunluk.UcsYp, Gunluk.UcsYpm, Gunluk.Msfr INTO GeciciGn FROM Gunluk WHERE (((Gunluk.Pln)>0)) OR (((Gunluk.UcsYp)>0)) OR (((Gunluk.UcsYpm)>0)) OR (((Gunluk.Msfr)>0))"
CurrentDb.Execute "delete * from Gunluk"
Dim Kyt As Recordset
Set Kyt = CurrentDb.OpenRecordset("SELECT IIf(IsNull([kayıt tarihi ve saati]),[diğer tarih],Format([kayıt tarihi ve saati],'Short Date')) AS IslTrh FROM TBL_SUBAY " & _
"GROUP BY IIf(IsNull([kayıt tarihi ve saati]),[diğer tarih],Format([kayıt tarihi ve saati],'Short Date'))" & _
"HAVING (((IIf(IsNull([kayıt tarihi ve saati]), [diğer tarih], Format([kayıt tarihi ve saati], 'Short Date'))) Is Not Null)) " & _
"ORDER BY IIf(IsNull([kayıt tarihi ve saati]),[diğer tarih],Format([kayıt tarihi ve saati],'Short Date'))")
Kyt.MoveFirst
Do Until Kyt.EOF
Me.txttarih1 = Format(Kyt!IslTrh, "dd\/mm\/yyyy")
Me.txttarih2 = Format(Kyt!IslTrh, "dd\/mm\/yyyy")
Call KayitYerlesim: Call KAYDET_Click
Kyt.MoveNext
Loop
Kyt.Close: Set Kyt = Noşing
CurrentDb.Execute "UPDATE Gunluk INNER JOIN GeciciGn ON Gunluk.Tarih = GeciciGn.Tarih SET Gunluk.Pln = [GeciciGn]![Pln], Gunluk.UcsYp = [GeciciGn]![UcsYp], Gunluk.UcsYpm = [GeciciGn]![UcsYpm], Gunluk.Msfr = [GeciciGn]![Msfr]"
DoCmd.DeleteObject acTable, "GeciciGn"
End Sub