K
kagard
Greetings:
I created two custom classes, clsEmployee and clsStaff. I want
clsStaff to hold a collection of employee objects. When I try to
instantiate clsStaff, VBA returns RTE 91 "Object variable or With
block variable not set." Specifically, this happens when I try to add
the first employee object to the collection with this line:
mcEmployees.Add AnEmployee, Key
At which point, the code is tries to execute the (empty)
Class_Terminate procedure for clsEmployee (which seems weird).
Another thing that seems strange is that I can print AnEmployee's
property values in the debug window:
? AnEmployee.Name
John Smith
But if I point to those same properties in the code window of the
currently executing class module, VBA says:
EmpName<Object variable or With block variable not set>
If it's not set, how can it print it for me in the debug window? Is
this a scope issue with the recordset. If the recordset goes out of
scope, are objects created from it destroyed? This is my first attempt
at using classes in Access VBA. (I'm using Access 2010).
The code apprear below my signature. Thanks for your help.
TIA
Keith
Public Sub TestStaffClass()
Dim TheStaff As clsStaff
Set TheStaff = New clsStaff
Debug.Print TheStaff.StaffCount
Debug.Print TheStaff.StaffName(0)
Debug.Print TheStaff.StaffName(TheStaff.StaffCount - 1)
End Sub
'The Staff Class
'-------------------
Option Compare Database
Option Explicit
Private mcEmployees As Collection
Private mStaffCount As Integer
Private Sub Class_Initialize()
'Class constructor
FillStaffCollection
End Sub
Private Sub Class_Terminate()
'Class destructor
End Sub
Private Sub FillStaffCollection()
Dim db As Database
Dim rsStaff As DAO.Recordset
Dim NoEmployees As Boolean
Dim AnEmployee As clsEmployee
Dim StaffCounter As Integer
Dim Key As Variant
Set db = CurrentDb
Set rsStaff = db.OpenRecordset("tlkpSalesStaff")
NoEmployees = (rsStaff.BOF And rsStaff.EOF)
If Not NoEmployees Then
rsStaff.MoveFirst
StaffCounter = 0
Do Until rsStaff.EOF
Set AnEmployee = New clsEmployee
With AnEmployee
.EmpNbr = rsStaff!Route
.EmpName = rsStaff!Salesmen
.BossName = rsStaff!DM
Key = .EmpName
End With
'Instantiation fails on the next line
'with RTE 91 "Object Variable or With block variable not
set."
mcEmployees.Add AnEmployee, Key
StaffCounter = StaffCounter + 1
Set AnEmployee = Nothing
rsStaff.MoveNext
Loop
End If
Me.StaffCount = StaffCounter
'Clean up
rsStaff.Close
Set rsStaff = Nothing
Set db = Nothing
End Sub
Public Property Get StaffCount() As Variant
StaffCount = mStaffCount
End Property
Public Property Let StaffCount(ByVal vNewValue As Variant)
mStaffCount = vNewValue
End Property
Public Property Get StaffName(ItemNbr As Long) As String
StaffName = mcEmployees.Item(ItemNbr)
End Property
'The Employee Class
'-------------------------
Option Compare Database
Option Explicit
'Set Employee's public properties
Private mEmpNbr As String
Private mEmpName As String
Private mLevel As String ' BM, Director, DM, Sales Rep,
Trainee
Private mSpecialty As String ' Combo, Chain, Craft, etc.
Private mBossNbr As String
Private mBossName As String
Private mGoalsPayout As Currency
Private mObjectivesPayout As Currency
Private mcGoals As Collection
'Add a GoalsActual collection
'Add an ObjectivesPlan collection
'Add an ObjectivesActual collection
Private Sub Class_Initialize()
'Class constructor
End Sub
Private Sub Class_Terminate()
'Class destructor
End Sub
Private Sub GetEmpGoals()
'Populate the employee goals collection
'Open a recordset containing volume goals for this employee
'Read through the recordset
'Create a Goal object from the record
'Add the Goal object to the Goals collection
End Sub
Public Property Get EmpNbr() As Variant
EmpNbr = mEmpNbr
End Property
Public Property Let EmpNbr(ByVal vNewValue As Variant)
mEmpNbr = vNewValue
End Property
'Additional Gets and Lets removed for brevity.
I created two custom classes, clsEmployee and clsStaff. I want
clsStaff to hold a collection of employee objects. When I try to
instantiate clsStaff, VBA returns RTE 91 "Object variable or With
block variable not set." Specifically, this happens when I try to add
the first employee object to the collection with this line:
mcEmployees.Add AnEmployee, Key
At which point, the code is tries to execute the (empty)
Class_Terminate procedure for clsEmployee (which seems weird).
Another thing that seems strange is that I can print AnEmployee's
property values in the debug window:
? AnEmployee.Name
John Smith
But if I point to those same properties in the code window of the
currently executing class module, VBA says:
EmpName<Object variable or With block variable not set>
If it's not set, how can it print it for me in the debug window? Is
this a scope issue with the recordset. If the recordset goes out of
scope, are objects created from it destroyed? This is my first attempt
at using classes in Access VBA. (I'm using Access 2010).
The code apprear below my signature. Thanks for your help.
TIA
Keith
Public Sub TestStaffClass()
Dim TheStaff As clsStaff
Set TheStaff = New clsStaff
Debug.Print TheStaff.StaffCount
Debug.Print TheStaff.StaffName(0)
Debug.Print TheStaff.StaffName(TheStaff.StaffCount - 1)
End Sub
'The Staff Class
'-------------------
Option Compare Database
Option Explicit
Private mcEmployees As Collection
Private mStaffCount As Integer
Private Sub Class_Initialize()
'Class constructor
FillStaffCollection
End Sub
Private Sub Class_Terminate()
'Class destructor
End Sub
Private Sub FillStaffCollection()
Dim db As Database
Dim rsStaff As DAO.Recordset
Dim NoEmployees As Boolean
Dim AnEmployee As clsEmployee
Dim StaffCounter As Integer
Dim Key As Variant
Set db = CurrentDb
Set rsStaff = db.OpenRecordset("tlkpSalesStaff")
NoEmployees = (rsStaff.BOF And rsStaff.EOF)
If Not NoEmployees Then
rsStaff.MoveFirst
StaffCounter = 0
Do Until rsStaff.EOF
Set AnEmployee = New clsEmployee
With AnEmployee
.EmpNbr = rsStaff!Route
.EmpName = rsStaff!Salesmen
.BossName = rsStaff!DM
Key = .EmpName
End With
'Instantiation fails on the next line
'with RTE 91 "Object Variable or With block variable not
set."
mcEmployees.Add AnEmployee, Key
StaffCounter = StaffCounter + 1
Set AnEmployee = Nothing
rsStaff.MoveNext
Loop
End If
Me.StaffCount = StaffCounter
'Clean up
rsStaff.Close
Set rsStaff = Nothing
Set db = Nothing
End Sub
Public Property Get StaffCount() As Variant
StaffCount = mStaffCount
End Property
Public Property Let StaffCount(ByVal vNewValue As Variant)
mStaffCount = vNewValue
End Property
Public Property Get StaffName(ItemNbr As Long) As String
StaffName = mcEmployees.Item(ItemNbr)
End Property
'The Employee Class
'-------------------------
Option Compare Database
Option Explicit
'Set Employee's public properties
Private mEmpNbr As String
Private mEmpName As String
Private mLevel As String ' BM, Director, DM, Sales Rep,
Trainee
Private mSpecialty As String ' Combo, Chain, Craft, etc.
Private mBossNbr As String
Private mBossName As String
Private mGoalsPayout As Currency
Private mObjectivesPayout As Currency
Private mcGoals As Collection
'Add a GoalsActual collection
'Add an ObjectivesPlan collection
'Add an ObjectivesActual collection
Private Sub Class_Initialize()
'Class constructor
End Sub
Private Sub Class_Terminate()
'Class destructor
End Sub
Private Sub GetEmpGoals()
'Populate the employee goals collection
'Open a recordset containing volume goals for this employee
'Read through the recordset
'Create a Goal object from the record
'Add the Goal object to the Goals collection
End Sub
Public Property Get EmpNbr() As Variant
EmpNbr = mEmpNbr
End Property
Public Property Let EmpNbr(ByVal vNewValue As Variant)
mEmpNbr = vNewValue
End Property
'Additional Gets and Lets removed for brevity.