Code Help...

[Rjc8513]

In [tbl NON MAIL LIST] I have a list of all units that do not have access to e-mail. I want to cycle thru that list, taking each IDNo and using it as a paramater in the query [sqry- NON MAIL LIST]. The query extracts data from the main table [tbl DATA]. Then I want to print the report [rpt NON MAIL]. The Record Source of the form is [sqry- NON MAIL LIST].

Below is my code. When I run the procedure, I get the error message "Run-time error 3265. Item not found in this collection". Can anyone tell me what I'm doing wrong? Thanks.

Dim IDNoFile As String
Dim NoRecords As Long
Dim I As Long
Dim dbs As Database
Dim rstList As Recordset
Dim fldFinNoFile As Field
Dim qryList As QueryDef

Set dbs = CurrentDb
Set rstList = dbs.OpenRecordset("tbl NON MAIL LIST&quot
Set fldIDNoFile = rstList.Fields("FILE NAME&quot
Set qryList = dbs.QueryDefs("sqry- NON MAIL LIST&quot

NoRecords = rstList.RecordCount

For I = 1 To NoRecords
IDNoFile = Left(fldIDNoFile.Value, 6)
qryList.Parameters![FINANCE] = IDNoFile
DoCmd.OpenReport "rpt NON MAIL"
rstList.MoveNext
Next I

Exit Function

 

[Databaseguy]

Item not found means what it sounds like. Check:

Set rstList = dbs.OpenRecordset("tbl NON MAIL LIST&quot
Set fldIDNoFile = rstList.Fields("FILE NAME&quot
Set qryList = dbs.QueryDefs("sqry- NON MAIL LIST&quot
Make sure tbl NON MAIL LIST, FILE NAME and sqry- NON MAIL LIST exist and are spelled properly. Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[MichaelRed]

Other problems may (WILL) arise:

NoRecords = rstList.RecordCount 'May (Probably?) not accurate?
'Should use rstList.Eof
'For I = 1 To NoRecords
Do While Not rstList.Eof

' IDNoFile = Left(fldIDNoFile.Value, 6) 'Needs a reference to rstList
IDNoFile = Left(rstLIst!fldIDNoFile.Value, 6)
qryList.Parameters![FINANCE] = IDNoFile 'Appears to attempt to assign the FIELD
"[Finance]", rather than the PARAMETER, [Finance], But I can't
really tell w/o the SQL for the query.
DoCmd.OpenReport "rpt NON MAIL" 'Record Source is "qryList"?
rstList.MoveNext
Loop 'More re 'proper' looping through recordset
'Next I

Exit Function
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over