How do I build a this class?

fischerj used Ask the Experts™
I want to build a class and be able to access it as follows:




I can do one or the other but not both in the same class.

Any help with this issue would be appreciated!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

I assume te problem lies in the reduplication of the Fields property, and not in the creation of the Database and Table objects, so here's what I got from the VB help File.  Make sure to read the comments at the end as they are important:

Create the custom collection class from VB like:

Option Explicit
Private mcolEmployees As New Collection

Public Function Add(ByVal Name As String, _
ByVal Salary As Double) As Employee
   Dim empNew As New Employee
   Static intEmpNum As Integer
   ' Using With makes your code faster and more
   ' concise (.ID vs. empNew.ID).
   With empNew
      ' Generate a unique ID for the new employee.
      intEmpNum = intEmpNum + 1
      .ID = "E" & Format$(intEmpNum, "00000")
      .Name = Name
      .Salary = Salary
      ' Add the Employee object reference to the
      ' collection, using the ID property as the key.
      mcolEmployees.Add empNew, .ID
   End With
   ' Return a reference to the new Employee.
   Set Add = empNew
End Function

Public Function Count() As Long
   Count = mcolEmployees.Count
End Function

Public Sub Delete(ByVal Index As Variant)
   mcolEmployees.Remove Index
End Sub

' Method of the Employees collection class.
Public Function Item(ByVal Index As Variant) _
As Employee
   Set Item = mcolEmployees.Item(Index)
End Function

' NewEnum must return the IUnknown interface of a
' collection's enumerator.
Public Function NewEnum() As IUnknown
   Set NewEnum = mcolEmployees.[_NewEnum]
End Function
'To hide the NewEnum method and give it the necessary procedure ID

'On the Tools menu, click Procedure Attributes to open the
'Procedure Attributes dialog box. In Name box, select the NewEnum method.

'Click Advanced to show the advanced features.
'Check Hide this member to make NewEnum hidden in the type library.

************Important Comments****************
'In the Procedure ID box, type –4 (minus four) to give NewEnum the procedure
'ID required by For Each … Next. Click OK.
'Important   In order for your collection classes to work with
'For Each … Next, you must provide a hidden NewEnum method
'with the correct procedure ID.

Here's the code that would access this class:

Private Sub cmdEmployeeAdd_Click()
   sbMain.Employees.Add txtName.Text, txtSalary.Text
   txtName.Text = ""
   txtSalary.Text = ""
   cmdListEmployees.Value = True
End Sub

Private Sub cmdEmployeeDelete_Click()
   ' Check to make sure there's an employee selected.
   If lstEmployees.ListIndex > -1 Then
      ' The first six characters are the ID.
      sbMain.Employees.Delete _
      Left(lstEmployees.Text, 6)
   End If
   cmdListEmployees.Value = True
End Sub

Private Sub cmdListEmployees_Click()
   Dim emp As Employee
   For Each emp In sbMain.Employees
      lstEmployees.AddItem emp.ID & ", " & emp.Name _
      & ", " & emp.Salary
End Sub

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

twalgrave has the full code, but 1 small thing missing:
the Item() property/function needs to be defined to be the default property/function of the class. The procedure is similar as for the NewEnum function...


No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- award the points to twalgrave
Please leave any comments here within the
next seven days.
Per recommendation, force-accepted.

EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial