'----------------------------------------------------------
'Les recettes du jour
'----------------------------------------------------------
Option Compare Database
Option Explicit
Private Sub Form_Load()
IDFabrication.SetFocus
End Sub
'----------------------------------------------------------
'Les recettes du jour - Ajout
'----------------------------------------------------------
Private Sub FabricationAjouter_Click()
Dim theDataBase As DAO.Database, Sqlresult As DAO.Recordset
Dim Style As Integer, Insulte As String, Titre As String, Reponse As String
Dim r As String, theNomComposant As String, theIDFabrication As Integer
Dim theDateFabrication As String, MaxIdFabrication As Integer
Dim insert As String
Dim theKount As Integer, theComposantNo As Integer, theDateDebutUtil As Date, theNomLot As String
'-------------------------------------------------------------------
' Table T_Fabrications : on vérifie que IDFabrication est renseigné
'-------------------------------------------------------------------
Set theDataBase = CurrentDb
On Error Resume Next
If Not DeterminerFabrication Then
Exit Sub
End If
theIDFabrication = Me.IDFabrication
theDateFabrication = Me.DateFabrication
'-------------------------------------------------------------------------
' Contrôle d'existence du composant
'-------------------------------------------------------------------------
If IsNull(Me.ComposantCombo.Column(1)) Or Len(Me.ComposantCombo.Column(1)) = 0 Then
Style = vbExclamation
Insulte = "Le champ Composant doit contenir un composant existant ! "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
ComposantCombo.SetFocus
Exit Sub
End If
'-----------------------------------------------------------------------------------
' On vérifie que le composant comporte au moins un lot
'-----------------------------------------------------------------------------------
theComposantNo = Me.ComposantCombo.Column(0)
r = "SELECT count(*) as Kount FROM T_Lots where IDComposant = " & theComposantNo & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("Kount").Value
If theKount = 0 Then
Style = vbExclamation
Insulte = "Le composant " & theComposantNo & " n’a pas de lot. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
ComposantCombo.SetFocus
Exit Sub
End If
'-------------------------------------------------------------------------------------
'Pour le composant en cours theComposantNo, on détermine le lot correspondant
'à la date de fabrication theDateFabrication,
'ainsi que la date de début d'utilisation de ce lot.
'-------------------------------------------------------------------------------------
r = "SELECT Max(x.DateDebutUtilisation) AS theDateDebutUtil, Max(x.NomLot) AS theNomLot " _
& " FROM T_Lots AS x, TLC_Composants AS y " _
& " WHERE y.IDComposant = " & theComposantNo & " " _
& " AND x.IDComposant = y.IDComposant " _
& " AND x.DateDebutUtilisation <= CDate('" & theDateFabrication & "') " _
& " GROUP BY x.IDComposant ; "
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theDateDebutUtil = Sqlresult.Fields("theDateDebutUtil").Value
theNomLot = Sqlresult.Fields("theNomLot").Value
'---------------------------------------------------------------------------------------
'On vérifie que le triplet <composant, date de fabrication, lot> n'est pas déjà présent
'dans la jointure T_Fabrications, T_Fabrication_Composants
'---------------------------------------------------------------------------------------
r = "select count(*) as k from T_Fabrications as x, T_Fabrication_Composants as y " _
& "where x.IDFabrication = y.IDFabrication " _
& " and Datefabrication = Cdate('" & theDateFabrication & "') " _
& " and Lotducomposantdujour = '" & theNomLot & "' AND IDComposant = " & theComposantNo & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("k").Value
'Si le triplet <composant, date de fabrication, lot> n'existe pas déjà, on crée un tuple fabrication
If theKount = 0 Then
insert = "insert into T_Fabrication_Composants (IDFabrication, Lotducomposantdujour, IDComposant) VALUES (" _
& "'" & theIDFabrication & "', '" & theNomLot & "', " & theComposantNo & ") ;"
theDataBase.Execute insert
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit.."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
'on rafraîchit le sous-formulaire
Me.Recalc
Else
r = "select NomComposant from TLC_Composants " _
& "where IDComposant = " & theComposantNo & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theNomComposant = Sqlresult.Fields("NomComposant").Value
Style = vbExclamation
Insulte = "Il existe déjà un triplet : " & Chr(13) & Chr(13) & "DateFabrication = '" & theDateFabrication & "', " & Chr(13) _
& "Composant = '" & theNomComposant & "'," & Chr(13) & "Lot = ' " & theNomLot & "'."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
theDataBase.Close: Set theDataBase = Nothing
End Sub
Private Sub IDFabrication_LostFocus()
'-----------------------------------------------------------------
' détermination de la date de fabrication
'-----------------------------------------------------------------
Dim theDataBase As DAO.Database, Sqlresult As DAO.Recordset
Dim Style As Integer, Insulte As String, Titre As String, Reponse As String
Dim r As String, theComposant As String, theIDFabrication As Integer
Dim theDateFabrication As String, MaxIdFabrication As Integer
Dim insert As String
Dim theKount As Integer, theComposantNo As Integer, theDateDebutUtil As Date, theNomLot As String
If IsNull(Me.IDFabrication) Then
Style = vbCritical
Insulte = "Veuillez renseigner IDFabrication."
Titre = "Formulaire " & Me.Name
Exit Sub
End If
theIDFabrication = Me.IDFabrication
'-------------------------------------------------------------------------------
' On vérifie que IDFabrication est présent dans la table T_Fabrications
'-------------------------------------------------------------------------------
Set theDataBase = CurrentDb
On Error Resume Next
r = "SELECT COUNT(*) AS Kount FROM T_Fabrications where IDFabrication = " & theIDFabrication & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("Kount").Value
If theKount = 0 Then
Style = vbCritical
Insulte = "L’identifiant IDFabrication = " & theIDFabrication & " n’existe pas dans la table T_Fabrications..."
Titre = "Formulaire " & Me.Name
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
r = "SELECT DateFabrication FROM T_Fabrications where IDFabrication = " & theIDFabrication & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theDateFabrication = Sqlresult.Fields("DateFabrication").Value
Me.DateFabrication = theDateFabrication
End Sub
'-------------------------------------------------------------
' suppression d'une ligne de la table TLC_Composant
'-------------------------------------------------------------
Private Sub FabricationSupprimer_Click()
Dim theDataBase As DAO.Database, Sqlresult As DAO.Recordset
Dim Style As Integer, Insulte As String, Titre As String, Reponse As String
Dim r As String, theNomComposant As String, theIDFabrication As Integer
Dim theDateFabrication As String, MaxIdFabrication As Integer, theTableId As Integer
Dim theKount As Integer, theComposantNo As Integer, theDateDebutUtil As Date, theNomLot As String
Dim Delete As String
Set theDataBase = CurrentDb
On Error Resume Next
'-------------------------------------------------------------------------
' On demande à Cézigue la valeur de l'identifiant de la ligne à supprimer
'-------------------------------------------------------------------------
theTableId = InputBox("Veuillez fournir la valeur de l'identifiant de la ligne à supprimer :")
'-------------------------------------
' Contrôle de structure l'identifiant
'-------------------------------------
If theTableId = 0 Then
Style = vbCritical
Insulte = "Identifiant de la ligne à supprimer : Valeur non conforme. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
'-------------------------------------------------
' Vérification de l'existence de l'identifiant
'-------------------------------------------------
r = "select count(*) as kount from T_Fabrication_Composants where ID_F_C = " & theTableId & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("kount").Value
If theKount = 0 Then
Style = vbCritical
Insulte = "Ligne à supprimer : l'identifiant '" & theTableId & "', n'existe pas. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
'----------------------------------------
' Suppression de la ligne dans la table
'----------------------------------------
Delete = "delete from T_Fabrication_Composants where ID_F_C = " & theTableId & " ;"
theDataBase.Execute Delete
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit.."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
'on rafraîchit le sous-formulaire
Me.Recalc
theDataBase.Close: Set theDataBase = Nothing
End Sub
'=============================================
'
' Changement de composant
'
'=============================================
Private Sub FabricationModifier_Click()
Dim theDataBase As DAO.Database, Sqlresult As DAO.Recordset
Dim Style As Integer, Insulte As String, Titre As String, Reponse As String
Dim r As String, theNomComposant As String, theIDFabrication As Integer
Dim theDateFabrication As String, MaxIdFabrication As Integer, theTableId As Integer
Dim theKount As Integer, theComposantNo As Integer, theDateDebutUtil As Date, theNomLot As String
Dim theUpdate As String
Me.Recalc
If Not DeterminerFabrication Then
Exit Sub
End If
theIDFabrication = Me.IDFabrication
theDateFabrication = Me.DateFabrication
Set theDataBase = CurrentDb
On Error Resume Next
'-------------------------------------------------------------------------
' On demande à Cézigue la valeur de l'identifiant de la ligne à modifier
'-------------------------------------------------------------------------
theTableId = InputBox("Veuillez fournir la valeur de l'identifiant de la ligne à modifier :")
'-------------------------------------
' Contrôle de structure l'identifiant
'-------------------------------------
If theTableId = 0 Then
Style = vbCritical
Insulte = "Identifiant de la ligne à modifier : valeur non conforme. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
'-------------------------------------------------
' Vérification de l'existence de l'identifiant
'-------------------------------------------------
r = "select count(*) as kount from T_Fabrication_Composants where ID_F_C = " & theTableId & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("kount").Value
If theKount = 0 Then
Style = vbCritical
Insulte = "Ligne à modifier : l'identifiant '" & theTableId & "', n'existe pas. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
'-------------------------------------------------------------------------
' On demande à Cézigue la valeur du nouveau composant
'-------------------------------------------------------------------------
theNomComposant = InputBox("Veuillez fournir la valeur du nouveau composant :")
'-------------------------------------
' Contrôle de structure du composant
'-------------------------------------
If IsNull(theNomComposant) Or Len(theNomComposant) = 0 Then
Style = vbCritical
Insulte = "Nouveau composant : valeur non conforme. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
'-------------------------------------------------
' Vérification de l'existence du composant
'-------------------------------------------------
r = "select count(*) as kount from TLC_Composants where NomComposant = '" & theNomComposant & "' ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("kount").Value
If theKount = 0 Then
Style = vbCritical
Insulte = "Ligne à modifier : le composant '" & theNomComposant & "' est inconnu. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
'-------------------------------------------------------------------
' Récupération de l'identifiant du composant à partir de son nom
'-------------------------------------------------------------------
r = "select IDComposant from TLC_Composants where NomComposant = '" & theNomComposant & "' ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theComposantNo = Sqlresult.Fields("IDComposant").Value
'-----------------------------------------------------
' Le composant doit comporter au moins un lot
'-----------------------------------------------------
r = "select count(*) as kount from TLC_Composants as x, T_Lots as y " _
& "where x.IDComposant = y.IDComposant and NomComposant = '" & theNomComposant & "' ;"
r = "select count(*) as kount from TLC_Composants as x, T_Lots as y " _
& "where x.IDComposant = y.IDComposant and x.IDComposant = " & theComposantNo & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theKount = Sqlresult.Fields("kount").Value
If theKount = 0 Then
Style = vbCritical
Insulte = "Ligne à modifier : le composant '" & theNomComposant & "' ne comporte aucun lot. "
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
Exit Sub
End If
'-------------------------------------------------------------------------------------
'Pour le composant de remplacement theComposantNo, on détermine le lot correspondant
'à la date de fabrication theDateFabrication,
'ainsi que la date de début d'utilisation de ce lot.
'-------------------------------------------------------------------------------------
On Error Resume Next
r = "SELECT Max(x.DateDebutUtilisation) AS theDateDebutUtil, Max(x.NomLot) AS theNomLot " _
& " FROM T_Lots AS x, TLC_Composants AS y " _
& " WHERE y.IDComposant = " & theComposantNo & " " _
& " AND x.IDComposant = y.IDComposant " _
& " AND x.DateDebutUtilisation <= CDate('" & theDateFabrication & "') " _
& " GROUP BY x.IDComposant ; "
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
theDateDebutUtil = Sqlresult.Fields("theDateDebutUtil").Value
theNomLot = Sqlresult.Fields("theNomLot").Value
'-----------------------------------------------------------------------------
' On met à jour la ligne concernée dans la table T_Fabrication_Composants
'-----------------------------------------------------------------------------
On Error Resume Next
theUpdate = "update T_Fabrication_Composants SET " _
& "IDComposant = " & theComposantNo _
& ", LotduComposantdujour = '" & theNomLot & "' " _
& "where ID_F_C = " & theTableId & " ;"
theDataBase.Execute theUpdate
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit.."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Sub
End If
'on rafraîchit le sous-formulaire
Me.Recalc
theDataBase.Close: Set theDataBase = Nothing
End Sub
'-----------------------------------------------------------------
' Cézigue en a terminé. On ferme la boutique.
'-----------------------------------------------------------------
Private Sub Quitter_Click()
DoCmd.Close
End Sub
'-----------------------------------------------------------------------
'
' traitements et contrôles portant sur la table T_Fabrications
'
'-----------------------------------------------------------------------
Function DeterminerFabrication()
Dim theDataBase As DAO.Database, Sqlresult As DAO.Recordset
Dim Style As Integer, Insulte As String, Titre As String, Reponse As String
Dim r As String, theNomComposant As String, theIDFabrication As Integer
Dim theDateFabrication As String, MaxIdFabrication As Integer, theTableId As Integer
Dim theKount As Integer, theComposantNo As Integer, theDateDebutUtil As Date, theNomLot As String
Dim Delete As String
'-------------------------------------------------------------------
' Table T_Fabrications : on vérifie que IDFabrication est renseigné
'-------------------------------------------------------------------
DeterminerFabrication = False
Set theDataBase = CurrentDb
On Error Resume Next
If IsNull(Me.IDFabrication) Then
Style = vbCritical
Insulte = "Veuillez renseigner le champ IDFabrication."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Function
End If
theIDFabrication = Me.IDFabrication
'-------------------------------------------------------------------------------
' On vérifie que IDFabrication est présent dans la table T_Fabrications
'-------------------------------------------------------------------------------
r = "SELECT COUNT(*) AS Kount FROM T_Fabrications where IDFabrication = " & theIDFabrication & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Function
End If
theKount = Sqlresult.Fields("Kount").Value
If theKount = 0 Then
Style = vbCritical
Insulte = "L’identifiant IDFabrication = " & theIDFabrication & " n’existe pas dans la table T_Fabrications..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Function
End If
'-----------------------------------------------------------------
' détermination de la date de fabrication
'-----------------------------------------------------------------
r = "SELECT DateFabrication FROM T_Fabrications where IDFabrication = " & theIDFabrication & " ;"
Set Sqlresult = theDataBase.OpenRecordset(r, dbOpenDynaset)
If Err.Number <> 0 Then
Style = vbCritical
Insulte = r & Chr(13) & Chr(13) & "Erreur " & Err.Number & ". " & Err.Description & Chr(13) & Chr(13) & " Prévenir qui de droit..."
Titre = "Formulaire " & Me.Name
Reponse = MsgBox(Insulte, Style, Titre)
theDataBase.Close: Set theDataBase = Nothing
Exit Function
End If
theDateFabrication = Sqlresult.Fields("DateFabrication").Value
Me.DateFabrication = theDateFabrication
DeterminerFabrication = True
End Function
Partager