Tricky Sequencial re-numbering

[Mick99]

Hi I have a what I think is a pretty tricky request for help. The overview is that I have a SubForm that is basically a list of meeting items for an agenda. By Default I am using a table as a template to populate the agenda items and item numbers. The user can then add additional item and the item is sorted into the list in the righ position by simply refreshing.So the real problem is that I have had to add a field in to allow a manual sorting as sometimes agendas do not always follow the initial sequence, so at time this needs to be overwritten. Therefore this sort number has to be manually entered and it is quite painful after new items have been added to the agenda, for example from the screen shot if I add an item 3.1.1 than I would have to re-number everything in the sort field manually from 7 and upwards, this actual Agenda had 70+ items so It would take some time to do. So I am looking for a way that I can choose to press a control and have the entire sort col re numbered sequencially from number 1 through to the number of records. Once this is done i will trigger the append to put the data into the Master table. Which is the next part of the puzzle as further updates are required I need to trigger the same re-numbering specifically for the agenda being viewed. Any help would be greatly appreciated.

Cheers Mick

Agenda 1 Pic



Agenda 2 Pic

[CJ_London]

you don't necessarily need any code, just right click on the item no column and select sort.

if the recordsource to your form is just the name of a table change it to

SELECT * FROM myTable ORDER BY [Item No]

Not sure why you need the sort column

[Mick99]

Hi Ajax, If i sort by item number I get the following issue whereby the 1.0, then 10.0, 11.0, 12.0 etc as per the screen shot following the 1.0 so its out of wack. I suppose if i could get a way to sort this sequencially than I could use a new table to append the data to and use the Autonumber to re number the items before placing the data back into the master table.

Cheers Mick

[IrogSinta]

You could add a calculated field and sort by that:

Sorter: Format(Format(Int(Val([Item ID])),"000.") & Format(Int(Val(Mid([Item ID],InStr([Item ID],".")+1))),"00") & Format(IIf([Item ID] Like "*.*.*",Mid([Item ID],InStr(InStr([Item ID],".")+1,[Item ID],".")+1),0),"00"),"000.00\.00")

Ron

[Mick99]

Sweet as Ron!! I just have a data type mismatch any ideas on how to get around this?
I get this when I try to sort by this field.
Cheers Mick

[IrogSinta]

Do you have an ItemID that's Null perhaps?

[Mick99]

Spot on 1 entry Null, i fixed this and it worked straight away. I have added that calc to my subform but it won't let me sort as the option is greyed out.

Cheers Mick

[IrogSinta]

I'm confused with the sorter field in your image. Why doesn't it have a field name and why the dividing line doesn't go up to the header?

[Mick99]

Sorry, I just threw it straight into the form to see if it would work. I've made it look better now.

[IrogSinta]

I'm not sue why you can't sort by it. Post the recordsource of your form and maybe that could give a clue.

[Mick99]

I have just updated the table and changed the form field from a calculated field. The field now looks at the table and the sort works.

Cheers Mick

[IrogSinta]

Well, its good that it works but it's preferable not to save a calculated field. It should have worked just as well in a query.

[Mick99]

Hi sorry for my late responce, have been off sick. You are right and I am using this in a query which then appends the original table so things stay up to date. Whilst of have been off I was asked if we could use 10.1.1.1 so it's another level, I had a quick play but could seem to get this to work. Any help is greatly appreciated.

Cheers Mick

[CJ_London]

you need to multiple all sort fields by 10 or 100 to effectively set a new column - sounds like it is time for a UDF

Put this code in a module and call it from your query. SubParaLevels is the maximum number of levels of sub paragraphs in the entire document eg. if you have

1.1
1.2
2.1.1
2.1.2
2.1.3
3.1
3.2

then the maximum number of sublevels is 2

Public Function ParaSort(ByVal PNum As String, ByVal SubParaLevels As Integer) As Double
Dim Strarray() As String
Dim i As Integer
Dim NStr As String

    Strarray = Split(PNum, ".")
    NStr = ""
    
    For i = 0 To UBound(Strarray)
        NStr = NStr & Format(Strarray(i), "00")
    Next I

    For i = UBound(Strarray)  To Levels
        NStr = NStr & "00"
    Next I

    ParaSort = Val(NStr)

End Function

So you would call this in your query as

SortField: ParaSort([Item No],2)

or of you want a full automation so you don't need to worry about keeping track of the 2 try

SortField: ParaSort([Item No],DMax("len([Item No])-len(replace([Item No],'.',''))","[qry Master Data]"))

or

SortField: ParaSort([Item No],(SELECT Max(len([Item No])-len(replace([Item No],'.',''))) FROM [qry Master Data] AS T)) which may be a bit quicker

[Mick99]

Hi I'll do some more testing but at the min the sort order is still out of wack as per screen shot.

Cheers Mick