ms access 97 vba collection.item(strKey) gives error 3021

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

I have a section of code something like this. Note the => lines for
explanation. Can someone help me figure out why I am getting this error?

Thanks.

Dim dbs As Database
Dim rstCategories As Recordset
Dim rstAds As Recordset
Dim CategoryFile As New Collection
Dim strKey As String
Dim strFile As String

Set dbs = CurrentDb
Set rstCategories = dbs.OpenRecordset("qryAdCategories")
Set rstAds = dbs.OpenRecordset("Items for Sale")

rstCategories.MoveFirst
While Not rstCategories.EOF
strKey = rstCategories.Fields("CatName")
CategoryFile.Add rstCategories.Fields("CatFile"), strKey

strFile = gcInHTMLDir & "\" & CategoryFile.Item(strKey)
=> The above code works OK

.... more code ...

rstCategories.MoveNext
Wend

rstAds.MoveFirst
While Not rstAds.EOF
strThisCategory = rstAds.Fields("Category")
strFile = gcInHTMLDir & "\" & CategoryFile.Item(strThisCategory)
=> The line above does NOT work!
=> Get a "Run-time error: '3021' No current record"
=> This error is supposedly for a recordset, which is not applicable, I
think.
=> It's basically the same code that worked in the While loop above, but
does not
=> work in this one. Any ideas?

... more code ....

rstAds.MoveNext
Wend
 
What are typical values for Categories in the "Items for Sale" table? If
they're numbers, that could be causing your problem. (And if you used the
ill-conceived "lookup table" feature in Access, it's likely that they're
numbers, regardless what you think they are!)
 
After I wrote this, which helped me "think aloud", I added
rstCategories.MoveFirst
after leaving the first While loop. Even though the Collection does not
appear to be related to the Recordset except as the source for the
Collection's Items, moving to a "current record" in the Recordset allowed
the Collection to work again.

While this solves the problem, it is more of a "workaround." I still don't
understand why that needs to be done, since reading Item from a Collection
is not dependent (or should not be dependent) on a Recordset's position or
EOF.

Can you explain this one?

Ted
 
Heiko said:
Hello Ted,
your problem should be that the collectionobject tries to insert an
item as object, which i got similary.
I just converted everything with a Trim$(cstr(field(<Name>))) or a
temporary string:

CategoryFile.Add Trim$(Cstr(rstCategories.Fields("CatFile"))), strKey
Click to expand...

Excellent catch Heiko, I think we were all missing that
point.

The only thing I would add is that the Trim(Cstr is probably
not needed. I suggest that just explicitly referencing the
field's Value property would remove the ambiguity.

CategoryFile.Add rstCategories!CatFile.Value, strKey
 
THANK YOU!

I tried this and it works just fine. Nowhere in my Access books (or MSDN
Knowledge Base) or my VBA books is this pointed out.

Actually, this solved two problems. I found another one after I wrote my
last post. Even though I was able to "read" the Items in the CategoryFile
Collection in the second While loop after I did an 'rstCategories.MoveFirst'
after the first While loop, all keys returned the value for Item(1),
regardless of how I addressed the item: Item("Automobiles"), Item(3),
Item(4), Item(n...), Item(strThisCategory). Everyone returned the value of
Item(1).

Now everthing works just fine. How did you learn about this?

Thanks again!

Ted
 
THANK YOU to you, Marsh, too.

Both yours and Heiko's suggestion work and solved both my problems. Again,
where is this documented? I can't be the only one who struggled with this,
can I?

Ted
 
Hello Ted,
I am getting in confusion with those themes during my
DoIT yourself with .NET in which almost all is an object.
To teach me something i implemented some collection in older
codefragments and get errors like yours.
After more than 10 years VB
i'ld like to say
Just do IT your way

Heiko
:-)
 
Back
Top