Custom Collection in Form Module not working

[Al Williams]

Access 2003
I am unable to get a custom collection to work in a Form module. The code
snippets are:

Dim colBaseThemes as Collection
Set colBaseThemes = New Collection
Dim strBaseRef as String

open the recordset (it works)
With rstBaseSectionThemes
.Find strBaseRef (it works)
Do While Not .EOF
colBaseThemes.Add Item:=rstBaseSectionThemes!ThemeNo,
Key:=Cstr(intBaseThemeKey)
Debug.Print "colbBaseThemes: " & colBaseThemes(Cstr(intBaseThemeKey) (it
prints the correct answer)
intBaseThemeKey = intBaseThemeKey +1
.Find strBaseRef, 1 (it works)
Loop
Debug.Print "Count: " & colBaseThemes.Count (the correct count (4) is printed)
Debug.Print "colBaseThemes: " & colBaseThemes(1) (to print the first entry
in the collection. Get error message: "Either BOF or EOF is True, or the
current record has been deleted. Requested operation requires a current
record."

What am I doing wrong? Thanks.

 

[Wolfgang Kais]

Hello Al.

Al Williams wrotr

Access 2003
I am unable to get a custom collection to work in a Form module.
The code snippets are:

Dim colBaseThemes as Collection
Set colBaseThemes = New Collection
Dim strBaseRef as String

open the recordset (it works)
With rstBaseSectionThemes
.Find strBaseRef (it works)
Do While Not .EOF
colBaseThemes.Add Item:=rstBaseSectionThemes!ThemeNo,
Key:=Cstr(intBaseThemeKey)
Debug.Print "colbBaseThemes: " & colBaseThemes(Cstr(intBaseThemeKey)
(it prints the correct answer)
intBaseThemeKey = intBaseThemeKey +1
.Find strBaseRef, 1 (it works)
Loop
Debug.Print "Count: " & colBaseThemes.Count
(the correct count (4) is printed)
Debug.Print "colBaseThemes: " & colBaseThemes(1) (to print the first
entry in the collection. Get error message: "Either BOF or EOF is
True, or the current record has been deleted. Requested operation
requires a current record."

What am I doing wrong? Thanks.

It sounds like you added a field object (4 times) that, when read from
the colleciton, returns the value of that field for the current record
of the recordset which can not be returned, necause the is at EOF.
I guess that you wanted to add the values, not the field, so the
respective line should read:
.Add Item:=!ThemeNo.Value, Key:=Cstr(intBaseThemeKey)

 

[Al Williams]

Thank you!! That fixed the problem. And here I thought that !ThemeNo and
!ThemeNo.Value were the same, especially since the Debug.Print statements
gave the correct ThemeNos. Again, thanks.