Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error: Object Invalid or No Longer Set with EXISTING table

Posted on 2002-03-21
8
Medium Priority
?
2,044 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 800 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

597 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