Prev: Can't delete module
Next: Setting IE Cookies - Windows 7
From: Tia Murchie-Beyma on 21 Jun 2010 21:26
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
From: Tia Murchie-Beyma on 21 Jun 2010 21:36
> I've never declared a type-type recordset nor used SEEK.

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
From: Salad on 21 Jun 2010 23:06
Tia Murchie-Beyma wrote:
> 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

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",dbopendynaset)
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.
From: John Spencer on 22 Jun 2010 08:31
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:
> 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

--
From: David W. Fenton on 22 Jun 2010 19:06
Tia Murchie-Beyma <tiamurch(a)gmail.com> wrote in
news:991f3fcc-e30b-4f86-81c6-837ff3708dfc(a)c33g2000yqm.googlegroups.co
m:

> Thanks! It was INDEX! Like SEEK, it doesn't work with those
> linked tables, I guess.

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

> 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.)

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.

> 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

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/
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Can't delete module
Next: Setting IE Cookies - Windows 7