Claude050897
asked on
CDaoRecordset: "Item not found in this collection"
(From Visual C++ I use DAO (data access object) to work with a Access database.)
I'm going mad! Why do I get this message?
1) I open the recordset with:
m_rsTitle.Open (dbOpenDynaset, "SELECT TitleID, Title FROM Title");
2) Everytime I want to read or write to the set I do:
m_rsTitle.SetFieldValue ("Title", szBuffer);
With each read or write I get "Item not found in this collection"
I've tried using SetFieldValue (1, szBuffer) but it gives the same error.
The weird thing is that just yesterday it worked for the first record that I add,
but then after that it only gave the error. And it is not just with either GetFieldValue() or
SetFieldValue(), but with both.
Any suggestions?
I'm going mad! Why do I get this message?
1) I open the recordset with:
m_rsTitle.Open (dbOpenDynaset, "SELECT TitleID, Title FROM Title");
2) Everytime I want to read or write to the set I do:
m_rsTitle.SetFieldValue ("Title", szBuffer);
With each read or write I get "Item not found in this collection"
I've tried using SetFieldValue (1, szBuffer) but it gives the same error.
The weird thing is that just yesterday it worked for the first record that I add,
but then after that it only gave the error. And it is not just with either GetFieldValue() or
SetFieldValue(), but with both.
Any suggestions?
Oops, always hit Submit before reading what I typed...
Try that again...
****************
To Open the Recordset:
Set m_rsTitle = CurrentDb.OpenRecordset("S
To Add a Record:
CurrentDb.Execute "INSERT INTO Title (TitleID, Title) VALUES ("SMIDAV", "Dave Smith");"
To Read a Record:
MyString = DLookup("Title", "Title", "TitleID = 'SMIDAV'")
Try that again...
****************
To Open the Recordset:
Set m_rsTitle = CurrentDb.OpenRecordset("S
To Add a Record:
CurrentDb.Execute "INSERT INTO Title (TitleID, Title) VALUES ("SMIDAV", "Dave Smith");"
To Read a Record:
MyString = DLookup("Title", "Title", "TitleID = 'SMIDAV'")
That would be for individual records...
If you are doing a loop, then it would be better to open and read the recordset like this...
**************************
Dim db As Database
Dim m_rsTitle As DAO.Recordset
Dim strID As String
Dim strName As String
Set db = CurrentDb
Set m_rsTitle = db.OpenRecordset("SELECT Title.* FROM Title, dbOpenDynaset)
Do Until m_rsTitle.EOF
strID = m_rsTitle!TitleID
strName = m_rsTitle!Title
MsgBox "ID = " & strID & " And Name = " & strName
m_rsTitle.MoveNext
Loop
m_rsTitle.Close
Set m_rsTitle = Nothing
Set db = Nothing
**************************
Or if you want to add a Record...
**************************
Dim db As Database
Dim m_rsTitle As DAO.Recordset
Set db = CurrentDb
Set m_rsTitle = db.OpenRecordset("SELECT Title.* FROM Title, dbOpenDynaset)
m_rsTitle.AddNew
m_rsTitle!TitleID = InputBox("Enter the ID of the new person")
m_rsTitle!Title = InputBox("Enter the Name of the new person")
m_rsTitle.Update
m_rsTitle.Close
Set m_rsTitle = Nothing
Set db = Nothing
If you are doing a loop, then it would be better to open and read the recordset like this...
**************************
Dim db As Database
Dim m_rsTitle As DAO.Recordset
Dim strID As String
Dim strName As String
Set db = CurrentDb
Set m_rsTitle = db.OpenRecordset("SELECT Title.* FROM Title, dbOpenDynaset)
Do Until m_rsTitle.EOF
strID = m_rsTitle!TitleID
strName = m_rsTitle!Title
MsgBox "ID = " & strID & " And Name = " & strName
m_rsTitle.MoveNext
Loop
m_rsTitle.Close
Set m_rsTitle = Nothing
Set db = Nothing
**************************
Or if you want to add a Record...
**************************
Dim db As Database
Dim m_rsTitle As DAO.Recordset
Set db = CurrentDb
Set m_rsTitle = db.OpenRecordset("SELECT Title.* FROM Title, dbOpenDynaset)
m_rsTitle.AddNew
m_rsTitle!TitleID = InputBox("Enter the ID of the new person")
m_rsTitle!Title = InputBox("Enter the Name of the new person")
m_rsTitle.Update
m_rsTitle.Close
Set m_rsTitle = Nothing
Set db = Nothing
BTW...
Having field names the same as table names is confusing.
MyString = DLookup("Title", "Title", "TitleID = 'SMIDAV'")
I recommend using prefixes to differentiate Table names from Field names...
MyString = DLookup("fldTitle", "tblTitle", "fldTitleID = 'SMIDAV'")
Having field names the same as table names is confusing.
MyString = DLookup("Title", "Title", "TitleID = 'SMIDAV'")
I recommend using prefixes to differentiate Table names from Field names...
MyString = DLookup("fldTitle", "tblTitle", "fldTitleID = 'SMIDAV'")
MacRena,
i think claude is looking for c++ code, not access vba, but i could be mistaken...
claude,
i've never used the CDAORecordSet class, but it seems straightforward. i do have some questions though...
1.) do you have dbOpenDynaset defined somewhere as a constant?
2.) m_rsTitle has been declared as a new CDAORecordSet, right?
3.) the only info on the web i could find quickly about CDAORecordSet is from ms. they use a slightly different syntax than you do. such as:
rs.SetFieldValue(_T("Custo
vs what you have (similiar, but slightly different)
m_rsTitle.SetFieldValue ("Title", szBuffer);
admittedly, i don't quite know what the _T() does in the MS version. i would guess it's a method that determines what the integer value is of the field in question...
4.) are you calling m_rsTitle.AddNew(); with m_rsTitle.Update()???
just a few questions that might spark you...
dovholuk
i think claude is looking for c++ code, not access vba, but i could be mistaken...
claude,
i've never used the CDAORecordSet class, but it seems straightforward. i do have some questions though...
1.) do you have dbOpenDynaset defined somewhere as a constant?
2.) m_rsTitle has been declared as a new CDAORecordSet, right?
3.) the only info on the web i could find quickly about CDAORecordSet is from ms. they use a slightly different syntax than you do. such as:
rs.SetFieldValue(_T("Custo
vs what you have (similiar, but slightly different)
m_rsTitle.SetFieldValue ("Title", szBuffer);
admittedly, i don't quite know what the _T() does in the MS version. i would guess it's a method that determines what the integer value is of the field in question...
4.) are you calling m_rsTitle.AddNew(); with m_rsTitle.Update()???
just a few questions that might spark you...
dovholuk
ASKER
Yip, I want c++ code.
1) dbOpenDynaset is a MFC constant
2) m_rsTitle is declared as a CDaoRecordset
3) I think the _T() just constructs a string
4) Yip
1) dbOpenDynaset is a MFC constant
2) m_rsTitle is declared as a CDaoRecordset
3) I think the _T() just constructs a string
4) Yip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Claude,
If you are using VB 4.0, then this link (FIX: "Item not found in this collection" with MFC DAO Join) might have the solution for your problem (another M$ bug):
http://support.microsoft.com/support/kb/articles/Q139/9/97.asp
This link (FIX: "Item not found in this collection" Message Box) might be interesting as well:
http://support.microsoft.com/support/kb/articles/Q148/8/48.asp
Hope this helps,
Nosterdamus
If you are using VB 4.0, then this link (FIX: "Item not found in this collection" with MFC DAO Join) might have the solution for your problem (another M$ bug):
http://support.microsoft.com/support/kb/articles/Q139/9/97.asp
This link (FIX: "Item not found in this collection" Message Box) might be interesting as well:
http://support.microsoft.com/support/kb/articles/Q148/8/48.asp
Hope this helps,
Nosterdamus
Sorry...
If you are using VB 4.0
Should be:
If you are using C++ 4.0 ofcourse....
If you are using VB 4.0
Should be:
If you are using C++ 4.0 ofcourse....
To Open the Recordset:
m_rsTitle = CurrentDb.OpenRecordset("S
To Add a Record:
CurrentDb.Execute "INSERT INTO Title (TitleID, Title) VALUES ("SMIDAV", "Dave Smith");"
To Read a Record:
MyString = DLookup("Title", "Title", "TitleID = 'SMIDAV'")