Solved Weird Visual Bug in my Code that Conditionally Hides Columns

[SparklySpartan]

Hi Everyone,

I wanted to pick your brains if you don't mind on an issue I'm having with one of my forms. A key characteristic of the form I'm creating is that it will conditionally show / hide / resize / relabel columns based on data that gets passed to one of it's subroutines. Any help or insights you could provide would be appreciated. I'm especially curious how others have tackled implementing this same kind of behavior in their forms, so please feel free to share your solutions on this thread.

The subroutine I've created to do this is called Change_Columns

Public Sub Change_Columns(ByRef column_info() As String)
    ' Column info is a 2 dimensional array with 3 rows
    ' row 1: Field Names
    ' row 2: Desired Widths for Columns (corresponding postions to row 1)
    ' row 3: Desired column names, if empty, default to captions already put there
    Dim ctrl As Control
    Dim column_included As Boolean
    Dim i As Integer
    Dim used_up_width As Double: used_up_width = 0
   
    For Each ctrl In Section(acDetail).controls
        Call Set_Column_Visibility(ctrl.Name, False)
        Call Set_Column_Width(ctrl.Name, 0)
    Next ctrl
   
    For Each ctrl In Section(acDetail).controls
        For i = 1 To UBound(column_info, 2)
            If column_info(1, i) = ctrl.Name Then
                Call Set_Column_Visibility(ctrl.Name, True)
                If ctrl.Name <> "Description" Then
                    Call Set_Column_Width(ctrl.Name, CDbl(column_info(2, i)))
                    used_up_width = used_up_width + column_info(2, i)
                End If
                Call Set_Column_Caption(ctrl.Name, column_info(3, i))
                Exit For
            End If
        Next i
    Next ctrl
   
    ' Set description column to be equal to the remaining space in twips
    Call Set_Column_Width("Description", Me.InsideWidth - used_up_width)
End Sub

I'm completely open and welcome to code criticism and being taught a better way to do this by the way, but this is what I've devised at the moment.

First, I hide all of the columns (my Set_Column_Width and Set_Column_Visibility subs modify the properties of both the fields named and their corresponding headers). I have to hide all of the columns before expanding them to avoid the error that goes along the lines of "You can't increase the size of this control because it would be too big to fit in the form."

After hiding every column, I go through the controls a second time and re-enable the ones whose names are mentioned in the array I pass this subroutine. While I'm at it, I set the widths of the enabled columns to the desired widths and the captions to the desired captions.

My final step is to set the description field's (this is labeled "Task" in the screenshot below) width to whatever amount is left according to a variable of mine keeping track of the width I've used and the forms .InsideWidth property. Some might ask why I didn't just set that field as stretch across the top. I did try that, but it seemed to behave weirdly and inconsistently, so I opted to explicitly set the width of this column myself.

This system seems to work fine except for one weird visual glitch that happens only when you open the database for the first time. Every time I first open the database and switch to this tab for the first time, I see this weird gap that by my understanding of my code should not be there.

(Also note the columns labeled "Type" and "Author", the dropdown whose value is "Standard" is supposed to be directly underneath the "Type" column, and field with my name should be directly underneath the "Author" column, which has also been pushed off screen because of this empty space in the column headers)

I should mention that this form's on load event runs the above Change_Columns subroutine and passes it a valid array of column names.

It gets weirder. When I do something to resize the screen a bit like hit F11 twice, or temporarily making access a smaller window then going back to a fully sized window, this weird visual glitch magically goes away... the gap disappears and the fields and column headers are all once-again aligned like they should be.

I'm just plain confused about why this happens. I was curious on if anyone ran into similar weird visual artifacts like this in their projects and found some way to stop them from occurring in their code.

I thought the issue had something to do with padding, which is supposed to be zero for all of the controls, so I added the following loop to the end of my subroutine to check:

For Each ctrl In Section(acDetail).controls
        ctrl.LeftPadding = 0
        ctrl.RightPadding = 0
    Next ctrl

Any insights as to what this is, why it happens, why it only happens the first time I open the DB, and how I might fix it would be greatly appreciated.

Thanks in advance for your time and thoughts, hope this sparks an interesting discussion!

 

[Josef P.]

Is HorizontalAnchor possibly set to the right?

 

[MajP]

It would be helpful if for now you put borders on your controls. You can do that in the code since you loop them. Hard to see where they are lining up.

 

[SparklySpartan]

I was gonna try adding the borders, but first I made sure everything was anchored top left, and now the gap isn't showing up anymore.
Appreciate your suggestions!

 

[isladogs]

In general, I use the Tag property to conditionally show/hide controls on forms. For example, see

Set Controls

This demonstrates a way of setting the state of a group of controls at the same time using the controls' Tag property

www.isladogs.co.uk

However, I use a different approach for hiding /moving fields in continuous forms. Rather than explain how I do this in detail, I suggest you read my articles below and try out the example databases

Hide & Restore Selected Columns

This article and example app demonstrates how to hide and restore selected columns in a continuous form. The form can also be sorted and filtered with all filtered fields highlighted in green and sorted columns in yellow.

www.isladogs.co.uk

Freeze Columns in Continuous Forms

This 2-part article discusses the freeze fields feature in datasheet forms and demonstrates how similar functionality can be added to continuous forms

www.isladogs.co.uk

Move/Resize Columns in Continuous Forms

This is the first part of a two part article showing how the layout of continuous forms can be modified by repositioning and/or resizing selected columns. The article includes an example app with all required code.

www.isladogs.co.uk

Hope something in those articles will be of use to you.

 

[SparklySpartan]

Hope something in those articles will be of use to you.

Thanks for the resources! I'll be sure to check them out.

I actually happened on one of your articles / videos showing this kind of thing is possible which is what sparked the whole idea for this in the first place, so it's kinda cool that you ended up replying to this thread. But yeah, I'll definitely take a look at your examples and see where I can refine my technique. Thanks again

 

[The_Doc_Man]

Getting unusual gaps SOMETIMES occurs (certainly not ALWAYS) when you have "layout" enabled for your forms. It becomes obvious if you switch to layout view because then you would see the layout area borders and one of them would coincide with that gap. However, this is an off-the-wall guess for something to look at.

 

[arnelgp]

when you size the width of a Column to 0, make sure you Adjust the rest of the Column to it's right also.
example if you have 5 columns and you set to 0 the width of column 2:

For i = 3 to 5  'the columns to column 2
    ' adjust the label position
    Me("Label" & i).Left = Me("Label" & i -1).Left + Me("Label" & i - 1).Width
   ' adjust the textbox position
   Me("textbox" & i).Left = Me("Textbox" & i - 1).Left + Me("Textbox" & i - 1).Width
Next

the same code if you Reinstate the column width.

for this to work, you need to Rename your labels as Label1, Labe2, etc.
and textboxes Textbox1, Textbox2, etc from Left to right.

there is also a demo (with freebee code) on the sample database section:

Move/Resize(Width) Column of Continuous Form

this is somewhat similar of the same demo here, but with full code that you can inspect (not an .accde). there may be some errors or unwanted behavior, since this is the initial upload. if you see any errors or unwanted behaviour, you try to fix them. there is no documentation on the code at the...

www.access-programmers.co.uk

 

[SparklySpartan]

when you size the width of a Column to 0, make sure you Adjust the rest of the Column to it's right also.

Interesting, I didn't end up needing to do that with my current system. I'm using controls in a layout, so by toggling one column, all of the columns to its right will automatically slide to the left. It turns out the reason for my weird gap was simply that not everything was anchored the same way, which caused this weird visual effect when I started up the database.

But your example makes perfect sense when the controls aren't in a layout. So, if in the future I wanted to achieve more complex positioning of controls without the restrictions created by using layouts, I'd have to use the technique in your example.