After splitting the database, portions of code that use recordsets
(now based on linked tables) don't work. Runtime error 3251
"Operation is not supported for this type of object." I've read past
posts on the same issue, but I can't get any solutions to work. Self-
taught amateur (so be gentle and use itty-bitty words, please). Even
hard-coding the new BE would be fine, really! My users are trying to
place folks in jobs and feed widows -- AND their air conditioning just
died! I really need to fix this quickly.

This code creates the next unique Household_Id (based on adding 1 to
the last value added).
The line where it breaks is: Household_Id_Recordset.Index =
"PrimaryKey"
The location of the BE is: D:\Client Statistical Database
\ClientDatabase_BE.mdb


Option Compare Database
Option Explicit

Private current_db As Database
Private Household_Id_Recordset As Recordset
Private Household_Id_Table As TableDef

Private Sub Form_Current()

Dim Current_Household_Id_String As String
Dim Current_Household_Id_Int As Long
Dim Update_Sql As String

'Open Next_Household_Id table
Set current_db = CurrentDb()
Set Household_Id_Table = current_db.TableDefs("Next_Household_Id")
Set Household_Id_Recordset = Household_Id_Table.OpenRecordset()
Household_Id_Recordset.Index = "PrimaryKey"

'Find out next household id
Current_Household_Id_String =
Household_Id_Recordset.Fields(0).Value
'Pad left with zeros
Current_Household_Id_String = Right("000000" &
Current_Household_Id_String, 6)

'Write household id into the form
Me![Household_Id] = Current_Household_Id_String

'Add 1 to next household id table
Current_Household_Id_Int = Val(Current_Household_Id_String)
Current_Household_Id_Int = Current_Household_Id_Int + 1
Update_Sql = "UPDATE Next_Household_Id SET
Next_Household_Id.Next_Id = " & Trim(Str(Current_Household_Id_Int))

DoCmd.RunSQL (Update_Sql)

End Sub


Any ideas are very welcome!

If I can get this bit working, I should be able to apply it to do in 8
other spots of code, and thus make some very hard-working, sweaty
charity workers a bit happier. Sigh.

Tia

Tia Murchie-Beyma wrote:
You can padzero using format. Ex:
num = 1
? format(num,"00000000")
00000001
num = 1234
? format(num,"00000000")
00001234
num = 12345678
? format(num,"00000000")
12345678

You could use Dlookup to get the next id since it appears there's only
one record in the table
Dim lngID as Long
lngID = Dlookup("Next_Id","Next_Household_Id")

Some people get the nextID using Dmax to get that maximum id value in a
table and adding 1 to it without using another table to hold the next
key value. Ex:
lngNextID = Dmax("YourIDFldName","YourTableName") + 1

Splitting the database should not affect the working of the database.
Modifying code in 7-8 in other locations simply adds the opportunity for
more errors. What I'd recommend is put the word
STOP
under the Private Sub Form_Current() line. This will place you in
debug/step mode. Then step thru the code and determine which line your
code is blowing up on. Knowing where it's blowing up would appear
crucial to solving your problem.

Salad, thank you for your ideas on padding zero and incrementing
Household_Id.

The line where this module breaks is:

Household_Id_Recordset.Index = "PrimaryKey"

and the reason it breaks is that table-type recordsets don't work with
linked tables, which is naturally what I have after splitting. That's
why splitting affected the code, and why I need to change it in
several different places in the application. I knew how to use
recordsets, more or less, and how handy that was! But now those forms
are broken.

Tia

Tia Murchie-Beyma <tiamurch (AT) gmail (DOT) com> wrote in
news:a2f1ca73-efd7-4e33-9ad0-de86d5d36dc8 (AT) x21g2000yqa (DOT) googlegroups.co
m:

Sounds like code that used to use SEEK, which works only on tables.
In a brief scan of the code, I didn't see a SEEK, though.

The other thing is that you can't open a table-type recordset on a
linked table.

Neither of these is something to worry about. You can always get the
same functionality by opening a database object that points to the
back end.

Of course, in general, there is hardly ever any justification for
using SEEK, as it shines only when you're jumping around a large
recordset thousands of time. Otherwise, you should use FindFirst or
just filter your recordset to the desired records. As to table-type
recordsets, I see no reason not to leave off the option entirely and
let the default recordset type be used (dynaset).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Tia Murchie-Beyma <tiamurch (AT) gmail (DOT) com> wrote in
news:f8c9972a-9649-4dfb-ae83-166904a73a40 (AT) s9g2000yqd (DOT) googlegroups.com
:

I've never declared a type-type recordset nor used SEEK.

And I have never had a form broken by problems affecting recordsets,
since I use bound forms, and let Access manage the recordsets behind
them.

It sounds to me like your unsplit application had a lot of things in
it that I'd classify as premature optimization -- things sound like
they were too complicated by half.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thanks! It was INDEX! Like SEEK, it doesn't work with those linked
tables, I guess. When Access links to external tables, the result is
evidently always dynaset type, and dynaset doesn't play with Index.
Or Seek, though I wasn't using it. I removed the index line and
cleaned some other stuff up, and my form now works, as beautifully as
before the split, with the code below. (I kind of wonder why I used
the index property (method?) at all, way back when.)

Option Compare Database
Option Explicit

Private Sub Form_Current()

Dim dbs As Database
Dim Current_Household_Id_String As String
Dim Current_Household_Id_Int As Long
Dim Household_Id_Recordset As Recordset
Dim Household_Id_Table As TableDef
Dim Update_Sql As String

Set dbs = CurrentDb

'Open Next_Household_Id table
Set Household_Id_Table = dbs.TableDefs("Next_Household_Id")
Set Household_Id_Recordset = Household_Id_Table.OpenRecordset()

'Find out next household id
Current_Household_Id_String =
Household_Id_Recordset.Fields(0).Value

'Pad left with zeros
Current_Household_Id_String = Right("000000" &
Current_Household_Id_String, 6)

'Write Household ID into the form
Me![Household_Id] = Current_Household_Id_String

'Add 1 to next household id table
Current_Household_Id_Int = Val(Current_Household_Id_String)
Current_Household_Id_Int = Current_Household_Id_Int + 1
Update_Sql = "UPDATE Next_Household_Id SET
Next_Household_Id.Next_Id = " & Trim(Str(Current_Household_Id_Int))

DoCmd.RunSQL (Update_Sql)

End Sub

Me, neither. At least not on purpose, with the first. But asking to
set that index, I guess, treated the recordset as a table type, and
there I was. Over complicated because I don't know enough. Thanks,
David. Very helpful!

Tia

Tia Murchie-Beyma wrote:
Glad you got it to work. I might have done something like this instead.
I think it does that same thing.
Dim rst as recordset
set rst = _
currentdb.openrecordset("Next_Household_Id",dbopen dynaset)
If rst.recordcount > 0 then
rst.edit
else
rst.addnew 'in case the table was never used before
endif
Me![Household_Id] = format(rst(0),"00000000)
rst(0) = rst(0) + 1
'alternate as it's more readable
'rst!Next_Id = rst!Next_Id + 1
rst.update
rst.close


I don't know why the form's OnCurrent event would always use the value
of the table. It might be useful for a New Record.
If Me.NewRecord then
....
endif

With your current code, it seems you update the value by one for table
"Next_Household_Id" every time a form presents a record, whether or not
it is an existing or new record.

Wow. That's kind of going around the barn.

You should be able to do all that with code like the following. UNTESTED
AIRCODE follows

Private Sub Form_Current()
Dim Current_Household_Id_Int As Long
Dim Update_Sql As String

'Find out next household id
Current_Household_Id_Int = Nz(DLookup("Next_ID","Next_Household_Id"),1)

'Write household id into the form
Me![Household_Id] = Format(Current_Household_Id_Int,"000000")

'Add 1 to next household id table
Update_Sql = "UPDATE Next_Household_Id " & _
" SET Next_Household_Id.Next_Id = " & Current_Household_Id_Int + 1

DoCmd.RunSQL (Update_Sql)

End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tia Murchie-Beyma wrote:
--

Tia Murchie-Beyma <tiamurch (AT) gmail (DOT) com> wrote in
news:991f3fcc-e30b-4f86-81c6-837ff3708dfc (AT) c33g2000yqm (DOT) googlegroups.co
m:

Well, if you're not using SEEK you don't need to set the INDEX
property, which is used only by SEEK.

If you did this:

Set dbs = DBEngine.OpenDatabase("[path/name of back end]")

....the code would still work. It's because you're using CurrentDB to
initialize your database variable that the linked tables cause
problems.

I find it very odd that you've got a database variable all defined
here and yet you use the very dangerous DoCmd.RunSQL to execute your
SQL instead of dbs.Execute Update_Sql, dbFailOnError (you would need
to add an error handler, though).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/