ColumnOrder in Subform not performing as expected

[rhonda6373]

Hello,

I have an option group on the main form that filters what records are displayed on the subform. The option group is named st.

I need to also reorder the columns on the subform if option 3 is chosen. I have to reorder several columns.

I have tried just one to see if it worked. The issue is that it reorders correctly if option 3 is chosen, but it does not reorder back to the original order if option 1 is chosen.

Here is the code. Can anyone tell me what the issue may be?

If st.Value = 3 Then
    Forms!Default!NavigationSubform!Company.ColumnOrder = 1 
And 
Forms!Default!NavigationSubform![Account Number].ColumnOrder = 2
Else If
 st.Value = 1 Then
    Forms!Default!NavigationSubform!Company.ColumnOrder = 2 And Forms!Default!NavigationSubform![Account Number].ColumnOrder = 1
End If
Forms!Default!NavigationSubform.Requery
Me.Refresh

[NeoPa]

Rhonda.

Why are you posting this code? It can't possibly even run like that. It doesn't even fit standard VBA syntax.

I tried to work out what your code might be like but it's so wrong that isn't even possible.

Please post your actual code - as tested and copied directly from your module. With that, we may have a starting point that can be worked with.

[rhonda6373]

I am posting the code because it does run. Thanks for the harsh answer, but forgive me I have not had VB in 10 years. Also, this is someone else's database with existing code in it. I would not be so condescending. I will figure this out on my own somehow.

[NeoPa]

My intention is not to be condescending, so I apologise if I came across that way. My intention is to try to help. I cannot work with code that cannot run, as I need to be able to work out what it is you even expect to happen in order to suggest something likely to make that happen.

If I put a single word (And) on a line on its own in a VBA module then it will neither compile nor run (See your code line #3). I'm completely lost and I'm recognised as an Access MVP by Microsoft. The point I'm trying to emphasise here is that I do actually know quite a lot about VBA code and how to work with it.

If you say it runs then I'm sure you believe that. I have no suspicions on that score. I'm simply here to tell you that something doesn't add up. I'd stake my reputation on the statement that, as it's posted, that code cannot run in an Access VBA module.

I suspect you are not going to like what I'm saying, so I'm happy to back away from this thread if you would like me to. I'm not here to upset anyone. If, on the other hand, you'd like me to help try to sort out this very confusing problem, I'm happy to do what I can. The first thing I would suggest would be to look at it running using some of the debugging techniques - Debugging in VBA.

[rhonda6373]

No problem. I did not realize putting the and on a separate line would be such a problem -- I was just trying to make it easier to read. It is not coded on a separate line and the code does compile. I am not delusional (well maybe but not in this instance). As you can see, I am new to the site and don't fully understand how I am supposed to write these questions up.

Sorry for the confusion and the waste of any time. I do appreciate the help. I found another way to do this where it is easier to understand because there were over 15 columns that had to be hidden/re-ordered depending on what option was chosen.

[NeoPa]

I have no suspicion that you're delusional Rhonda. What may seem relatively straightforward to you, however, often has hidden complications you may not be aware of though. Such that what appears to be the case may not be when you look under the hood. Without experience in any field it's easy to miss important points. No one should criticise you for that.

I know of members who've posted code they were sure was working only to discover later that it was never actually being run. This can happen easily with event handling procedures. I only determined the problem when I got hold of an actual copy of their project.

I would add that a number of members attempt to make life easier for those reading the question by reformatting the code in a more readable layout. While this can be frustrating, the fact that the intention is to try to make our life easier is generally recognised and appreciated. SQL is a prime case where this makes good sense. Unfortunately (For other reasons fortunately) VBA has many syntax rules that code must adhere to in order to be valid.

With your posted code, and assuming (We try to avoid assumptions as they can lead us a merry dance) lines #2 through #4 are actually on a single executable line, what we have is something very different from what I suspect you believe.

Instead of assigning 1 to Company.ColumnO rder and then assigning 2 to [Account Number].ColumnOrder what you actually have is :

1. See if 1 and [Account Number].ColumnOrder have any bits in common (And is a bitwise operator that works on the two references each side of it).
2. Check if the previous calculated value is equal to 2.
3. Assign the previous (Boolean) result to Company.ColumnO rder

That being the case, you can see why I was convinced that something is very wrong with the code. I still suspect the actual code makes a lot more sense, but that's only a suspicion. I would need to see it directly to know for sure.

That said, it sounds as if you're happy with your alternative approach, and frankly, if you're happy then I am too. No need for more unless and until you decide you want to.

[rhonda6373]

Thanks, NeoPa. Understood. I will post the code as is in the future unless it is SQL.

Here is what I did in the event someone else is having issues with reordering columns in a subform based on a certain condition:

Select Case [st]
   Case "3"
      Forms!Default!NavigationSubform![Account Number].ColumnOrder = 1
      Forms!Default!NavigationSubform!Company.ColumnOrder = 2
      Forms!Default!NavigationSubform![CBS Lic #].ColumnOrder = 3
      Forms!Default!NavigationSubform!Product.ColumnOrder = 4
      Forms!Default!NavigationSubform![File Server].ColumnOrder = 5
      Forms!Default!NavigationSubform![Term Server].ColumnOrder = 6
      Forms!Default!NavigationSubform![Active Lic #].ColumnOrder = 7
      Forms!Default!NavigationSubform![Pat Reg].ColumnOrder = 8
      Forms!Default!NavigationSubform![Serv Conf].ColumnOrder = 9
      Forms!Default!NavigationSubform![Impl Date].ColumnOrder = 10
      Forms!Default!NavigationSubform![Software].ColumnOrder = 11
      Forms!Default!NavigationSubform!WS.ColumnOrder = 12
      Forms!Default!NavigationSubform![Hold Date].ColumnOrder = 13
      Forms!Default!NavigationSubform![Time Zone].ColumnOrder = 14
      Forms!Default!NavigationSubform![Notes].ColumnOrder = 15
      
      Forms!Default!NavigationSubform![Serv Conf].ColumnHidden = False
      Forms!Default!NavigationSubform![Impl Date].ColumnHidden = False
      Forms!Default!NavigationSubform![Software].ColumnHidden = False
      Forms!Default!NavigationSubform!WS.ColumnHidden = False
      Forms!Default!NavigationSubform![Hold Date].ColumnHidden = False
            
      Forms!Default!NavigationSubform![Frames Date].ColumnHidden = True
      Forms!Default!NavigationSubform!Frames.ColumnHidden = True
      Forms!Default!NavigationSubform![Cloud Ver].ColumnHidden = True
      Forms!Default!NavigationSubform![Live Date].ColumnHidden = True
      Forms!Default!NavigationSubform![Q Date].ColumnHidden = True
      Forms!Default!NavigationSubform![Interfaces].ColumnHidden = True
      
      Case Else
      Forms!Default!NavigationSubform![Account Number].ColumnOrder = 1
      Forms!Default!NavigationSubform!Company.ColumnOrder = 2
      Forms!Default!NavigationSubform![CBS Lic #].ColumnOrder = 3
      Forms!Default!NavigationSubform!Product.ColumnOrder = 4
      Forms!Default!NavigationSubform![File Server].ColumnOrder = 5
      Forms!Default!NavigationSubform![Term Server].ColumnOrder = 6
      Forms!Default!NavigationSubform![Pat Reg].ColumnOrder = 7
      Forms!Default!NavigationSubform![Interfaces].ColumnOrder = 8
      Forms!Default!NavigationSubform!Frames.ColumnOrder = 9
      Forms!Default!NavigationSubform![Frames Date].ColumnOrder = 10
      Forms!Default!NavigationSubform![Cloud Ver].ColumnOrder = 11
      Forms!Default!NavigationSubform![Live Date].ColumnOrder = 12
      Forms!Default!NavigationSubform![Active Lic #].ColumnOrder = 13
      Forms!Default!NavigationSubform![Q Date].ColumnOrder = 14
      Forms!Default!NavigationSubform![Time Zone].ColumnOrder = 15
      Forms!Default!NavigationSubform![Notes].ColumnOrder = 16
      
      Forms!Default!NavigationSubform![Serv Conf].ColumnHidden = True
      Forms!Default!NavigationSubform![Impl Date].ColumnHidden = True
      Forms!Default!NavigationSubform![Software].ColumnHidden = True
      Forms!Default!NavigationSubform!WS.ColumnHidden = True
      Forms!Default!NavigationSubform![Hold Date].ColumnHidden = True
      
      Forms!Default!NavigationSubform![Frames Date].ColumnHidden = False
      Forms!Default!NavigationSubform!Frames.ColumnHidden = False
      Forms!Default!NavigationSubform![Cloud Ver].ColumnHidden = False
      Forms!Default!NavigationSubform![Live Date].ColumnHidden = False
      Forms!Default!NavigationSubform![Q Date].ColumnHidden = False
      Forms!Default!NavigationSubform![Interfaces].ColumnHidden = False
   End Select
   
DoCmd.SetWarnings False

[NeoPa]

Excellent. Have a look at this reworked version. I've assumed that the [st] ComboBox is actually numeric, in line with your original post, but if it isn't (String instead) then the change is very simple and only to line #2. I've also assumed that, as there are no changes to the column orders of many (The first six) of these controls, they are already in the correct position by design.

With Forms!Default.NavigationSubform.Form
    Select Case [st]
    Case 3
        .[Active Lic #].ColumnOrder = 7
        .[Pat Reg].ColumnOrder = 8
        .[Serv Conf].ColumnOrder = 9
        .[Impl Date].ColumnOrder = 10
        .[Software].ColumnOrder = 11
        .WS.ColumnOrder = 12
        .[Hold Date].ColumnOrder = 13
        .[Time Zone].ColumnOrder = 14
        .[Notes].ColumnOrder = 15

        .[Serv Conf].ColumnHidden = False
        .[Impl Date].ColumnHidden = False
        .[Software].ColumnHidden = False
        .WS.ColumnHidden = False
        .[Hold Date].ColumnHidden = False

        .[Frames Date].ColumnHidden = True
        .Frames.ColumnHidden = True
        .[Cloud Ver].ColumnHidden = True
        .[Live Date].ColumnHidden = True
        .[Q Date].ColumnHidden = True
        .[Interfaces].ColumnHidden = True
    Case Else
        .[Pat Reg].ColumnOrder = 7
        .[Interfaces].ColumnOrder = 8
        .Frames.ColumnOrder = 9
        .[Frames Date].ColumnOrder = 10
        .[Cloud Ver].ColumnOrder = 11
        .[Live Date].ColumnOrder = 12
        .[Active Lic #].ColumnOrder = 13
        .[Q Date].ColumnOrder = 14
        .[Time Zone].ColumnOrder = 15
        .[Notes].ColumnOrder = 16

        .[Serv Conf].ColumnHidden = True
        .[Impl Date].ColumnHidden = True
        .[Software].ColumnHidden = True
        .WS.ColumnHidden = True
        .[Hold Date].ColumnHidden = True

        .[Frames Date].ColumnHidden = False
        .Frames.ColumnHidden = False
        .[Cloud Ver].ColumnHidden = False
        .[Live Date].ColumnHidden = False
        .[Q Date].ColumnHidden = False
        .[Interfaces].ColumnHidden = False
    End Select
End With

NB. If this code is being run from within the module of the form named "Default" then any reference in the code to Forms!Default can be replaced with Me. If it's being run from the module of the form used within the subform then any reference to Forms!Default!N avigationSubfor m can be replaced with Me. Either is a good move generally as it makes the code easier to understand and easier to maintain.

Rhonda:
I will post the code as is in the future unless it is SQL.

Generally a good idea but not always absolutely necessary. I suspect as you gain more experience you will learn what can and cannot be changed without affecting the sense of it. It's always a good idea to warn if you are paracoding though (Like paraphrasing but with code - I just made that word up). That way if we end up at one of those "This just cannot possibly make sense." moments then we know to request the raw code from you ;-)

[rhonda6373]

Good tips, thanks! I will make those changes. Yes, st is numeric.

[NeoPa]

You're welcome :-)

NB. I've made a very slight amendment to the code in order to make it more directly match the object being referenced. I updated the previous post with the changes.