Numbering Issue

[jfgambit]

I have a form that contains a listbox with Emergency Contact information for employees. When a user selects one of the rows in the listbox the detailed emergency contact information is populated on the form (Name, Address Phone number, Order Number, etc). One of the detail items is Order Number...as in the Order in which you call if the employee losses a leg. I have code behind the form when adding a new contact that will default the number to 1 or find the Dmax of the Order Number and add 1 to it. Works great.

Now here is a problem I can't seem to solve. Let's say "Mike" gets divorced and his #1 contact is no longer his wife. Let's say his Mom should be number 1, or let's say he wants his ex off all together, what code can I use on the AfterUpdate event of the Order Number or on my Delete Record that would take the remaining Contacts and reorder them to the correct numbers?

Example:

1. Ex-Wife
2. Mom
3. Dad

We change the Order Number of Ex-Wife to 2 and the code reorders the list to

1. Mom
2. Ex-Wife
3. Dad

OR

1. Ex-Wife
2. Mom
3. Dad

We delete the Ex-Wife and the code reorders the list to

1. Mom
2. Dad

I just don't want them to have to manualy re-order all the contacts.

 

[Mile-O]

Essentially what you are after is a Bubble Sort, if I remember its name properly.

I think you'd have to use a little DAO or ADO to put all of the order numbers relevant to that employee into an array first, and also make an array for other details that will be shifting.

Treat the following as an outline rather than actual code.

Dim intInner As Integer, intOuter As Integer ' loop counters
Dim intValue() As Integer, intTemp As Integer

For intOuter = 2 To ListTotal Step 1
    For intInner = 1 To (ListTotal-1) Step 1
        If intValue(intOuter) > intValue(intOuter + 1) Then
            intTemp = strB(intOuter + 1)
            intValue(intOuter + 1) = intValue(intOuter)
            intValue(intOuter) = intTemp
        End If
    NextInner
Next Outer

 

[jfgambit]

Thanks Mile...I'll give it a whirl..