Problem with Index using Seek method

[RBohannon]

I'm using Access 2000.
I've written a function, blnExists(), to check if a particular value
exists in the primary key field of a table. blnExists returns true if
the value is in the table and false if not.

Public Function blnExists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strRS)

rs.Index = strIndex
rs.Seek "=", strTarget

If rs.NoMatch = True Then
blnExists = False
Else
blnExists = True
End If

rs.Close

End Function

where strRS is the recordset to be searched, strIndex is the Index of
the recordset, and strTarget is the value being searched for.

The function works with
blnExists("tblPersonal", "SSN", strSSN)
But when I use
blnExists("tblExamData", "ExamNum", strExam)
I get an error message: Run-time error '3800' : 'ExamNum' is not an
index in this table.
and execution stops at
rs.Index = strIndex

But ExamNum is the primary key field of tblExamData; Indexed == Yes(No
Duplicates).
Can anyone suggest why this is happening and how to fix it?

Thank you for your consideration.

[Allen Browne]

The index may be named "PrimaryKey" instead of "ExamNum".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RBohannon" <ra*****@hotmail.com> wrote in message
news:ad**************************@posting.google.c om...

I'm using Access 2000.
I've written a function, blnExists(), to check if a particular value
exists in the primary key field of a table. blnExists returns true if
the value is in the table and false if not.

Public Function blnExists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strRS)

rs.Index = strIndex
rs.Seek "=", strTarget

If rs.NoMatch = True Then
blnExists = False
Else
blnExists = True
End If

rs.Close

End Function

where strRS is the recordset to be searched, strIndex is the Index of
the recordset, and strTarget is the value being searched for.

The function works with
blnExists("tblPersonal", "SSN", strSSN)
But when I use
blnExists("tblExamData", "ExamNum", strExam)
I get an error message: Run-time error '3800' : 'ExamNum' is not an
index in this table.
and execution stops at
rs.Index = strIndex

But ExamNum is the primary key field of tblExamData; Indexed == Yes(No
Duplicates).
Can anyone suggest why this is happening and how to fix it?

Thank you for your consideration.

[david epsom dot com dot au]

The default index for a primary key field is called
'PrimaryKey'. Open the table in design mode: right
click and select 'indexes' to see named indexes.
Index names don't change when you change field
names, and not all fields are automatically indexed.

"RBohannon" <ra*****@hotmail.com> wrote in message
news:ad**************************@posting.google.c om...

I'm using Access 2000.
I've written a function, blnExists(), to check if a particular value
exists in the primary key field of a table. blnExists returns true if
the value is in the table and false if not.

Public Function blnExists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strRS)

rs.Index = strIndex
rs.Seek "=", strTarget

If rs.NoMatch = True Then
blnExists = False
Else
blnExists = True
End If

rs.Close

End Function

where strRS is the recordset to be searched, strIndex is the Index of
the recordset, and strTarget is the value being searched for.

The function works with
blnExists("tblPersonal", "SSN", strSSN)
But when I use
blnExists("tblExamData", "ExamNum", strExam)
I get an error message: Run-time error '3800' : 'ExamNum' is not an
index in this table.
and execution stops at
rs.Index = strIndex

But ExamNum is the primary key field of tblExamData; Indexed == Yes(No
Duplicates).
Can anyone suggest why this is happening and how to fix it?

Thank you for your consideration.

[RBohannon]

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message news:<41***********************@news.syd.swiftdsl. com.au>...

The default index for a primary key field is called
'PrimaryKey'. Open the table in design mode: right
click and select 'indexes' to see named indexes.
Index names don't change when you change field
names, and not all fields are automatically indexed.

"RBohannon" <ra*****@hotmail.com> wrote in message
news:ad**************************@posting.google.c om...

I'm using Access 2000.
I've written a function, blnExists(), to check if a particular value
exists in the primary key field of a table. blnExists returns true if
the value is in the table and false if not.

Public Function blnExists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strRS)

rs.Index = strIndex
rs.Seek "=", strTarget

If rs.NoMatch = True Then
blnExists = False
Else
blnExists = True
End If

rs.Close

End Function

where strRS is the recordset to be searched, strIndex is the Index of
the recordset, and strTarget is the value being searched for.

The function works with
blnExists("tblPersonal", "SSN", strSSN)
But when I use
blnExists("tblExamData", "ExamNum", strExam)
I get an error message: Run-time error '3800' : 'ExamNum' is not an
index in this table.
and execution stops at
rs.Index = strIndex

But ExamNum is the primary key field of tblExamData; Indexed == Yes(No
Duplicates).
Can anyone suggest why this is happening and how to fix it?

Thank you for your consideration.

Right you are.
The reason the function worked with tblPersonal is because SSN was
indexed, but it was not the primary key.

I made the necessary changes, and now everything works OK.

Thank you both,

Randy