1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset, rstS As DAO.Recordset
Dim strSQL As String
Dim cuSolde As Currency
'Ici on va chercher le solde du début
strSQL = "SELECT [Comptes Bancaire].[Solde Initial] FROM [Comptes Bancaire] " _
& "WHERE ((([Comptes Bancaire].NCompte)=" & Me.NCompte & "));"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
cuSolde = rst("Solde Initial")
rst.Close
Set rst = Nothing
'Ici on parcours les enregistrements pour avoir les dates
strSQL = "SELECT Operations.NCompte, Operations.[Date Oper], Operations.Solde FROM Operations " _
& "WHERE (((Operations.NCompte)=" & Me.NCompte & ")) ORDER BY Operations.[Date Oper];"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
While rst.EOF = False
'Ici on met à jour le solde dans les enregistrements
strSQL = "SELECT Sum(Operations.Débit) AS DébitT, Sum(Operations.Crédit) AS CréditT FROM Operations " _
& "WHERE (((Operations.NCompte)=" & Me.NCompte & ") AND ((Operations.[Date Oper])<=#" & rst("Date Oper") & "#));"
Set rstS = db.OpenRecordset(strSQL)
If rstS.EOF = False Then
rst.Edit
rst("Solde") = cuSolde + rstS("DébitT") - rstS("CréditT")
rst.Update
End If
rst.MoveNext
Wend
'On libère les variables
rst.Close
Set rst = Nothing
rstS.Close
Set rstS = Nothing
Set db = Nothing
End Sub |
Partager