I have a table with a composite index on three fields: RDAY, RSM, and RSEQ (each sorted ascending.)
I am trying to renumber the RSEQ field in increments of 5, while preserving the order the records are currently sorted in. Here is the code I am using: - Public Function Resequence(TableName)
-
Dim rs As Recordset
-
Set rs = CurrentDb.TableDefs(TableName).OpenRecordset
-
rs.Index = "SortOrder"
-
rs.MoveFirst
-
Do While Not rs.EOF
-
If dDay <> rs.Fields("RDAY") Or dSM <> rs.Fields("RSM") Then dSeq = 0
-
dSeq = dSeq + 5
-
dDay = rs.Fields("RDAY")
-
dSM = rs.Fields("RSM")
-
rs.Edit
-
rs.Fields("RSEQ") = dSeq
-
rs.Update
-
rs.MoveNext
-
Loop
-
End Function
Unfortunately, this code causes an infinite loop. I think because RSEQ is an indexed field, and we're changing its value, the order of the records changes and we never get to EOF. Currently it only changes values for the first value of RSM.
If I remove RSEQ from the index, the loop completes, but the order is not completely preserved.
How can I move through the records in their original order, and change the RSEQ field without reordering the records?
5 1736
Hi. Instead of trying to do this in code for such a one-off task I'd generate a temporary table listing the values of all three fields, with a fourth field for the new value of RSEQ which you wish to set, and update the RSEQ field to the new value by joining these tables accordingly.
It should be straightforward to use a query to extract your current composite key values into Excel, say, then create the new key value as a fourth column and paste the four columns back as a new table into Access.
For simplicity I have just given simple table and field names in the example below, with F1 to F3 for your existing fields and F4 for the replacement value of F3: - UPDATE tblTestUpdate INNER JOIN tblUpdateNewKey ON
-
-
(tblTestUpdate.F3 = tblUpdateNewKey.F3) AND
-
(tblTestUpdate.F2 = tblUpdateNewKey.F2) AND
-
(tblTestUpdate.F1 = tblUpdateNewKey.F1)
-
-
SET tblTestUpdate.F3 = [f4];
-
You would have to be careful that in renumbering your existing field you do not create duplicates of some existing values which would violate the integrity of the composite key and therefore not be updateable. For example, you could not renumber a composite value of 1, 1, 1 to 1, 1, 5 if the value 1, 1, 5 already exists in your table.
There are two advantages to this approach. Firstly, in joining to the existing composite key value there are no issues with changing what is already indexed (as long as there are no key violations), and secondly you have a physical mapping table available showing what changes were made which you can use to undo those changes if need be.
-Stewart
ADezii 8,834
Recognized Expert Expert
I started to re-write the code to see if it was a Syntax issue, when I realized that you have no closing End If to the If Statement in Line #7. This could definitely be causing the Infinite Loop problem. In any event, I'll post the alternate code which has the single advantage of always being sure that the Records will be in proper order prior to processing. Don't forget to add the End If (LOL). - Public Function Resequence(TableName)
-
Dim MyDB As DAO.Database
-
Dim rs As Recordset
-
Dim strSQL As String
-
-
'Let's be absolutely sure of the Sort Order
-
strSQL = "SELECT * FROM " & TableName & " ORDER BY [RDAY], [RSM], [RSEQ];"
-
-
Set MyDB = CurrentDb
-
Set rs = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
-
-
rs.MoveFirst
-
-
With rs
-
Do While Not .EOF
-
If dDay <> rs.Fields("RDAY") Or dSM <> rs.Fields("RSM") Then dSeq = 0
-
dSeq = dSeq + 5
-
dDay = rs.Fields("RDAY")
-
dSM = rs.Fields("RSM")
-
rs.Edit
-
rs.Fields("RSEQ") = dSeq
-
rs.Update
-
End If
-
rs.MoveNext
-
Loop
-
End With
-
-
rs.Close
-
Set rs = Nothing
-
End Function
P.S. - As well as Code Tags, Indenting is always critical in posting code. Had the code been properly Indented, the missing End If would have been quite obvious. Just food for thought.
Hi ADezii. The If in the OP's first post is an example of a single-line IF statement which does not need an End If. It is an allowed form of syntax, as the MS-Help entry shows (asterisked line below):
Syntax
If condition Then [statements] [Else elsestatements] **
Or, you can use the block form syntax:
If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatement s] ...
[Else
[elsestatements]]
End If
-Stewart
ADezii 8,834
Recognized Expert Expert
Thanks Stewart, that one went right over my head! (LOL).
Stewart: Thanks for answering, I do like your approach. Unfortunately, this is not a one off process, but something we will have to do to three or four tables periodically. (I have no control over the source data, as it comes from another company's software.)
ADezii: I don't know why running off a Dynaset recordset hadn't occured to me - I used to do them for everything until I realized you don't really need to for working directly on a table. Changing the recordset type got around the index problem and the results are perfect.
PS. Sorry about the IF thing. I hate using the block form when the actual code being carried out is so tiny, but I think we've demonstrated that is does add to the code's readability by splitting it out.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: BStorm |
last post by:
Does anyone know the best way to update a DataTable row column using
specific values for a multipart primary key?
For example, updating an OrderAmt column in an OrderDetail table where the
primary key consists of an OrderNo and DetailSeqNo?
|
by: Amir Zicherman |
last post by:
hi,
i have a btree index on col1 in table1. The column has either values
1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
there are about 20 million rows in the table. when i do a "select *
from table1 where col1=4" it takes very long time to get back to me
(around 4 minutes). why is it taking so long if i have an...
|
by: astro |
last post by:
I have a non-updatable column in a SQL:-Server view that represents a 1-to
{0,1} relation with a child table.
I want the end-user to be able to change this value. This view is generated
with the following SQL code:
create view dbo.v_leads_person
AS
SELECT
p.personID,
|
by: Martin R |
last post by:
Hi,
How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?
thx,
Martin
*** Sent via Developersdex http://www.developersdex.com ***
|
by: =?Utf-8?B?Y2NfY3Jhc2g=?= |
last post by:
Hello all,
This is probably a simple issue but since I am a newbie I am really stuck on
it. I have been developing some code and I was using rowPosition to pull data
from a Access Data base and I changed it to something that will read through
and match up my primary keys (MR#). Anyway this works fine as long as I have
data in every field in...
| |
by: sourabhmca |
last post by:
Hi friends,
I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.
|
by: sk27ahmed |
last post by:
Hi
Any one can show me how to access datagridview column value on column checked unchecked.
I create one column in datagridview of type checkbox,and on button click i write code to select all rows in the datagridview.
The code for select all is
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
...
|
by: billa856 |
last post by:
Hi,
I am trying to insert Null value in column(ShipDate) in my table.That column(ShipDate)'s type id date/time and format is short date.
I am using "" to insert Null in that column(ShipDate) but it shows warning that
customer can't append all the records in the append query.
Customer set 1 field(s) to Null due to a type conersion...
|
by: Ben |
last post by:
Hi,
With this code, when i click on Update button, the value updated of field
"wa" in the table is NULL.The update of the other field (not in a DD) works
good.
It seems that the selectedvalue of the dropdownlist (which exists, i tested
it with response.write) is not used as updating value.
Any idea why?
|
by: Mark A |
last post by:
Suppose a table like this:
CREATE TABLE TABLE_A
(COL_1 INT NOT NULL PRIMARY KEY,
COL_2 CHAR(1) NOT NULL);
and a SQL statement like this:
UPDATE TABLE_A SET COL_1 = ? AND COL_2 =? WHERE COL_1 = ?;
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |