Create a collection of custom classes in another custom class

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.
 
A

argeedblu

You have declared the collection but have not created it before you
try to use it.

Private Sub Class_Initialize()
'Class constructor
set mcEmployees = new Collection

End Sub



Glenn
 
K

kagard

You have declared the collection but have not created it before you
try to use it.

Private Sub Class_Initialize()
'Class constructor
     set mcEmployees = new Collection

End Sub

Glenn

Sounds like a newbie mistake. Grrr. Thanks, Glenn.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top