move top record to the bottom in table

[Hargreavesk]

Hi,

I have a table with 5000 cheque numbers in it. When I pull the top number into another table, I would like that number to be moved to the bottom of the table. Is there a way to do this?

Thanks

Kate

 

[LittleSmudge]

I think I've supplied a working solution in your other thread on the subject thread700-251550 LS

 

[Hargreavesk]

LittleSmudge,

Thanks for your suggestions but will it work, If I need to keep looping the numbers? My Cheque Numbers start at 970001 and finish at 975000 but once I have reached 975000, I'd like to revert back to 970001 which will have a different id number assigned automatically by my table. I have tried adding 1 to the last number in the table but this is not going to work when I the numbers revert.

Regards

Kate

 

[MichaelRed]

There appears to ne some lack of understanding of relational data base understanding here. One of the major concepts involved in relational data bases is the concept of abstraction which (overly simplified) says that the programer/user should NOT need to know the details of the organization of the data base. This includes the "ordinal position" of records and fields in the various tables. The 'simple' request to FORCE a record to a specific position is a BIG RED FLAG, to the fact that the data base is being forced into an un-natural situation. Go back the beginning of the exercise and start over.


MichaelRed
m.red@att.net

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

 

[LittleSmudge]

Yes Hargreavesk you can still get it to work

Two options:-
1) Think of the cheque number in two parts. The "97" prefix - That will never change
followed by the index number of the cheque.

So do as I suggested with the index number, then whenever you use the cheque number prefix it with "97".

2) Modify the code I supplied last time to
If value = 975000 Then
Set value in recordset = 970001
Else
Increment value in recordset
End IF


Thinking about it some more as I type I'd suggest option 2.

Option 1 has too many problems around 'leading zero' issues etc.


G LS