Run-time error 3251 - Operation is not supported for this type of Object rs.Index = "

[JGravesNBS]

Run-time error 3251 - Operation is not supported for this type of Object rs.Index = "

I have a working Access 2010 IUOE.mdb that contains table, queries, forms, reports, etc

I split the database into IUOE_Fe.mdb (queries, forms, reports, etc) and IUOE_Be.mdb (tables)

I have a form that is giving me the following error on a field On Lost Focus event

Set rs = db.OpenRecordset("CodeWDOEpct") ', dbOpenSnapshot, dbForwardOnly)

'Run-time error 3251 - Operation is not supported for this type of Object
rs.Index = "id"

How can I get the code working?

CodeWDOEpct (table) looks as follows:

id is an AutoNumber, Primary Key, No Index

id Agreement WDOE OT DT TT

1 Maintenance 0.0475 $13.08 $0.00 $0.00
2 Heavy Highway 0.0475 $13.58 $27.16 $54.32
3 Building 0.0475 $13.08 $0.00 $0.00
4 Council 0.0475 $13.58 $27.16 $54.32
5 Technical Engineer's 0.04 $0.00 $0.00 $0.00
6 Shop 0.015 $0.00 $0.00 $0.00
7 Municipality 0.015 $0.00 $0.00 $0.00

 

[The_Doc_Man]

What you have is a statement that probably doesn't like having an OpenRecordset with no open-type. That tick-mark after the first right parenthesis makes everything to the right of it a comment, so it is going to try to do the default, which is a dbOpenTable (I think). Is there something about the table that is somehow a bit strange such that you would not want to open it as a table-type recordset?

 

[JGravesNBS]

I removed the tick mark and get the same error

I inherited this code, my vba is limited, i'm just trying to get the database split, the code works fine in the IUOE.mdb

After the:

Set rs = db.OpenRecordset("CodeWDOEpct") ', dbOpenSnapshot, dbForwardOnly)
rs.Index = "id"

There are a series of the following routines:

rs.Seek ">=", ctty, 3
If rs.NoMatch Then
msgbox "Unable to find match"
Else
[Forms]![wdoe]![ot_re] = Round(rs![ot] * b, 2)
End If

 

[moke123]

IIRC, isnt there some type of limitation in using the seek method with linked tables? I havent used seek in a long time but recall there is some issue. Maybe with table type recordsets?

edit: found this...You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects

 

[gemma-the-husky]

note that this is also posted on UA

as moke says, I think the problem is a seek/indexed read on a linked table which is not available, which is what I posted on UA

 

[JGravesNBS]

Moke

Thanks for the clue, after further review the following seems to work

Tables: How to perform Seek on Linked Tables
http://access.mvps.org/access/tables/tbl0006.htm

Thanks again for saving my butt