VBA - Reorder Values in multiple fields

Hi Experts; I need some help with renumbering values.

Example:

Field A: 1
Field B: 2
Field C: 3
Field D: 4
Field E: 5

If user changes Field B to 4 they want the following results:

Field A: 1
Field C: 2
Field D: 3
Field B: 4
Field E: 5

Any help will be appreciated!
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarWestCommented:
I think you can replace where the ordernumber is grater than or equal changed number with existing number + 1
aikimarkCommented:
You might use the Choose() or Switch() functions or one of the methods I describe in this article:
http://www.experts-exchange.com/articles/14179/Big-Choices-Dynamic-columns-with-A-Better-Choose-function.html

Your example isn't entirely clear.  If the user changes one field, that doesn't automatically change any other fields.

If this is a simple field ordering, you should be able to change the field's OrdinalPosition property with VBA code.
Gustav BrockCIOCommented:
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Eileen MurphyIndependent Application DeveloperAuthor Commented:
The problem is these are all individual fields on a form and not records in a list.  The other caveat is that some fields are blank or null which is valid.
Gustav BrockCIOCommented:
Then they are not fields but controls (textboxes).

Use the AfterUpdate event of these to call one function that reads all the textboxes (ignoring Null) and returns your ordered list.

/gustav
PatHartmanCommented:
Are you asking to have controls on a form shift based on the value in them?  This design needs to be changed.  The list on the form should be records in a table.  Then one of the techniques already suggested will work for you.  It sounds like you have an unnormalized table and there is no tool in Access that is going to make working with that easy.  Access is a relational database.  It is not a spreadsheet.
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Understood - I inherited this project and they want to keep it this way despite my desire to change it. Hundreds of queries and reports depend on these fields, as they are, and the client doesn't want the expense of having me go through every one to make the appropriate changes. Anyway - I attached a sample db with explanations on the form for each record. Can you take a look at that for me? I am trying to implement the code that was suggested -- and am not getting the desired results. I'm still working on it too.

Thanks!!
Database2.zip
aikimarkCommented:
The fields are bound to controls by their name, not by their ordinal field number.
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Unfortunately. I'm getting closer as I dig through this -- If I figure it out I'll post the result. I really appreciate all of your help.
aikimarkCommented:
I am trying to implement the code that was suggested -- and am not getting the desired results.

Please let the experts know what you are trying and what/where it is giving you problems.
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Hi Experts; The code is behind the button on the form included in the sample db -- where I was trying to implement the code...
Gustav BrockCIOCommented:
Given your sample data, I'm about to say this is not possible, as I can't see no common rule for the shufling.

/gustav
FarWestCommented:
just to make sure
do you want to order the text boxes positions based on their new ordered values?
is it ok for user to press renumber button or you want it event driven?
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I'm calling the reorder code after each value is changed, but prompt the user if the auto-renumber should take place in the event they wish to manually reorder them.
Eileen MurphyIndependent Application DeveloperAuthor Commented:
** Added Module Level integer variables representing each field's Old Value

** For each of the fields I added a BEFORE_UPDATE sub to retain the old value **
Example:
Private Sub AdjOrigOrderNo_BeforeUpdate(Cancel As Integer)
        intAdjOrigOrderNo = Nz(Me.AdjOrigOrderNo.OldValue, 0)
End Sub

** For each of the fields I call the below function using the below syntax. The intAdjOrigOrderNo represents the OldValue.
 ** The strField represents the field's name. Ex: AdjOrigOrderNo

Call RenumberPOs("AdjOrigOrderNo", Me.AdjOrigOrderNo, intAdjOrigOrderNo)

Private Function RenumberPOs(strField As String, intNewVal As Integer, intOldVal)

    DoCmd.RunCommand acCmdSaveRecord

    If MsgBox("Renumber PO's?", vbYesNo) = vbNo Then Exit Function
   
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "trackchangePO" Then
            If Ctrl.Name <> strField Then
                If Ctrl.Value <> 0 And Not IsNull(Ctrl.Value) Then
                   
                    If intOldVal > intNewVal Then
                       
                        If Ctrl.Value < intNewVal Then
                            'do nothing
                        ElseIf Ctrl.Value > intOldVal Then
                            'do nothing
                        ElseIf Ctrl.Value >= intNewVal Then
                            Ctrl.Value = Ctrl.Value + 1
                        End If
                       
                    ElseIf intOldVal < intNewVal Then
                       
                        If Ctrl.Value < intOldVal Then
                            'do nothing
                        ElseIf Ctrl.Value <= intNewVal Then
                            Ctrl.Value = Ctrl.Value - 1
                        ElseIf Ctrl.Value > intNewVal Then
                            'do nothing
                        End If
                    End If
                   
                End If
            End If
        End If
    Next
   
    DoCmd.RunCommand acCmdSaveRecord
   
    Exit Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I'm not sure if this is the appropriate procedure - I figured this out on my own. I'm happy to split the points among everyone who tried to help me -- but I wanted to insure that the correct response be available to someone searching for answers in the future. ~ Eileen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.