asked on

using the "seek" function, getting error 3251

I have a form that I am using to edit fields in a table. The table is indexed on the field "OrderID" and this field has unique entries. When I finish making my changes I click on the save button and it goes to the following code:

Public Function seekOrderID(theOrderID As String)
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("Orders")

rec.Index = "idxOrderID"
rec.Seek "=", theOrderID

If rec.NoMatch Then
MsgBox "not found"
Else
MsgBox "Found"
End If

rec.Close
End Function

When the code hits the index line:
rec.Index = "idxOrderID"
I get the following error message:
run-time error '3251'
Operation is not supported by this type of object

I have already checked the table and changed the name of the index several times but nothing helped.

Thank you for your time.

MartyM

joshco

I would suggest using the SQL solution to this problem rather than trying to seek on indexes like that. This is a bit more advanced, but you should have no trouble following:

Public Function seekOrderID(theOrderID As String)
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("select orderid from Orders where orderid =" & theOrderId )

' If there are no records found, then the recordset will be at the end before you even begin to move it, thus it will not have found a record

if rst.eof = true then
MsgBox "not found"
Else
MsgBox "Found"
End If

rec.Close
set db = nothing
set rec = nothing
End Function

This is a bit easier, I suggest you research SQL for your benefit in the future.
-Josh

ASKER CERTIFIED SOLUTION

graham_charles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MartyM

ASKER

My error was that i was using a LINKED table, not a local one.

So new my new question is "Is it possible to use a LINKED table while using seek and/or find commands?"

Thank you very much.

graham_charles

Yes, but that is a new question. Please post it as a new question.

graham_charles

Oh, OK, fine, I'll post it here. You can use the .Seek and .Index on tables that are native to the database object. If you use "CurrentDB()", that means the current database. To open your data tables in a different database, you'll need to use the OpenDatabase method:

Dim db as DAO.Database
Dim rec as DAO.Recordset

Set db = DBEngine(0).OpenDatabase("c:\myfiles\mydb.mdb")
Set rec = db.OpenRecordset("Orders", dbOpenTable)

Then you'll be able to use Seek and Index.

g.

caltain

I know this is an old thread, but the short answer is that in order to use index and seek, you must use the dbOpenTable and you can't use that on a linked table. Doing so will result in a Runtime Error.

The SQL method of SELECT on a dynaset or snapshot recordset is the best I've found to work with linked tables. I'd prefer the speed of a seek type search, but I need to have the back end databases.

Dan Brooks

using the &quot;seek&quot; function, getting error 3251

using the "seek" function, getting error 3251