POkusao sam da postavim kontrolu JMBG na nivo tabele i uspeo sam sledece:
1. KOntrolna cifra za JMBG se kontrolise u Validation Rule za kolonu
2. U Validation Rule na nivou tabele kontrolise se datumski deo i deo koji se odnosi na pol osobe
Zakaceni primer ima jednu tabelu koja dozvoljava NULL vrednosti u koloni JMBG, ali ne i duplikate (UNique index). Ako se JMBG unese, onda mora imati dobru kontrolnu cifru i podaci o datumu rodjejna i polu osobe moraju se slagati.
Evo i korisna funkcija pomocu koje generisete slucajni JMBG, koji mozete korsititi u tetsiranju.
Code:
Function Random_JMBG(datDatumRodjenja As Date, strPol As String, Optional intRegion As Integer = 0) As String
'---------------------------------------------------------------------------------------
' Procedure : Random_JMBG
' Author : Zidar
' Date : 27-Nov-2012
' Purpose : generise JMBG za testiranje
' Uslovi : strPol je ili "M" ili "Z"
' datDatumRodjenja je izmedju 1 Jan 2015 i 31 Dec 2016
' Iz Wikipedia:
' The number is made up of 13 digits in a form "DD MM YYY RR BBB K" (whitespaces are for convenience; digits are written without separation) where:
'
' DD – day of birth
' MM – month of birth
' YYY – last three digits of the year of birth
' RR – political region [8] of birth (for persons born before 1976, political region where they were first registered)
' BBB - gender, 0-499 for Female, 500-999 for male
' K - check digit = f (DD,MM,YYY,RR,BBB)
' Example: Random_JMBG (#05/25/1892#,"M",39) - musko dete rodjeno u hrvatskom Zagorju 25 O5 1892
'---------------------------------------------------------------------------------------
'
Dim JMBG As String
Dim CheckDigit As Integer
Dim M As Integer
Dim K As Integer
On Error GoTo Random_JMBG_Error
Randomize
If Not (strPol = "M" Or strPol = "Z") Then
Random_JMBG = ""
MsgBox "POgresno unesen POL, prihvataju se samo vrednosti M ili Z!"
Exit Function
End If
If datDatumRodjenja < #1/1/1850# Or datDatumRodjenja > #12/31/2099# Then
Random_JMBG = ""
MsgBox "POgresno unesen DatumRodjenja, prihvataju se samo vrednosti izmedju 1 Jan 1850 i 31 Dec 2099!"
Exit Function
End If
'deo DD MM YYY
JMBG = Format(Day(datDatumRodjenja), "00")
JMBG = JMBG & Format(Month(datDatumRodjenja), "00")
JMBG = JMBG & Right(Format(Year(datDatumRodjenja), "0000"), 3)
'Political region RR, randoly selected in range 10-19.
If intRegion = 0 Then
intRegion = 10 + Int((9 * Rnd) + 0)
End If
JMBG = JMBG & Format(intRegion, "00")
'I will choose Sarajevo, for tetsing purposes
'deo BBB
If strPol = "Z" Then
JMBG = JMBG & Format(Int((499 * Rnd) + 0), "000")
Else
JMBG = JMBG & Format(500 + Int((499 * Rnd) + 0), "000")
End If
Random_JMBG = JMBG
M = 11 - (7 * (Val(Mid([JMBG], 1, 1)) + Val(Mid([JMBG], 7, 1))) _
+ 6 * (Val(Mid([JMBG], 2, 1)) + Val(Mid([JMBG], 8, 1))) _
+ 5 * (Val(Mid([JMBG], 3, 1)) + Val(Mid([JMBG], 9, 1))) _
+ 4 * (Val(Mid([JMBG], 4, 1)) + Val(Mid([JMBG], 10, 1))) _
+ 3 * (Val(Mid([JMBG], 5, 1)) + Val(Mid([JMBG], 11, 1))) _
+ 2 * (Val(Mid([JMBG], 6, 1)) + Val(Mid([JMBG], 12, 1)))) Mod 11
If M >= 1 And M <= 9 Then
K = M
JMBG = JMBG & Format(K, "0")
Random_JMBG = JMBG
Exit Function
End If
If M = 1 Then
K = 0
JMBG = JMBG & Format(K, "0")
Random_JMBG = JMBG
Exit Function
End If
If M = 10 Then
Random_JMBG = ""
MsgBox "Problem! Izracunata kontrolna cifra = 10, sto ne sme da se desi! Greska je negde u ulaznim podacima!"
Exit Function
End If
EXIT_HERE:
On Error Resume Next
Exit Function
Random_JMBG_Error:
Select Case Err
Case Else
MsgBox "Error " & Err.Number _
& " (" & Err.Description _
& ") in procedure Random_JMBG of Module Module1"
End Select
Resume EXIT_HERE
End Function
Re: Access baza "Održavanje zgrade"
