How to iterate through a custom collection

[vicrauch]

I have not been able to figure out what I need to get to the individual entries in a collection. I have the For Each loop, but the code stops at Debug.Print Item.FieldName. The following is the code. I'm looking for how to do this so it will work. Thanks! The error message is: "Object doesn't support this property or method" Run=time error '438'

Sub ShowFields()
Dim Item As Variant
   For Each Item In cAddPermits
   Debug.Print Item.FieldName
  Next Item
End Sub

Here is the code for the two classes used here. (cFieldName)

Option Compare Database
Option Explicit

Dim mFieldName As String

Public Property Get FieldName() As String
  FieldName = mFieldName
End Property

Public Property Let FieldName(newFieldName As String)
  mFieldName = newFieldName
End Property

And then cFieldNames

Option Compare Database
Option Explicit

Private mPrivateCollection As Collection

' Add a new cFieldName item to the collection
Public Function Add(FieldName As String) As cFieldName
  Dim newItem As New cFieldName
  Dim Key As Variant

  newItem.FieldName = FieldName
  Key = FieldName
  
'add to the private collection
  mPrivateCollection.Add newItem, Key         ', FieldName
  Set Add = newItem
ExitFunction:
  Set newItem = Nothing
  Exit Function
End Function

And finally, the routine that fills the collection

Option Compare Database
Option Explicit

Public cAddPermits As cFieldNames

Sub FillAddPermits()
  Set cAddPermits = New cFieldNames
  cAddPermits.ClassInitialize
  
  cAddPermits.Add "PermitType"
  cAddPermits.Add "FiscalYearID"
End Sub

[rpeare]

I don't know what you're trying to do but this is an example of what I use to cycle through records in a table:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("<table or query name>")
rst.MoveFirst

Do While rst.EOF <> True
    sfield1 = rst.Fields(0) 'first field of a recordset is always 0, you can also put the field name in quote marks 
    sfield2 = rst.Fields(1)
    debug.print sfield1 & ", " & sfield2
    rst.MoveNext
Loop
rst.Close
Set db = Nothing

[vicrauch]

rpeare, Thanks for trying, but a collection is nothing like a table as far as Access is concerned. So explaining how to read sequential records from a table really is no help in discovering what is wrong with the line of code I've shown in red.

[rpeare]

I have not been able to figure out what I need to get to the individual entries in a table

This says to me 'I want to cycle through records in a table' (you specifically mentioned a table in your post)

The process I posted lets you cycle through records, and where needed edit the contents of a specific record (the code isn't in my example but it uses the same type of structure).

So perhaps if it's not actually a TABLE that you want to cycle through you could say what exactly you are trying to cycle through? is it a list box, a combo box, a virtual query?

[vicrauch]

Sorry about the accidental reference to table. I guess I work with them too much. I do believe all the rest of the code shows that I'm working with classes and collections. Plus the title of the thread is "How to iterate through a custom collection."

[pbaldy]

Vic, I saw the thread but was going to leave it for someone more knowledgeable in classes and collections (in that instance I like to leave the thread with 0 replies so someone else is more likely to look at it). They are an area of weakness for me, as I've never seemed to have a need for them. A guy wrote a process for us years ago that used them, and he incremented a variable when he populated it and then iterated through it with:

        For lngTemp = 0 To lngCarTMax
            If Trim$(udtCarT(lngTemp).Car) = strTemp Then

I rewrote the process later with a simple lookup. I've also seen:

Dim obj As Object          

For Each obj In clnPopUpAlarm       
  ...     
Next

[orange]

I saw the post as well and have used classes very rarely. There is an example at
http://bytes.com/topic/access/answer...tions-question

where a respondent terry Kreft responded to a post. He also shows how to iterate.
There is no reference to a Table, but if you just want to move something from a collection to a table, then I guess instead of debug.print you do an addnew and update. Just my $.02.
Good luck