By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Renumbering two fields in different tables at once

P: 49
Ok I have two tables both with an ID field, whenever a record is deleted these need to be renumbered to keep corresponding data. Here is the code I am using to do this:
Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database
  2.     Dim rs2 As DAO.Recordset
  3.     Dim rs3 As DAO.Recordset
  4.  
  5.     Set db = CurrentDb()
  6.     Set rs2 = db.OpenRecordset("PrinterCartInventory")
  7.     Set rs3 = db.OpenRecordset("CartUse")
  8.     rs2.MoveFirst
  9.     i = 1
  10.     Do Until (rs2.EOF And rs3.EOF)
  11.  
  12.         rs2.Edit
  13.         rs2!ID = i
  14.         rs2.Update
  15.         rs3.Edit
  16.         rs3!ID = i
  17.         rs3.Update
  18.         i = i + 1
  19.         rs2.MoveNext
  20.         rs3.MoveNext
  21.     Loop
  22.  
Whenever it runs it gives me an error saying no current record. I tried commenting out stuff to see where it was hanging up at and it is fine until rs3!ID=i. Does this mean this table needs focus to be able to do this or what?
Feb 19 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
It's not wise to change an ID field and seldomly needed.
The only option would be to add an additional field and update that to reflect the sequence when you need a speedy solution, the more secure query solution would be:

select ID, DCount("ID","tblX","ID<=" & ID) as Sequence from tblX;

This will give you the numbering needed, but once again, changing unique keys is often disastrous for the data's history. Why this need ?

Nic;o)
Feb 19 '07 #2

ADezii
Expert 5K+
P: 8,597
Ok I have two tables both with an ID field, whenever a record is deleted these need to be renumbered to keep corresponding data. Here is the code I am using to do this:
Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database
  2.     Dim rs2 As DAO.Recordset
  3.     Dim rs3 As DAO.Recordset
  4.  
  5.     Set db = CurrentDb()
  6.     Set rs2 = db.OpenRecordset("PrinterCartInventory")
  7.     Set rs3 = db.OpenRecordset("CartUse")
  8.     rs2.MoveFirst
  9.     i = 1
  10.     Do Until (rs2.EOF And rs3.EOF)
  11.  
  12.         rs2.Edit
  13.         rs2!ID = i
  14.         rs2.Update
  15.         rs3.Edit
  16.         rs3!ID = i
  17.         rs3.Update
  18.         i = i + 1
  19.         rs2.MoveNext
  20.         rs3.MoveNext
  21.     Loop
  22.  
Whenever it runs it gives me an error saying no current record. I tried commenting out stuff to see where it was hanging up at and it is fine until rs3!ID=i. Does this mean this table needs focus to be able to do this or what?
I'm assuming that both Tables have exactly the same number of Records, because if they didn't, this code would never work. If either Recordset has more or less Records then the other, EOF would turn True for one Recordset and not the other. The MoveNext for the Recordset where EOF = True would now generate a Runtime Error. If in fact they do have the same number of Records, try an explicit MoveFirst on rs3:
Expand|Select|Wrap|Line Numbers
  1. rs2.MoveFirst
  2. rs3.MoveFirst
P.S. Nico gave you some excellant advice - you should take it.
Feb 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.