Item not in this collection 
Author Message
 Item not in this collection

Ths code below works fine until it gets to the line "s_sname =
rst![Sample_Surname]" where it falls over and gives the above error message.
I've checked the table and 'Sample_Surname' is there.  I've gone so far as
recreating the table and renaming all the fields with no luck.  It dossn't
complain about 'bupa' or 's_date', so what's wrong with this particular
field?   Does someone know what's going wrong here.  Any help much
appreciated.

Mo

Function samples()

Dim db As Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim criteria As String
Dim reccount As Integer
Dim s_sname As String
Dim bupa As String
Dim s_date As Date

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_bu_imp", dbOpenDynaset)

If rst.RecordCount = 0 Then
    MsgBox "No records to merge", vbOKOnly + vbInformation, "No Records"
    Exit Function
End If

rst.MoveLast
reccount = rst.RecordCount
rst.MoveFirst

Set rst = db.OpenRecordset("qry_find_samples", dbOpenDynaset)

Set rst3 = db.OpenRecordset("tbl_visits", dbOpenDynaset)

Do Until rst.EOF

s_sname = rst![Sample_Surname]
bupa = rst![Bupano]
s_date = rst![Sample_Date]

Loop

End Function



Fri, 28 May 2004 21:08:06 GMT  
 Item not in this collection
Hi.

Are you sure you using the right recordset, you never set rst2.

You wrote "I've checked the table and 'Sample_Surname' is there" but in your
code you first sets the rst to tbl_bu_imp in
Set rst = db.OpenRecordset("tbl_bu_imp", dbOpenDynaset)
and the later on you set rst to qry_find_samples in
Set rst = db.OpenRecordset("qry_find_samples", dbOpenDynaset)
are you sure that the field [Sample_Surname] is in the query
"qry_find_samples".

maybe it should be
Set rst2 = db.OpenRecordset("qry_find_samples", dbOpenDynaset)

If not, what are you using rst2 and rst3 for, i can't see you using it
anywere?

s_sname = rst![Sample_Surname]
bupa = rst![Bupano]
s_date = rst![Sample_Date]

--
JBu


Quote:
> Ths code below works fine until it gets to the line "s_sname =
> rst![Sample_Surname]" where it falls over and gives the above error
message.
> I've checked the table and 'Sample_Surname' is there.  I've gone so far as
> recreating the table and renaming all the fields with no luck.  It dossn't
> complain about 'bupa' or 's_date', so what's wrong with this particular
> field?   Does someone know what's going wrong here.  Any help much
> appreciated.

> Mo

> Function samples()

> Dim db As Database
> Dim rst As DAO.Recordset
> Dim rst2 As DAO.Recordset
> Dim rst3 As DAO.Recordset
> Dim criteria As String
> Dim reccount As Integer
> Dim s_sname As String
> Dim bupa As String
> Dim s_date As Date

> Set db = CurrentDb()
> Set rst = db.OpenRecordset("tbl_bu_imp", dbOpenDynaset)

> If rst.RecordCount = 0 Then
>     MsgBox "No records to merge", vbOKOnly + vbInformation, "No Records"
>     Exit Function
> End If

> rst.MoveLast
> reccount = rst.RecordCount
> rst.MoveFirst

> Set rst = db.OpenRecordset("qry_find_samples", dbOpenDynaset)

> Set rst3 = db.OpenRecordset("tbl_visits", dbOpenDynaset)

> Do Until rst.EOF

> s_sname = rst![Sample_Surname]
> bupa = rst![Bupano]
> s_date = rst![Sample_Date]

> Loop

> End Function



Fri, 28 May 2004 21:32:54 GMT  
 Item not in this collection
Thanks JBU.  Couldn't see the wood for the trees again!  I've sorted the
problem out now.  I do use rst2  & rst3 later (hadn't written the code yet).

Mo


Quote:
> Hi.

> Are you sure you using the right recordset, you never set rst2.

> You wrote "I've checked the table and 'Sample_Surname' is there" but in
your
> code you first sets the rst to tbl_bu_imp in
> Set rst = db.OpenRecordset("tbl_bu_imp", dbOpenDynaset)
> and the later on you set rst to qry_find_samples in
> Set rst = db.OpenRecordset("qry_find_samples", dbOpenDynaset)
> are you sure that the field [Sample_Surname] is in the query
> "qry_find_samples".

> maybe it should be
> Set rst2 = db.OpenRecordset("qry_find_samples", dbOpenDynaset)

> If not, what are you using rst2 and rst3 for, i can't see you using it
> anywere?

> s_sname = rst![Sample_Surname]
> bupa = rst![Bupano]
> s_date = rst![Sample_Date]



Fri, 28 May 2004 23:15:31 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. item not found in collection error in qrydef

2. Item not found in Collection Error?

3. Help needed with an item not in a collection..........ACC97

4. Item not found in collection error

5. Item Not Found in Collection (Error 3036)

6. Item Not Found in Collection (Null Fields)

7. 3265 runtime error, item not found in collection

8. Trouble with ADO Connection.Open() Error 3625 Item not found in collection

9. VB Collections - Accessing the collection, not the item.

10. Variable field Names/Item not in Collection

11. Item not found in this collection

12. Item not found in this collection error

 

 
Powered by phpBB® Forum Software