?
Solved

how to edit key-field values and avoid immediate resort

Posted on 2000-03-29
8
Medium Priority
?
234 Views
Last Modified: 2010-04-04
I'm using D4 with MS ACCESS 97 tables.
The table is indexed and the value of the indexed field is like a sequence number. Now I have to insert a set of new records into this table (at the position of the cursor in the DBGrid). To do so it is neccesary to edit all records which have a higher sequence number than the sequence number at cursor position, to make space for the new records.
My problem:
When I increase the key-field value of the first record after the cursor position (for example by 4), the changed record wil be resorted acc to the index and the method table.next will not find the second record after the original cursor position.
How can I avoid the resorting of the records until the modifications have been made?
Remark:
I have tried to start the modifications beginning from the bottom, but this works to slow.

does anybody have an idea??
0
Comment
Question by:GePh
  • 3
  • 3
  • 2
8 Comments
 
LVL 7

Expert Comment

by:Motaz
ID: 2668010
Befor you modify the all next records save current position using:

P:= Table1.GetBookMark;


After modifing all records return to that position again using:

Table1.GotoBookmark(P);

P: String;


0
 

Author Comment

by:GePh
ID: 2668072
ok, with bookmark I am able to go back to the original cursor position, but this does not solve my problem to modify all the following records.  
0
 
LVL 7

Expert Comment

by:Motaz
ID: 2668112
If you want to modify the following records write this:

Incerement:= 1;
while not Table1.Eof do
begin
  Table1.Edit;
  Table1.FieldByName('YourField').AsInteger:= Table1.FeildByName('YourField').AsInteger + Increment;
  Table1.Post;
  Table1.Next;
end;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GePh
ID: 2668182
That is what I tried, but this doesn't give the expected result.
Example:
2o records, the value of the key field is 1...20.
Expecting I want to insert 4 records after position 10, I have to increase record 11 to 20 by 4.
Starting with record 11, the new value is 15. Immediately after posting this modification, the former record 11 is now at position 15 and table.next will bring me to record 16 and not to 12, as I want. So I am looking for a method to access the records 11 ... 20 step by step to be able to modify them
0
 
LVL 9

Accepted Solution

by:
ITugay earned 400 total points
ID: 2668254
Let suppose you need to make hole in table between 3 and 4

RCN  DATA
1     aaa
2     bbb
3     ccc
4     ddd
5     eee
6     fff

before inserting record execute SQL expression:
"UPDATE YOUR_TABLE set RCN=RCN+1 WHERE RCN > 3"
then reopen your table and goto to your last inserted record.

All it possible ONLY if RCN is NOT primary key.

Cheers,
Igor




0
 
LVL 7

Expert Comment

by:Motaz
ID: 2668311
You must go from the last record to the current such as:


for i:= 20 downto 11 do
begin
  Table1.Edit;
  Table1.FieldByName('YourField').AsInteger:= i + 4
  Table1.Post;
  Table1.Prior;
end;

This will ensure that the modified records will not moved form it's position.

Motaz
www.geocities.com/motaz1
0
 

Author Comment

by:GePh
ID: 2668500
Your proposal works fine!

Thank you for your help
0
 
LVL 9

Expert Comment

by:ITugay
ID: 2668534
Always wellcome;)
Igor.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question