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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
| Option Compare Database
Dim rsClient As DAO.Recordset
Dim rsReservation As DAO.Recordset
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sallechoisie As String
Dim s As String
Dim d1 As Date
Dim d2 As Date
Private Sub cbo_choix_AfterUpdate()
Me.cbo_choix.Requery
End Sub
Private Sub cbo_choix_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct * from CLIENT where code_cl like'" & Me.cbo_choix & "'")
If rs.Fields!code_cl = Me.cbo_choix Then
Me.code_cl = rs.Fields!code_cl
Me.nom_prenom = rs.Fields!nom_prenom
Me.tel1 = rs.Fields!tel1
Me.tel2 = rs.Fields!tel2
Me.email = rs.Fields!email
End If
Me.cbo_choix.Requery
End Sub
Private Sub CmdAjouter_Click()
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Then
ctrl.Value = ""
End If
Next ctrl
End Sub
Private Sub CmdEnreg_Click()
MsgBox "voulez vous enregistrer "
'Verifier q'un client existe ou pas dans la base de données
Set db = CurrentDb
'Set rqClient = db.OpenRecordset("select distinct * from CLIENT where code_cl ='" & Me.code_cl & "'")
Set rsClient = db.OpenRecordset("CLIENT")
rech = Nz(DLookup("code_cl", "CLIENT", code_cl = " & cbo_choix &"))
'si le client existe
If rsClient!code_cl = " & cbo_choix & " Then
With rs.FindFirst
Me.code_cl = rsClient!code_cl
Me.nom_prenom = rsClient!nom_prenom
Me.tel1 = rsClient!tel1
Me.tel2 = rsClient!tel2
Me.email = rsClient!email
rsClient.Update
rsClient.Close
Else
's'il n'existe pas on le crée
'If (IsNull(Forms.CLIENT.Controls("code_cl"))) Then
'MsgBox "veuillez entrer le code du client"
'Exit Sub
'End If
'If (IsNull(Forms.CLIENT.Controls("nom_prenom"))) Then
'MsgBox "veuillez entrer le nom du client"
'Exit Sub
'End If
rsClient.AddNew
'ajouter un client
rsClient!code_cl = Forms.CLIENT.Controls("code_cl")
rsClient!nom_prenom = Forms.CLIENT.Controls("nom_prenom")
rsClient!tel1 = Forms.CLIENT.Controls("tel1")
rsClient!tel2 = Forms.CLIENT.Controls("tel2")
rsClient!email = Forms.CLIENT.Controls("email")
rsClient.Update
rsClient.Close
End If
While rsRservation.EOF
'Enregistrement d'une ou des réservations du client après des vérifications d'usage ou contraintes
'vérifie si une salle n'est pas sélectionnée par l'utilisateur
If (IsNull(Forms!CLIENT.sfReservationSalleClient.Form.Controls("code_sal"))) Then
MsgBox ("Veuillez sélectionner une salle")
Exit Sub
End If
'vérifie si une date de début n'est pas entrée par l'utilisateur
If (IsNull(Forms!CLIENT.sfReservationSalleClient.Form.Controls("date_debut"))) Then
MsgBox ("Veuillez entrer une date de debut")
Exit Sub
End If
'vérifie si une date de fin n'est pas entrée par l'utilisateur
If (IsNull(Forms!CLIENT.sfReservationSalleClient.Form.Controls("date_fin"))) Then
MsgBox ("Veuillez entrer une date de fin")
Exit Sub
End If
'vérifier si la salle est disponible aux dates demandées
s = Forms!CLIENT.sfReservationSalleClient.Form.Controls("code_sal n")
d1 = Forms!CLIENT.sfReservationSalleClient.Form.Controls("date_debut")
d2 = Forms!CLIENT.sfReservationSalleClient.Form.Controls("date_fin")
If salledisponible(s, d1, d2) = True And Forms.CLIENT.Controls("code_cl") <> "" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "insert into RESERVATION(RESERVATION.code_cl,RESERVATION.code_sal_,RESERVATION.objet_res,RESERVATION.date_debut,RESERVATION.date_fin,RESERVATION.heure_debut,RESERVATION.heure_fin,RESERVATION.statut " & _
"Values(" & Forms.CLIENT.Controls("code_cl") & ", " & sallechoisie & ", " & Forms.CLIENT.sfRESERVATION.Form.Controls("objet_res") & ",#" & (Format(d1, "mm/dd/yyyy")) & "#, #" & (Format(d2, "mm/dd/yyyy")) & "#, " & Forms.CLIENT.sfRESERVATION.Form.Controls("heure_debut") & ", " & _
Forms.CLIENT.sfRESERVATION.Form.Controls("heure_fin") & ", " & Forms.CLIENT.sfRESERVATION.Form.Controls("statut") & ")"
' "Where RESERVATION.code_cl = '" & vcodecl & "'"
DoCmd.SetWarnings True
DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "sfRESERVATION", acNormal, , , acFormAdd
Else
'ouvrir le formulaire liste des salle diponible
End If
rsReservation.MoveNext
Wend
rsClient.Update
rsClient.Close
db.Close
Set rsClient = Nothing
Set db = Nothing
End Sub
Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select code_cl from CLIENT")
rs.MoveFirst
While Not rs.EOF
Me.cbo_choix.AddItem rs.Fields!code_cl
rs.MoveNext
Wend
rs.Close
End Sub
Public Function salledisponible(sallechoisie As String, datedebutdemandee As Date, datefindemandee As Date) As Boolean
Dim db1 As DAO.Database, rs1 As DAO.Recordset
Dim sql As String
On Error GoTo ErrorHandler
Set db1 = CurrentDb
sql = "select * from RESERVATION where (code_sal =" & sallechoisie & ") "
Set rs1 = db1.OpenRecordset(sql, dbOpenDynaset)
'si il n'y a pas d'enregistrement
If rs1.RecordCount = 0 Then
salledisponible = False
Exit Function
End If
With rs1
Do Until .EOF
If (rs1!date_debut Is "between (#" & (Format(datedebutdemandee, "mm/dd/yyyy")) & "#) And (#" & (Format(datefindemandee, "mm/dd/yyyy")) & "#)") Then
If (rs1!date_fin Is "between (#" & (Format(datedebutdemandee, "mm/dd/yyyy")) & "# )And( #" & (Format(datefindemandee, "mm/dd/yyyy")) & "#)") Then
If (rs1!date_debut < ("#" & (Format(datedebutdemandee, "mm/dd/yyyy")) & "#")) And (rs1!date_debut > ("#" & (Format(datefindemandee, "mm/dd/yyyy")) & "#")) Then
salledisponible = False
MsgBox "la salle est déja réservée à cette période"
GoTo Exit_0
Else
salledisponible = True
End If
rs1.Update
rs1.MoveNext
Loop
End With
rs1.Close
db1.Close
Exit_0:
Set rs1 = Nothing
Set db1 = Nothing
Exit Function
Exit Function
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
salledisponible = False
End Function |
Partager