Na nivou tabele ne moze se nista uraditi za UPDATE. Mora na formi, ne na AfterUpdate() jer je kasno, nego na Form_BeforeUpdate().
Recimo da imas u tabeli kolone UserCreated, DateCreated, UserUpdated, DateUpdated, moses ovako da postavis:
Na samoj tabeli, za UserCreated i DateCreated stavis DEFAULT vrednosti, Username() i Now(). Onda mozes ta dva polja da zaboravi. ne trebaju ti ni u jednoj formi, to ce tabela sama da belezi, kao sto verovatno znas i sam.
Za promenu rekorda, treba ti neka forma preko koje se rekord menja. Forma ima Before_Update event. Tu se ubace dve linije koda:
UserUpdated = username()
DateUpdated = Now()
Form_BeforeUpdate je poslednji dogadja pred snimanje promena i sta tu uradis ide u rekord, nema nicega izmedju gde se moze to promeniti.
Problem sa funkcijom UserName je sto ce vratiti nasto kao "Administrator", ako nije postavljena Access security.
Ja zato koristim funkciju koja vraca ime pod kojim s elogujes na kompjuter:
Code:
'******************** Code Start **************************
' API: Get Login name
' Author (s)
'Dev Ashish
'
'
'(Q) How do I retrieve the UserName with which the user is logged into the network?
'
'(A) Paste the following code in a new module and call the function fOSUserName.
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
'******************** Code End **************************
fOSUserName se na zalost ne moze koristiti u dizajnu tabele, kao Default value, pa i ona mora u BeforeUpdate na formu. Sada bi cela prica izgledala nekako ovako:
Code:
Sub MyForm_beforeUpdate(cancel as integer)
If me.NewRecord then
UserCReated = fOSUserName ()
DateCreated = Now()
Else
UserUpdated = fOSUserName ()
DateUpdated = Now()
End If
End sub
Pretpostavka je da korisnici nece ili ne mogu da menjaju podatke direktno u tabelama, nego samo kroz forme. To nije moguce 100% garantovati, ali se u razumnim uslovima moze lepo raditi preko formi i na BeforeUpdate. Ako korisnik ume i hoce da napravi lom sa podacima, onda nema odbrane, osim prelazak na neki 'veliki' SQL sistem.