473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Renumbering two fields in different tables at once

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
2 1533
nico5038
3,080 Expert 2GB
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
8,834 Expert 8TB
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

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

Similar topics

5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
3
by: amywolfie | last post by:
This is an interesting one..... I have a field on FORM!FORMDATA (no, I did not name this) called KEY. There can be multiple KEYS per record (equates to FIELDS on a given DOCUMENT). The...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
0
by: elmo | last post by:
Hi, I have a transaction table that I need to update from a from. The form should be able to display information from different tables. I have a Learner table with Learner information. I also...
1
by: Christa Waggett | last post by:
Hi, I'm not a programmer but would appreciate some help with the following. I've been looking at various sites but cannot find the information I require. I have a table of strata plans and if we...
6
by: allyn44 | last post by:
HI--what I am trying to do is 2 things: 1. Open a form in either data entry mode or edit mode depending on what task the user is performing 2. Cancel events tied to fields on the form if I am in...
9
by: Fish Womper | last post by:
I am at best a part time developer of Access databases. I use Access 2.0, as this is all my employer has on its computers. Even so, to use this ancient version requires a fairly convoluted...
1
by: DeanL | last post by:
Hi everyone, Does anyone know if it's possible to link fields from a SQL Server db into an Access db and if so then how? I have a number of users with an Access db and they need a small amount...
21
by: no1zson | last post by:
I do not even know how to correctly ask this question. I have an item field in the code I am about to post. Simple intger meant to be an item number for a cd. The user enters this number. Over the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.