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
| Sub MajCodesPostaux()
Dim vDb As DAO.Database, vRst As DAO.Recordset, vSql As String, vCpUnique As String
Set vDb = CurrentDb()
Set vRst = vDb.OpenRecordset("Patient", dbOpenSnapShot)
With vRst
If Not .EOF Then
.MoveFirst
DoCmd.SetWarnings False 'désactive les messages d'alertes
Do
If uniciteCodePostal(!IdPatient, vCpUnique) Then
vDb.Execute "UPDATE T SET [Code Postal]='" & vCpUnique & "' WHERE IdPatient=" & !IdPatient & ";"
End If
vCpUnique = ""
.MoveNext
Loop Until .EOF
End If
.Close
End With
Set vRst = Nothing
DoCmd.SetWarnings True
End Sub
Private Function uniciteCodePostal(ByVal pIdPatient As Long, ByRef pCpRetour As String) As Boolean
'renvoie True si un seul code postal est trouvé dans la table T, le code postal unique est renvoyé dans pCpRetour
Dim vRst As DAO.Recordset, vSql As String
vSql = "SELECT DISTINCT [Code Postal] FROM T WHERE IdPatient=" & pIdPatient & " AND [Code Postal]<>999 AND [Code Postal] IS NOT NULL;"
Set vRst = CurrentDb.OpenRecordset(vSql, dbOpenSnapshot)
With vRst
If Not .EOF Then
.MoveLast
If .RecordCount = 1 Then
pCpRetour = ![Code Postal]
uniciteCodePostal = True
End If
End If
.Close
End With
Set Vrst = Nothing
End Function |
Partager