Hi,
I usely define a class module for each important data table, and then:
define a property for all fields and needed calculated fields, define methods for all the things this class can do (there is always a LoadX) method.
A simple example: the class UserInfo
Code:
Option Compare Database
Option Explicit
Dim lngUserID As Long
Dim lngUserLevel As Long
Dim strUserName As String
Dim strUserLogin As String
Dim strUserPassword As String
Public Property Get UserID() As Variant
On Error GoTo Err_GetUID
UserID = lngUserID
Exit_GetUID:
Exit Property
Err_GetUID:
UserID = 0
End Property
Public Property Let UserID(ByVal vNewValue As Variant)
On Error GoTo Err_LetUID
lngUserID = Nz(vNewValue, 0)
Exit_LetUID:
Exit Property
Err_LetUID:
lngUserID = 0
End Property
Public Property Get UserLevel() As Variant
On Error GoTo Err_Getlevel
UserLevel = lngUserLevel
Exit_Getlevel:
Exit Property
Err_Getlevel:
UserLevel = 0
End Property
Public Property Let UserLevel(ByVal vNewValue As Variant)
On Error GoTo Err_Letlevel
lngUserLevel = Nz(vNewValue, 0)
Exit_LetLevel:
Exit Property
Err_Letlevel:
lngUserLevel = 0
End Property
Public Property Get UserName() As Variant
On Error GoTo Err_GetName
UserName = strUserName
Exit_GetName:
Exit Property
Err_GetName:
UserName = "?"
End Property
Public Property Let UserName(ByVal vNewValue As Variant)
On Error GoTo Err_LetName
strUserName = Nz(vNewValue, "?")
Exit_LetName:
Exit Property
Err_LetName:
strUserName = "?"
End Property
Public Property Get UserPassword() As Variant
On Error GoTo Err_GetPW
UserPassword = strUserPassword
Exit_GetPW:
Exit Property
Err_GetPW:
UserPassword = "ERROR"
End Property
Public Property Let UserPassword(ByVal vNewValue As Variant)
On Error GoTo Err_LetPW
strUserPassword = Nz(vNewValue, "ERROR")
Exit_LetPW:
Exit Property
Err_LetPW:
strUserPassword = "ERROR"
End Property
Public Property Get UserLogin() As Variant
On Error GoTo Err_GetLogin
UserLogin = strUserLogin
Exit_GetLogin:
Exit Property
Err_GetLogin:
UserLogin = "ERROR"
End Property
Public Property Let UserLogin(ByVal vNewValue As Variant)
On Error GoTo Err_LetLogin
strUserLogin = Nz(vNewValue, "ERROR")
Exit_LetLogin:
Exit Property
Err_LetLogin:
strUserLogin = "ERROR"
End Property
Public Sub LoadUser()
On Error GoTo Err_LoadUser
Dim rst As New ADODB.Recordset
Dim rstUser As New ADODB.Recordset
rst.Open "tsysCurrentUser", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
With rst
If .BOF And .EOF Then
intError = cerrNoRecords
Err.Raise 64000
End If
.MoveFirst
Me.UserID = !cuUserID
.Close
End With
Set rst = Nothing
rstUser.Open "select * from tsysUsers where userID = " & Me.UserID, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
With rstUser
If .BOF And .EOF Then
intError = cerrNoRecords
Err.Raise 64000
End If
.MoveFirst
Me.UserLevel = !UserLevel
Me.UserLogin = !UserLogin
Me.UserName = !UserName
Me.UserPassword = !UserPassword
.Close
End With
Set rstUser = Nothing
Exit_LoadUser:
Exit Sub
Err_LoadUser:
Call gsgErrorHandling
Resume Exit_LoadUser
End Sub
You could for example add a method ChangeUserPasword to this class.
To use the class in another module
Code:
dim objUser as New UserInfo
objUser.LoadUser
debug.Print objUser.UserName
succes
NG