Loop through Form fields, set same width - reference older thread - using VBA 1

[kjv1611]

In Reference to thread702-1664646

I want to do the same thing as the VBA snippet does, but be able to loop through all the fields on a form. I can loop through controls of a form and fields of a recordset, but neither seems to get me to what I'm needing.

Can anyone give me a hint as to how to loop through the columns, and set the column widths? half an nich widths should work nicely in my scenario.

Thanks for any input.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

is this a datasheet or tabular form?

 

[kjv1611]

sorry - datasheet view

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

hmm... well, I have an idea... if I can get it to refer to the control names, I can have it loop through all controls on form, and then take the name from that control, and get the columns property??? I think I'll give that a go just to see..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Ok, my idea worked.. so far.

First of all, let me specify why this is important in my scenario: I'm using a cross-tab query for the source, so the # of columns will grow over time, as each column is a day... so as each day is added, a new column will be added - the project will not go long enough for a column count limitation to matter. If it did, I could still limit the # of columns, so would still be okay.. so I could look a the last 10 days or something.

Anyway, here's the code:

Sub Form_Load()
   Const TWIPSTOINCHES = 1440
   Dim frm As Form
   Dim ctl As Control
    
   Set frm = Forms!MainFormName!SubFormName.Form [COLOR=#73D216]'Could be simpler if only one main form involved
                                                 ' - also can just use the local Form variable.[/color]
   
   For Each ctl In frm.Controls
      If ctl.Name LIKE "*_Label" Then
         [COLOR=#4E9A06]'Do not do anything if it's a label - using the system generated label names.[/color]
      Else
         frm.Controls(ctl.Name).ColumnWidth = TWIPSTOINCHES * 1
      End If
   Next ctl
   
[COLOR=#4E9A06]'Not sure if repainting is necessary.  I haven't tested yet without it.[/color]
   frm.Repaint

[COLOR=#4E9A06]'Not 100% sure variable declaration is 100% required or recommended, but I do as a habbit[/color]
   If ctl Is Nothing Then Else Set ctl = Nothing
   If frm Is Nothing Then Else Set frm = Nothing

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Sorry left off the last line....

Sub Form_Load()
   Const TWIPSTOINCHES = 1440
   Dim frm As Form
   Dim ctl As Control
    
   Set frm = Forms!MainFormName!SubFormName.Form [COLOR=#4E9A06]'Could be simpler if only one main form involved
                                                 ' - also can just use the local Form variable.[/color]
   
   For Each ctl In frm.Controls
      If ctl.Name LIKE "*_Label" Then
         [COLOR=#4E9A06]'Do not do anything if it's a label - using the system generated label names.[/color]
      Else
         frm.Controls(ctl.Name).ColumnWidth = TWIPSTOINCHES * 1
      End If
   Next ctl
   
'Not sure if repainting is necessary.  I haven't tested yet without it.
   frm.Repaint

[COLOR=#4E9A06]'Not 100% sure variable declaration is 100% required or recommended, but I do as a habbit[/color]
   If ctl Is Nothing Then Else Set ctl = Nothing
   If frm Is Nothing Then Else Set frm = Nothing 

End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

Just to be 'picky'...

   For Each ctl In frm.Controls
      If [blue]NOT ([/blue]ctl.Name LIKE "*_Label"[blue])[/blue] Then
         frm.Controls(ctl.Name).ColumnWidth = TWIPSTOINCHES [red][s]* 1[/s][/red]
      End If
   Next ctl

Why multiply by 1?

Have fun.

---- Andy

 

[kjv1611]

Oh... I was multiplying by .5 and .75 trying to find the right width, and 1 seemed to work best.. well, who knows, I might can go .95 or something, but not worth trying more. The main reason I left the 1 is just to remind me it's "one inch" - although that can be gotten from the variable name, and could simply add in comments.. for now I just left the 1. I did think about that, though.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

By the way.. I did not use the NOT() construction, and instead just use an empty IF/ELSE bit, as I've found in the past a little bit of better performance (doubtful would matter with this item), and I had read some seemingly good sources on the topic as well - several years ago, so I couldn't point to the reference straight away. Seems it came up in discussions here, actually.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

That bit of code is still working rather nicely. Who'd of thought you'd need custom code to just get the columns to size the way you wanted. I sure would not have. But that works out really well, actually, since I know the columns count will eventually grow. Sorry to post to this again, I'm just really excited about this one - it works just lovely.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

Here is a more generic version in case the form has all kinds of controls just not textboxes and labels.

Public Sub SetColumnWidth(frm As Access.Form, Optional inchwidth As Single = -2#)
  '-2 is best fit
  Const TwipsToInches = 1440
  Dim ctrl As Access.Control
  On Error Resume Next
  For Each ctrl In frm.Controls
     If inchwidth = -2# Then
       ctrl.ColumnWidth = -2#
     Else
       ctrl.ColumnWidth = inchwidth * TwipsToInches
     End If
  Next ctrl
End Sub

I do not like the resume next (although it works well). You could check all of the control types like
ctrl.controlType = acTextbox or ctrl.controltype = acCombobox
(accheckbox, acoptionbutton,actogglebutton, aclistbox)
but even that is not full proof. Example a checkbox inside an option group cannot have its columnwidth set. Easier to try them all and see what ones support a column width.

 

[kjv1611]

Thanks. With datasheet view, can you even have other types of controls? I just assumed that you only had labels and text boxes when it's a datasheet view.

Well, one thing I didn't think about well enough, so now am trying to figure out the best way to work around is when there are new fields/columns added to the end results. I need to be able to dynamically add columns.

I'm looking online to see what is the best way to add columns or else rebuild the column list. What would be idea would be if I could manually re-run the wizard, basically, so that it just adds all fields (except for one that I've got skipped by dynamically creating the record source.

Anyway, once I decide on a method, I'll post that. Also, MajP, I put a star on your post in the other referenced thread, as that was what I went with for how to set the column widths.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

ok... I think I found a better method altogether. It's not as pretty of a form, but the widths just work, and it automatically lists the correct columns. All I had to do was use a Pivot Table Form in Access. I'd never used that in Access, and though probably had seen it, just never gave it much thought.

So, no VBA required, and no messing with the record source. It also eliminates the "<>" column that I was getting rid of with VBA to dynamically create the form's record source.

But... Now...

Now do I force the sort order of the column headers in the pivot form?? And I thought I was finished... but it's not sorting the way I want...

I want the column headers sorted in descending order, not ascending... I sorted both the underlying query and the form itself (on the data tab), and still it's showing in ascending order. And also, I don't need to see the (Blank) column, so I'm wondering if I remove it, if the pivot form will automatically add the new columns, or if they will be filtered off initially.. well more to think about and test..



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Ok, nevermind about the (Blank) column - it works correctly - I just wasn't thinking... so it all looks good so far, except for the sorting... which really isn't a big deal, but I just think it'd fit better in Descending order for the specific instance..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I found it, I found it!

Okay, here's how to sort a pivot form - it's really straight forward, I just had to quit assuming and start looking, I guess.

1. On the ribbon, click on "PivotTable Tools"
2. Click the "Property Sheet" button if it isn't already showing
3. In the pivot table, select the header that mentions the field which is used for the column headers (In my case, it was "DayComplete", and each column is a date)
4. In the property sheet, under General Commands, click the Sort button for Z->A (ascending).

That's it - as soon as I did that, it began sorting correctly!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Well, there is one more caveat, though not very important. I'd rather it put the "(Blank)" column at the beginning rather than end... so I'd rather it sorted by (Blank) and THEN everything else in Descending order... and I could manually change that, but I'm as soon as I do that, it removes the auto sort, so the next time a field is added, it'll be in the wrong location. So, "(Blank)" will just have to remain at the end..

Also, on the number formatting (assuming you're using numbers) - if it's just a SUM or COUNT, and you're in the thousands, and want a comma, but no period, you have to use a custom formatting... so here's what worked for me:

#,###

I put the above in the Text format of the Properties window for the field which was being calculated - in order to do that, I just had to make sure that field was selected in the pivot table.

I'm sure this is all elementary to many of you - I've just never used this feature (Access 2010 - no idea whether it existed prior).

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

With datasheet view, can you even have other types of controls? I just assumed that you only had labels and text boxes when it's a datasheet view.

So any form can be shown in datasheet view. Some controls are ignored, some are converted to textboxes, and some appear the same way. A combobox shows as a combobox, a checkbox shows as a checkbox, and a listbox shows as a combobox.
So you can only set the columnwidth property for those that will show in the datasheet or you will get an error. I thought I could just check if it has a control source, but that does not seem to completely work. So I could not find a simple way except trapping the error to determine if the control (column) could be shown in datasheet view. I am sure there is some way to do that.

In datasheet view you can show textboxes, checkboxes, comboboxes, and single radio button
A listbox converts to a combobox
Some controls can be converted into textboxes: bound object frame, toggle button, probably an attachment control
Some controls are not shown in datasheet: label, tab control, line, etc
And some are shown at certain times and not others: a radio button (option button) not part of an option group will show. Certain image controls will convert to a textbox if the object is not embedded.

 

[kjv1611]

Thanks for the additional information - good to know. I just rarely have used datasheet view, personally, as I generally prefer the Continuous view.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57