Need help with code

[dvannoy]

I have a field in a table called id. I donot(cannot) use an autonumber etc.. for several different reasons.. what i need to do is, append records from a linked table into a temp table. with the field id counting the records..so if there were five records the first id field would have a 1 in it. then the second would have a 2 etc.. until the end of the recordset. i put together the code below..but it will not loop through the recordset. it will only work for the first record. before append the data from the linked table I will run a delete query to delete all records from the temp table. so , the ID # always starts with 1.


Dim Db As Database
Dim rs As Recordset
Dim x As Integer

Set Db = DBEngine.Workspaces(0).Databases(0)
Set rs = Db.OpenRecordset("tblName", DB_OPEN_DYNASET)

rs.MoveFirst

Do While Not rs.EOF
x = 1

For x = 1 To rs!ID
rs.Edit
If DCount("id", "tblName&quot = 0 Then
rs![ID] = 1
Else
rs![ID] = DMax("id", "tblName&quot + 1
End If
rs.Update
Next
rs.MoveNext

Loop


End Sub



Thanks in advance dvannoy@onyxes.com

 

[MichaelRed]

Hmmmmmmmmmmm,

All I see is the attempt to edit the record. None is ever added to the recordset, only the "Id" of the record is changed.

.Edit <> .AddNew

More trouble in loop lane - but you need to actually append some record to some table before worrying about that.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over