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.OpenRecordse t(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("tblP ersonal", "SSN", strSSN)
But when I use
blnExists("tblE xamData", "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]

Re: Problem with Index using Seek method

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" <ranb550@hotmai l.com> wrote in message
news:ad618ae4.0 408050716.5c4ea 174@posting.goo gle.com...[color=blue]
> 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.OpenRecordse t(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("tblP ersonal", "SSN", strSSN)
> But when I use
> blnExists("tblE xamData", "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.[/color]

[david epsom dot com dot au]

Re: Problem with Index using Seek method

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" <ranb550@hotmai l.com> wrote in message
news:ad618ae4.0 408050716.5c4ea 174@posting.goo gle.com...[color=blue]
> 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.OpenRecordse t(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("tblP ersonal", "SSN", strSSN)
> But when I use
> blnExists("tblE xamData", "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.[/color]

[RBohannon]

Re: Problem with Index using Seek method

"david epsom dot com dot au" <david@epsomdot comdotau> wrote in message news:<4112e771$ 0$27221$61ce578 d@news.syd.swif tdsl.com.au>...[color=blue]
> 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" <ranb550@hotmai l.com> wrote in message
> news:ad618ae4.0 408050716.5c4ea 174@posting.goo gle.com...[color=green]
> > 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.OpenRecordse t(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("tblP ersonal", "SSN", strSSN)
> > But when I use
> > blnExists("tblE xamData", "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.[/color][/color]

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