Incremental Numbering on a Continuous Form

[Tim Bedborough]

Hi all

I have a 'to do' list (on a continuous form) where I want to change the order of things periodically. I am using an incremental numbering system using 10, e.g. item 1 = 10, item 2 = 20 and so on. If I need to squeeze something in I can use e.g. 22 or I can manually renumber everything in 10s again.

What I would like to do is press a button so that all the list number items ([ListNo]) are renumbered starting from 10 and increasing in increments of 10.

I'm looping as per code below but it's definately not happy. I have had a 'Google' but I'm struggling. Can any one help please?

Thanks



Private Sub btnRenumber_Click()

Dim rs As Recordset
Set rs = Me.RecordsetClone

Dim Val As Integer
Val = Me.ListNo

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Val = 10

Do Until rs.EOF = True
Val = Val + 10
rs.MoveNext
Loop

End If

End Sub

 

[vba_php]

hello Tim,

I'm not sure if you can grab anything from the this sample code, but it might be worth a look....

 

[Tim Bedborough]

Thanks Adam, please can you point me to a web link or pdf. I think our firewall is being a bit keen.
Many thanks.

 

[vba_php]

............................

 

[theDBguy]

Hi Tim. You could also check out this demo.

 

[Tim Bedborough]

Thanks DBGuy. 'Move up and down' method bugged out. Neither what I am after but thanks anyway. I'm looking for an automated way of doing all records rather than changing one at a time.

 

[theDBguy]

Thanks DBGuy. 'Move up and down' method bugged out. Neither what I am after but thanks anyway. I'm looking for an automated way of doing all records rather than changing one at a time.

Hi Tim. I would appreciate if you could elaborate on what "bugged out," so I can fix it. It will help not only me, but also those who might download the demo in the future.


Also, I'm pretty sure you didn't mean you want to change "all" records; because if you had 10 records and inserted something in the middle, there's no need to change the first record up to the middle one, correct?


Regarding the demo, you are right that the move up/down approach does not apply to you, but the dropdown one should (and you can also enter a number instead). For example, here's the initial records order in the demo.

When I changed the sort order for Custodian from 6 to 4, records 4 through 8 got changed.

So, the demo might not be exactly what you wanted, but I was hoping it would help you get started in the right direction. Cheers!

 

[Gasman]

@theDBguy
I downloaded the demo, but it mentions MVF and Attachments?
It looks nothing like your pics?

 

[GinaWhipp]

Hmm, thinking this might help...
https://www.access-diva.com/vba7.html

 

[theDBguy]

@theDBguy
I downloaded the demo, but it mentions MVF and Attachments?
It looks nothing like your pics?

Hmm, that's interesting. Did you click the link in post #5?

 

[Gasman]

Hmm, that's interesting. Did you click the link in post #5?

Sorry, being a dumbass.
I inadvertently clicked on another of your demos when unzipped, that actually mentions MVFAttachments if I looked closer.:banghead::banghead:

 

[theDBguy]

Sorry, being a dumbass.
I inadvertently clicked on another of your demos when unzipped, that actually mentions MVFAttachments if I looked closer.:banghead::banghead:

No worries. I do the same thing some times. Cheers!

 

[Gasman]

Your demo appears to do exactly what the o/p wanted.?

Worked for me anyway.

Thank you.

 

[theDBguy]

Your demo appears to do exactly what the o/p wanted.?

Worked for me anyway.

Thank you.

Hi. Thanks! I was into SharePoint at the time I made it, so that's where the inspiration came from.

 

[Tim Bedborough]

Hi DBGuy

I wasn't meaning to be dismissive, sorry if it came accross this way. These forums are very helpful and I'm grateful to anyone who posts a reply. Here is a smiley face to prove it.....

Just trying to go quicker than I should I think.

Bugs only because I didn't read the instructions....whoops. I needed to 'save as' to make it an editable database. It works OK.

I don't really want to have to change each individual item, more a case of making the first item in the list start with the number 10 and then everyone after it being incremental in 10s.

So if I changed list to:
10 Hi
20 Hows things
30 Fine thanks

and added in 25

I then wanted it all to be automated to renumber 10, 20, 30 and 40.

Thanks

 

[theDBguy]

Hi DBGuy

I wasn't meaning to be dismissive, sorry if it came accross this way. These forums are very helpful and I'm grateful to anyone who posts a reply. Here is a smiley face to prove it.....

Just trying to go quicker than I should I think.

Bugs only because I didn't read the instructions....whoops. I needed to 'save as' to make it an editable database. It works OK.

I don't really want to have to change each individual item, more a case of making the first item in the list start with the number 10 and then everyone after it being incremental in 10s.

So if I changed list to:
10 Hi
20 Hows things
30 Fine thanks

and added in 25

I then wanted it all to be automated to renumber 10, 20, 30 and 40.

Thanks

Hi. Understood and no worries. Although the demo only increments each record by 1, there's no stopping you from modifying it to make it update the records by 10. On the other hand, if you use this routine in your app, then does it really matter what the increment is, since it will automatically renumber the whole table anyway? Just a thought...

 

[Tim Bedborough]

DBGuy. OK thanks. End of day for me and brain hurts. I'll have a look tomorrow and see if I can make it do what I want.

 

[theDBguy]

DBGuy. OK thanks. End of day for me and brain hurts. I'll have a look tomorrow and see if I can make it do what I want.

Okay, good luck. Please let us know how it goes. But also, please consider what I said about the need to modify the code. If you can live with using 1 as the increment, then you won't have to worry about figuring out how to modify the code. Cheers!

 

[Tim Bedborough]

Hi DBGuy.

Well I've had a go! Stuck with 1 as increment numbering. The code worked fine. Made the name changes with table and control/field bespoke to my database. Added the module (not had to bother with 'public ' routines but added it OK). Some of the code was beyond anything I've tackled to date but all made sense (once you know how!).

BUT

My fault as I didn't make it clear. I have 1 to many relationship with my to do lists. So I have a number of to do lists (no numbering, just filtered by date) then they have related tables and sub forms to deal with their own numbered lists (lists within lists). Your code works fine if you only have 1 'to do' list. I have loads all held in the same table so my SortOrder (ListNo) may have lots of duplicates. Using your code I have 'to do' lists with only a few items in them but the list number could be e.g. 1, 456, 734, 899 etc rather than 1,2,3,4. It's all sequential (low to high) and in order for every list I have, just not incremental. I've reverted to plan A and sticking with my manual sequential numbering in 10's.

Many thanks for your tip though. It works well for a single list and I learned something too.

 

[theDBguy]

Hi. If you can post a sample copy of your db with test data, we might be able to help you figure it out.