Solved

Error: Object Invalid or No Longer Set with EXISTING table

Posted on 2002-03-21
8
2,027 Views
Last Modified: 2007-11-27
In my Access application, there is a temporary table that is regenerated through a make-table query every time I need it (a table of 40 random questions from a larger table of 2500). The make-table SQL is run from VBA code.

After creating the table, I desperately need a sequentially numbered field, simply to have the records numbered 1 through 40. (There is a very good reason to need this number - trust me.)

Because I know of no other method to get a sequentially numbered field, I wrote the following bit of VBA:


Dim tdf As DAO.TableDef, rst As DAO.Recordset
Dim fld As DAO.Field
Dim i As Integer
   
Set tdf = CurrentDb.TableDefs("temptable")
Set fld = tdf.CreateField("seqnr", dbInteger)
tdf.Fields.Append fld

Set rst = CurrentDb.OpenRecordset( _
                          "SELECT * FROM temptable")
With rst
   Do Until .EOF
      i = i + 1
      .Edit
      .Fields("seqnr").Value = i
      .Update
      .MoveNext
   Loop
   .MoveFirst
End With


My problem is this: on the line where I Set the tdf object variable, no error is reported. But on the next line, I get the 'Object invalid or no longer set' error (3420). Now there is no doubt at all that the "temptable" table exists. So my questions are:

1. How come I get this error on this very straightforward piece of code? Why can't I refer to a TableDef in this very simple manner?

2. Is there a smarter way of getting a sequentially numbered field in my data? I would much prefer an extra column in my queries that numbers the result records, but have no clue. And an AutoNumber field is no option, since there is already an AutoNumber field in my temptable...

Double points for two answers...
0
Comment
Question by:FlorisMK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6885324
Hi florisMK,

Try the following method, it should fill the numbers upon declaration... (no need to loop through the records, as it uses the AutoNumber mechanism):

Dim tdf As DAO.TableDef, rst As DAO.Recordset
Dim fld As DAO.Field
Dim i As Integer
   
Set tdf = CurrentDb.TableDefs("temptable")
Set fld = tdf.CreateField("seqnr", dbInteger)
fld.Attributes = fld1.Attributes + dbAutoIncrField
tdf.Fields.Append fld



Please resolve the following questions:

Windows 2000 topic area
http://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20225639 dated 11/03/01
http://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20181512 dated 09/11/01

Microsoft Access topic area
http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20181835 dated 09/12/01

JavaScript topic area
http://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20215186 dated 10/26/01
Please notice that the above is a link Q which you can delete as no one yet have replied with a comment!

http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20241875 dated 11/29/01

If you need any forther assistance resolving the above Qs, please post a 0 (zero) pionts question at the Community Support Topic Area [ http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt ], paste the link to the qestion you need help on, an add a short description of what you need, and a moderator will help you out in no time...

Thanx!

Nosterdamus
0
 
LVL 2

Author Comment

by:FlorisMK
ID: 6885525
AH - that would be half the solution! Should have thought of that one myself - create an AutoNumber field! But it doesn't solve the main problem: I get error 3420 when referring to the TableDef, so I don't even get the chance to create the new field:

Set tdf = CurrentDb.TableDefs("temptable")
' Executed with no error, but...

' setting a watch on the tdf variable shows me that
' the 'Object is invalid or no longer set', and

Set fld = tdf.CreateField("seqnr", dbInteger)
' generates RTE 3420: Object invalid or no longer set

This while I know for a fact that the table is present and current (setting a watch on CurrentDB shows me the temptable in the TableDefs collection).

PS: Will look into those other questions...
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 6891206
Effectively when you're only interested in having a sequence number, all you need is an extension to your query like.
select dcount("questionID","tblQuestions","questionID<=" & questionID), field1, field2 from tblQuestions;

This will enumerate the questions, assuming the questionID is unique. Based on this query you can create again a maketable query...

Nic;o)

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:nico5038
ID: 6891212
Microsoft has an article about ranking in queries where the use a sub-query to do the same as the above DCOUNT:
http://support.microsoft.com/support/kb/articles/Q208/9/46.ASP

Nic;o)
0
 
LVL 2

Author Comment

by:FlorisMK
ID: 6892338
Nico, that is an excellent solution to the enumeration problem - obviously I'm not too familiar with domain functions (-:

It bypasses the problem of the error referring to the tabledef, so I'm inclined to give you points. Want to give it a few more days though, because I hope there is an obvious answer to the error 3240 problem...
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6892364
I can look into that, but then you'll need to compact and zip the .mdb and drop it in my nico5038 mailbox "at"  yahoo.com

Nic;o)
0
 
LVL 2

Author Comment

by:FlorisMK
ID: 6899158
I'll just accept your domain function as a solution to my problem - thanks!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6899579
You're welcome, success with the application !

Nic;o)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question