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)
Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM temptable")
Do Until .EOF
i = i + 1
.Fields("seqnr").Value = i
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...