CHAPITRE
QUATRIEME :
REALISATION DE LA SOLUTION
PRECONISEE
Ce chapitre concerne la réalisation
de la base de données. Cette base de données a été
réalisée grâce Microsoft visual basic 6.0, Microsoft Access
2003 et son interrogation a été l'oeuvre du langage `QBE' (Query
By Example) et du SQL (structured query langage),sans oublier l'intervention de
Crystal Reports Pro pour l'implémentation des états.
III.1. LES TABLES ET LES
RELATIONS
III.2. LES REQUETES
III.3 LES ECRANS DES
MENUS
1. Menu Principal
'déclaration des variables de connexion
Dim con As New Connection
Dim caisse As New Recordset
Dim centralisation As New ADODB.Recordset
Dim exercice As New Recordset
Dim malade As New ADODB.Recordset
Private Sub MDIForm_Load()
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = App.Path & "\tfc1.mdb" 'connnection
à la base des données
con.Mode = adModeReadWrite
con.Open 'ouverture de la connection
End Sub
2. Le sous menu Entrées des
informations
a. Journal des opérations diverses.
Dim RowValue
Dim cnn1 As ADODB.Connection, MonRs As ADODB.Recordset, RsLect As
ADODB.Recordset, tout As New ADODB.Recordset, imput As New ADODB.Recordset,
sommeDC As New ADODB.Recordset
Private Sub cmdExercPrec_Click()
MonRs.MovePrevious
If MonRs.BOF Then
MsgBox "Premier exercice comptable", vbInformation, "CSCompta"
MonRs.MoveFirst
Else
End If
txtExercice.Text = MonRs("CodExercice")
End Sub
Private Sub DataList1_Click()
DataGrid1.Text = DataList1.Text
DataList1.Visible = False
End Sub
Private Sub DataList1_LostFocus()
DataList1.Visible = False
End Sub
Private Sub DataGrid1_Click()
DataList1.Visible = False
End Sub
Private Sub DataGrid1_Scroll(Cancel As Integer)
DataList1.Visible = False
End Sub
Private Sub DataGrid1_ButtonClick(ByVal ColIndex As Integer)
If ColIndex = 1 Then
DataList1.Top = DataGrid1.Top +
DataGrid1.RowTop(DataGrid1.Row) + DataGrid1.RowHeight
DataList1.Left = DataGrid1.Left +
DataGrid1.Columns(ColIndex).Left
' Width and Height properties can be set a design time
' The width of the list does not have to be the same as the
width of the grid column
DataList1.Width = DataGrid1.Columns("NumCompDiv").Width
DataList1.Height = 2000
DataList1.Visible = Not DataList1.Visible
If DataList1.Visible Then
DataList1.Text = DataGrid1.Text
DataList1.ZOrder ' etre sur que le datalist est au
dessus du datagrid
End If
End If
End Sub
Private Sub cmdDernOp_Click()
On Error Resume Next
If val(txtVer.Text) = 0 Then
RsLect.MoveLast
txtNumOp.Text = RsLect("NumOp")
txtNumOp.Text = RsLect("NumOp")
txtDateOp.Text = RsLect("DateOp")
txtLibelleOp.Text = RsLect("LibelleOp")
txtNumPJ.Text = RsLect("NumPj")
txtTypOp.Text = RsLect("TypOp")
Set DataGrid1.DataSource = RsLect("cmdPetFille").Value
Else
MsgBox "Opération non équilibré",
vbCritical, "CSCompta"
Beep
End If
DataGrid1.Columns(0).Caption = "Opération N°"
'DataGrid1.Columns(1).Caption = "Compte"
DataGrid1.Columns(2).Caption = "Débit"
DataGrid1.Columns(3).Caption = "Crédit"
'Pour le calcul du total debit et credit et degagement de la
difference
With sommeDC
.ActiveConnection = cnn1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open "select sum(MontDeb)as SD, sum(MontCred) as SC from
Timputation where NumOp='" & txtNumOp.Text & "'"
txtSD.Text = sommeDC("SD")
txtSC.Text = sommeDC("SC")
txtVer.Text = val(txtSD.Text) - val(txtSC.Text)
.Close
End With
End Sub
Private Sub cmdNouvOp_Click()
MonRs.AddNew
End Sub
Private Sub cmdOpPrec_Click()
If val(txtVer.Text) = 0 Then
RsLect.MovePrevious
If RsLect.BOF Then
MsgBox "Debut des opérations", vbInformation,
"CSCompta"
RsLect.MoveFirst
End If
txtNumOp.Text = RsLect("NumOp")
txtNumOp.Text = RsLect("NumOp")
txtDateOp.Text = RsLect("DateOp")
txtLibelleOp.Text = RsLect("LibelleOp")
txtNumPJ.Text = RsLect("NumPj")
txtTypOp.Text = RsLect("TypOp")
Set DataGrid1.DataSource = RsLect("cmdPetFille").Value
Else
MsgBox "Opération non équilibré",
vbCritical, "CSCompta"
Beep
End If
DataGrid1.Columns(0).Caption = "Opération N°"
'DataGrid1.Columns(1).Caption = "Compte"
DataGrid1.Columns(2).Caption = "Débit"
DataGrid1.Columns(3).Caption = "Crédit"
'Pour le calcul du total debit et credit et degagement de la
difference
With sommeDC
.ActiveConnection = cnn1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open "select sum(MontDeb)as SD, sum(MontCred) as SC from
Timputation where NumOp='" & txtNumOp.Text & "'"
txtSD.Text = sommeDC("SD")
txtSC.Text = sommeDC("SC")
txtVer.Text = val(txtSD.Text) - val(txtSC.Text)
.Close
End With
End Sub
Private Sub cmdOpSuiv_Click()
If val(txtVer.Text) = 0 Then
RsLect.MoveNext
If RsLect.EOF Then
MsgBox "Fin des opérations", vbInformation, "CSCompta"
RsLect.MoveLast
End If
txtNumOp.Text = RsLect("NumOp")
txtNumOp.Text = RsLect("NumOp")
txtDateOp.Text = RsLect("DateOp")
txtLibelleOp.Text = RsLect("LibelleOp")
txtNumPJ.Text = RsLect("NumPj")
txtTypOp.Text = RsLect("TypOp")
Set DataGrid1.DataSource = RsLect("cmdPetFille").Value
Else
MsgBox "Opération non équilibré",
vbCritical, "CSCompta"
Beep
End If
'Pour la vérification de l'équilibre
On Error Resume Next
With sommeDC
.ActiveConnection = cnn1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open "select sum(MontDeb)as SD, sum(MontCred) as SC from
Timputation where NumOp='" & txtNumOp.Text & "'"
txtSD.Text = sommeDC("SD")
txtSC.Text = sommeDC("SC")
txtVer.Text = val(txtSD.Text) - val(txtSC.Text)
.Close
End With
DataGrid1.Columns(0).Caption = "Opération N°"
'DataGrid1.Columns(1).Caption = "Compte"
DataGrid1.Columns(2).Caption = "Débit"
DataGrid1.Columns(3).Caption = "Crédit"
End Sub
Private Sub cmdPremExer_Click()
MonRs.MoveNext
If MonRs.EOF Then
MsgBox "Dernier exercice", vbInformation, "CSCompta"
MonRs.MoveLast
Else
End If
txtExercice.Text = MonRs("CodExercice")
txtNumOp.Text = RsLect("NumOp")
Set DataGrid1.DataSource = RsLect("cmdPetFille").Value
End Sub
Private Sub cmdPremOp_Click()
If val(txtVer.Text) = 0 Then
RsLect.MoveFirst
txtNumOp.Text = RsLect("NumOp")
txtNumOp.Text = RsLect("NumOp")
txtDateOp.Text = RsLect("DateOp")
txtLibelleOp.Text = RsLect("LibelleOp")
txtNumPJ.Text = RsLect("NumPj")
txtTypOp.Text = RsLect("TypOp")
Set DataGrid1.DataSource = RsLect("cmdPetFille").Value
Else
MsgBox "Opération non équilibrée",
vbCritical, "CSCompta"
End If
DataGrid1.Columns(0).Caption = "Opération N°"
'DataGrid1.Columns(1).Caption = "Compte"
DataGrid1.Columns(2).Caption = "Débit"
DataGrid1.Columns(3).Caption = "Crédit"
'Pour le calcul du total debit et credit et degagement de la
difference
With sommeDC
.ActiveConnection = cnn1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open "select sum(MontDeb)as SD, sum(MontCred) as SC from
Timputation where NumOp='" & txtNumOp.Text & "'"
txtSD.Text = sommeDC("SD")
txtSC.Text = sommeDC("SC")
txtVer.Text = val(txtSD.Text) - val(txtSC.Text)
.Close
End With
End Sub
Private Sub Form_Load()
Set cnn1 = New ADODB.Connection
cnn1.CursorLocation = adUseClient
cnn1.Provider = "MSDataShape"
cnn1.Properties("Data Provider") = "Microsoft.Jet.OLEDB.4.0"
cnn1.ConnectionString = App.Path & "\tfc1.mdb"
cnn1.Mode = adModeReadWrite
cnn1.Open
Set MonRs = New ADODB.Recordset
MonRs.StayInSync = False
MonRs.LockType = adLockOptimistic
MonRs.Open "SHAPE {SELECT * FROM `Texercice`} AS cmdMere APPEND
(( SHAPE {SELECT * FROM `Topération`} AS cmdFille APPEND ({SELECT * FROM
`Timputation`} AS cmdPetFille RELATE 'NumOp' TO 'NumOp') AS cmdPetFille) AS
cmdFille RELATE 'CodExercice' TO 'CodExercice') AS cmdFille", cnn1
Set RsLect = New ADODB.Recordset
RsLect.LockType = adLockOptimistic
Set RsLect = MonRs("cmdFille").Value
'Set RsLect = RsLect("cmdPetFille").Value
txtExercice.Text = MonRs("CodExercice")
txtNumOp.Text = RsLect("NumOp")
txtDateOp.Text = RsLect("DateOp")
txtLibelleOp.Text = RsLect("LibelleOp")
txtNumPJ.Text = RsLect("NumPj")
txtTypOp.Text = RsLect("TypOp")
Set DataGrid1.DataSource = RsLect("cmdPetFille").Value
'pour inserer tous les numéros de comptes dans la
datalist1
With imput
.ActiveConnection = cnn1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open "Select NumCompDiv From TcompteDivisionnaire ORDER
BY NumCompDiv"
End With
Set Adodc1.Recordset = imput
DataList1.Visible = False
DataGrid1.Columns(1).Button = True
DataGrid1.Columns(0).Caption = "Opération N°"
'DataGrid1.Columns(1).Caption = "Compte"
DataGrid1.Columns(2).Caption = "Débit"
DataGrid1.Columns(3).Caption = "Crédit"
With sommeDC
.ActiveConnection = cnn1
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open "select sum(MontDeb)as SD, sum(MontCred) as SC from
Timputation where NumOp='" & txtNumOp.Text & "'"
txtSD.Text = sommeDC("SD")
txtSC.Text = sommeDC("SC")
txtVer.Text = val(txtSD.Text) - val(txtSC.Text)
.Close
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
cnn1.Close
End Sub
Private Sub verifEquil_Click()
If val(txtVer.Text) <> 0 Then
MsgBox "Votre écriture n'est pas
équilibrée!Veillez retoucher avant de passer", vbInformation,
"CSCompta"
Else
MsgBox "Ecriture correcte", vbInformation, "CScompta"
End If
End Sub
b. Journal de caisse
'declaration des variables de connection
Dim con As New Connection
Dim td As New Recordset
Private Sub Command1_Click()
End Sub
Private Sub cboCompte_Click()
td.ActiveConnection = con
td.Source = "select * from TcompteDivisionnaire where NumCompDiv=
'" & cboCompte.Text & "'"
td.Open
lblDesignCompDiv.Caption = td("DesignCompDiv")
td.Close
End Sub
Private Sub cboComptecaisse_Click()
td.ActiveConnection = con
td.Source = "select DesignCompDiv from Tcomptedivisionnaire where
NumCompDiv= '" & cboComptecaisse.Text & "'"
td.Open
lblcaisse.Caption = td("DesignCompDiv")
td.Close
td.ActiveConnection = con
td.Open "select sum(MontDeb) as SD,sum(MontCred) as SC from
Timputation where NumCompDiv ='" & cboComptecaisse.Text & "'"
txtSolde.Text = td("SD") - td("SC")
td.Close
End Sub
Private Sub cboNumOp_Click()
td.ActiveConnection = con
td.Source = "select DateOp from Topération where NumOp= '"
& cboNumOp.Text & "'"
td.Open
lblDateOp.Caption = td("DateOp")
td.Close
End Sub
Private Sub cboTypeOp_Click()
If cboTypeOp.Text = "Encaissement" Then
cboNumOp.Enabled = True
cboCompte.Enabled = True
txtDebit.Enabled = True
txtLibelle.Enabled = True
Else
cboNumOp.Enabled = True
cboCompte.Enabled = True
txtCredit.Enabled = True
txtLibelle.Enabled = True
txtDebit.Enabled = False
End If
End Sub
Private Sub cmdExecuter_Click()
If cboTypeOp.Text = "" Or cboNumOp.Text = "" Or cboCompte.Text =
"" Or cboMonnaie.Text = "" Or txtTaux.Text = "" Then
MsgBox "Vous devez remplir les champs recquis", vbCritical,
"CSCompta"
Else
On Error GoTo s
If cboTypeOp.Text = "Encaissement" Then
td.ActiveConnection = con
con.Execute "insert into caisse(NumOp,NumComp,Entre,Sortie)
values ('" & cboNumOp.Text & "','" & cboCompte.Text & "',0, '"
& val(txtDebit.Text) * val(txtTaux.Text) & "') "
con.Execute "insert into caisse(NumOp,NumComp,Entre,Sortie)
values ('" & cboNumOp.Text & "','" & cboComptecaisse.Text &
"','" & val(txtDebit.Text) * val(txtTaux.Text) & "' , 0)"
MsgBox "Operation effectué", vbInformation,
"CSCompta"
td.Open "select sum(MontDeb) as SD,sum(MontCred) as SC from
Timputation where NumCompDiv ='" & cboComptecaisse.Text & "'"
txtSolde.Text = td("SD") - td("SC")
td.Close
Else
If val(txtCredit.Text) < val(txtSolde.Text) Then
con.Execute "insert into caisse(NumOp,NumComp,Entre,Sortie)
values ('" & cboNumOp.Text & "','" & cboCompte.Text & "','"
& val(txtCredit.Text) & "',0) "
con.Execute "insert into caisse(NumOp,NumComp,Entre,Sortie)
values ('" & cboNumOp.Text & "','" & cboComptecaisse.Text &
"',0,'" & val(txtCredit.Text) & "') "
MsgBox "Operation effectué", vbInformation, "CSCompta"
Else
MsgBox "Vous ne pouvez pas faire sortir beaucoup plus qu'il a de
liquidité en caisse", vbInformation, "CSCompta"
End If
End If
End If
cboCompte.Text = ""
txtDebit.Text = 0
txtCredit.Text = 0
lblDesignCompDiv.Caption = ""
txtLibelle.Text = ""
Exit Sub
s: MsgBox "Vous voulez faire des opérations sur un journal
qui n'existe pas, veillez creer d'abord le journal de caisse", vbCritical,
"CSCompta"
End Sub
Private Sub Form_Load()
cboTypeOp.AddItem "Encaissement"
cboTypeOp.AddItem "Decaissement"
cboNumOp.Enabled = False
cboCompte.Enabled = False
txtLibelle.Enabled = False
txtDebit.Enabled = False
txtCredit.Enabled = False
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = App.Path & "\tfc1.mdb"
'connnection à la base des données
con.Mode = adModeReadWrite
con.Open 'ouverture de la connection
With td
.ActiveConnection = con
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open "select * from TcompteDivisionnaire ", con
Do Until td.EOF
cboCompte.AddItem (td("NumCompDiv"))
td.MoveNext
Loop
.Close
.Open "select * from Topération ", con
Do Until td.EOF
cboNumOp.AddItem (td("NumOp"))
.MoveNext
Loop
.Close
.Open "select * from TcompteDivisionnaire where
NumCompPrinc='57'", con
Do Until td.EOF
cboComptecaisse.AddItem (td("NumCompDiv"))
.MoveNext
Loop
.Close
.Open "Select CodMonnaie from Monnaie"
Do Until td.EOF
cboMonnaie.AddItem td("CodMonnaie")
.MoveNext
Loop
.Close
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
con.Close
End Sub
a. c. Journal des entrées
médicaments
'declaration des variables de connection
Dim con As New Connection
Dim td As New Recordset
Private Sub cboCodMat_Click()
td.ActiveConnection = con
td.Source = "select * from Tmedicament where CodMat= '" &
cboCodMat.Text & "'"
td.Open
txtDesignMat.Text = td("DesignMat") & " " & td("DosMat")
& " " & td("FormMat")
td.Close
End Sub
Private Sub cboModePaie_Click()
txtCompteCred.Text = cboModePaie.Text
'selection des comptes divisionnaires dans la table
TcompteDivisionnaire
td.Open " select * from TcompteDivisionnaire where NumCompDiv='"
& cboModePaie.Text & "' ", con
Do Until td.EOF
lblCompte.Caption = td("DesignCompDiv")
td.MoveNext
Loop
td.Close
End Sub
Private Sub cboNumOp_Click()
td.Open "select DateOp from Topération where NumOp='"
& cboNumOp & "'", con
Do Until td.EOF
lblDateOp.Caption = td("DateOp")
td.MoveNext
Loop
td.Close
End Sub
Private Sub cmdEnreg_Click()
If cboCodMat.Text = "" Or txtDesignMat.Text = "" Or
txtReference.Text = "" Or cboNumOp.Text = "" Or txtNomFour.Text = "" Or
txtQentre.Text = "" Or txtPrixUnitEntre.Text = "" Then
MsgBox "Vous devez remplir tous les champs", vbCritical,
"CSCompta, Verification"
Else
td.ActiveConnection = con
con.Execute "insert into
journalEntreSortieStock(NumOp,CodMat,QuantEntre,PrixUnit,Fournisseur,Reference)values
( '" & cboNumOp.Text & "','" & cboCodMat.Text & " ', ' " &
val(txtQentre.Text) & " ', '" & val(txtPrixUnitEntre.Text) & "','"
& txtNomFour.Text & "', ' " & txtReference.Text & "')"
txtPrixTotEntre.Text = val(txtQentre.Text) *
val(txtPrixUnitEntre.Text)
con.Execute "insert into Timputation
(NumOp,NumCompDiv,MontDeb,MontCred) values ('" & cboNumOp.Text & "','"
& cboCodMat.Text & "','" & txtPrixTotEntre.Text & "',0)"
con.Execute "insert into Timputation
(NumOp,NumCompDiv,MontDeb,MontCred) values ('" & cboNumOp.Text & "', '"
& txtCompteCred.Text & "', 0 ,'" & txtPrixTotEntre.Text &
"')"
td.Open
MsgBox "Enregistré avec succès", vbInformation,
"CSCompta"
td.Close
End If
cboCodMat.Text = ""
txtDesignMat.Text = ""
txtReference.Text = ""
cboNumOp.Text = ""
txtNomFour.Text = ""
txtQentre.Text = ""
txtPrixUnitEntre.Text = ""
End Sub
Private Sub Form_Load()
td.CursorType = adOpenDynamic
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = App.Path & "\tfc1.mdb"
'connnection à la base des données
con.Open 'ouverture de la connection
td.Open "select * from Tmedicament ", con
Do Until td.EOF
cboCodMat.AddItem (td("CodMat"))
td.MoveNext
Loop
td.Close
'inserer les numero d'operation dans cboNumOp
td.Open "select * from Topération", con
Do Until td.EOF
cboNumOp.AddItem (td("NumOp"))
td.MoveNext
Loop
td.Close
cboModePaie.Visible = False 'cacher le combo box
correspondant au mode de paie
txtPrixTotEntre.Visible = False
txtCompteCred.Visible = False
End Sub
Private Sub Form_Unload(Cancel As Integer)
con.Close
End Sub
Private Sub optModePaie_Click(Index As Integer)
If optModePaie(0) Then
cboModePaie.Clear
'inserer les comptes banque dans le combo box
td.Open " select * from TcompteDivisionnaire where
NumCompPrinc='56' ", con
Do Until td.EOF
cboModePaie.AddItem (td("NumCompDiv"))
td.MoveNext
Loop
td.Close
ElseIf optModePaie(1) Then
cboModePaie.Clear
'inserer les comptes caisse dans le combo box
td.Open " select * from TcompteDivisionnaire where
NumCompPrinc='57' ", con
Do Until td.EOF
cboModePaie.AddItem (td("NumCompDiv"))
td.MoveNext
Loop
td.Close
ElseIf optModePaie(2) Then
cboModePaie.Clear
'inserer les comptes fournisseur dans le combo box
td.Open " select * from TcompteDivisionnaire where
NumCompPrinc='40' ", con
Do Until td.EOF
cboModePaie.AddItem (td("NumCompDiv"))
td.MoveNext
Loop
td.Close
End If
cboModePaie.Visible = True
End Sub
d. Journal des sorties médicaments
'declaration des variables de connection
Dim con As New Connection
Dim td As New Recordset
Private Sub cboCodMat_Click()
td.ActiveConnection = con
td.Source = "select * from Tmedicament where CodMat= '" &
cboCodMat.Text & "' "
td.Open
txtDesignMat.Text = td("DesignMat") & " " & td("DosMat")
& " " & td("FormMat")
td.Close
'pour selectionner la quantité total entré pour le
produit correspondant au numero dans le combobox
td.ActiveConnection = con
td.Source = "select Reste from StockGlobal where CodMat= '" &
cboCodMat & "' "
td.Open
On Error GoTo s
txtTotQEntre = td("Reste")
td.Close
Exit Sub
s:
td.ActiveConnection = con
td.Close
td.Open "select SommeDeQuantEntre from StockGlobal where CodMat=
'" & cboCodMat & "'"
txtTotQEntre = td("SommeDeQuantEntre")
td.Close
End Sub
Private Sub cboNumOp_Click()
td.Open "select DateOp from Topération where NumOp='"
& cboNumOp & "'", con
Do Until td.EOF
lblDateOp.Caption = td("DateOp")
td.MoveNext
Loop
td.Close
End Sub
Private Sub cmdSortir_Click()
If cboCodMat.Text = "" Or cboNumOp.Text = "" Or txtQSort.Text =
"" Then
MsgBox "Vous devez remplir tous les champs", vbCritical,
"CSCompta"
Else
If txtQSort.Text > txtTotQEntre.Text Or txtQSort.Text <= 0
Then
MsgBox "Quantité supérieur par rapport à la
quantité disponible", vbInformation, "CSCompta"
Else
td.ActiveConnection = con
con.Execute "insert into
JournalEntreSortieStock(NumOp,CodMat,QuantSorti,Reference) values ('" &
cboNumOp.Text & "','" & cboCodMat.Text & "' ,'" &
val(txtQSort.Text) & " ','" & txtRef.Text & "') "
MsgBox "Opération effectué avec succès",
vbInformation, "CScompta"
td.Open
td.Requery
td.Close
End If
cboCodMat.Text = ""
cboNumOp.Text = ""
txtQSort.Text = ""
txtDesignMat.Text = ""
txtRef.Text = ""
txtTotQEntre.Text = ""
End If
End Sub
Private Sub Form_Load()
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = App.Path & "\tfc1.mdb"
'connnection à la base des données
con.Open
'ouverture de la connection
With td
.ActiveConnection = con
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
td.Open "select CodMat from
JournalEntréesEnStock group by CodMat", con
Do Until td.EOF
cboCodMat.AddItem (td("CodMat"))
td.MoveNext
Loop
td.Close
End With
'inserer les numero d'operation dans cboNumOp
td.Open "select * from Topération", con
Do Until td.EOF
cboNumOp.AddItem (td("NumOp"))
td.MoveNext
Loop
td.Close
End Sub
Private Sub Form_Unload(Cancel As Integer)
con.Close
End Sub
a. e. Journal de prévision
budgétaire
'declaration des variables de connection
Dim con As New Connection
Dim td As New Recordset
Private Sub cmdModMont_Click()
cmdModMont.Visible = False
Dim exerc As String
Dim compte As String
exerc = InputBox("Veillez saisir le code de l'exerice")
compte = InputBox("Veillez saisir le numero de compte ")
If exerc = "" Or compte = "" Then
MsgBox "Vous devez remplir les paramètres récquis",
vbCritical, "CSCompta"
cmdModMont.Visible = True
Else
On Error GoTo s
td.ActiveConnection = con
td.Open "select * from Tprevision where CodExercice='" &
exerc & "' and NumCompDiv='" & compte & "'"
cboCodExrcice.Text = td("CodExercice")
cboComptePrev.Text = td("NumCompDiv")
txtMontPrev.Text = td("MontPrev")
txtModifMont.Text = td("ModifMont")
td.Close
cboCodExrcice.Enabled = False
txtMontPrev.Enabled = False
cboComptePrev.Enabled = False
End If
Exit Sub
s: MsgBox "Ce compte n'a jamais été prévu
pour cet execice, prevoyez le d'abord", vbExclamation, "CSCompta"
End Sub
Private Sub cmdPrevoir_Click()
If cboCodExrcice.Text = "" Or cboComptePrev.Text = "" Or
txtMontPrev.Text = "" Or txtModifMont.Text = "" Then
MsgBox "Vous devez remplir tous les champs", vbCritical,
"CSCompta"
Else
On Error GoTo s
con.Execute "insert into
Tprevision(NumcompDiv,CodExercice,MontPrev,ModifMont) values ('" &
cboComptePrev.Text & "','" & cboCodExrcice.Text & "','" &
val(txtMontPrev.Text) & "','" & val(txtModifMont.Text) & "')"
cboCodExrcice.Text = ""
cboComptePrev.Text = ""
txtMontPrev.Text = ""
txtModifMont.Text = ""
Exit Sub
s: MsgBox "Modification non effectué, risque de
doublons, ce compte est déja prévu pour cet exercice",
vbCritical, "CSCompta"
End If
End Sub
Private Sub cmdValider_Click()
td.ActiveConnection = con
td.Open "update Tprevision set ModifMont='" &
val(txtModifMont.Text) & "' where CodExercice='" & cboCodExrcice.Text
& "' and NumCompDiv='" & cboComptePrev.Text & "'"
cmdModMont.Visible = True
cboCodExrcice.Text = ""
cboComptePrev.Text = ""
txtMontPrev.Text = ""
txtModifMont.Text = ""
MsgBox "Opération effectuée avec succès",
vbInformation, "CSCompta"
End Sub
Private Sub Form_Load()
td.CursorType = adOpenDynamic
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = App.Path & "\tfc1.mdb"
'connnection à la base des données
con.Open 'ouverture de la connection
td.Open "select * from Texercice", con
Do Until td.EOF
cboCodExrcice.AddItem (td("CodExercice"))
td.MoveNext
Loop
td.Close
' selection des comptes et leur mise dans combo box
td.Open "select * from TcompteDivisionnaire", con
Do Until td.EOF
cboComptePrev.AddItem (td("NumCompDiv"))
td.MoveNext
Loop
td.Close
End Sub
Private Sub Form_Unload(Cancel As Integer)
con.Close
End Sub
3. Le sous menu Impressions des informations
Ce menu est constitué de la rétroaction des
toutes les informations enregistrées dans la base des données.
Ces états ont été réalisés grâce
à CRYSTAL REPORT PRO 4.6
Ci-dessous quelques unes des ces états :
a. Tableau de prévision budgétaire
b. Etat d'exécution du budget
c. La fiche de stock
d. Le bilan de l'exercice
` Code pour la selection des informations des informations dans
la base de données sur base des critères de selection
Dim filtre1 As String
filtre1 = InputBox("Entrez le Code de l'exercice", "CSCompta")
bilanCompPrinc.ReportFileName = App.Path & "\bilan compte
principaux.rpt"
bilanCompPrinc.ParameterFields(0) = "exercice;" & filtre1
& ";TRUE"
bilanCompPrinc.Destination = crptToWindow
bilanCompPrinc.PrintReport
| 
