Fields autofit and alternate row color (1 Viewer)

StephanL

New member
Local time
Today, 13:18
Joined
Feb 18, 2022
Messages
14
Hello everyone, it's been just a few days since I became a member here and I gotta say, what a nice site! Great people, great ideas!
I'm asking for a bit of help with a minor issue with Access. Is it possible to write VBA code to autofit fields and to alternate row color?
My main code creates tables, then populates them with data. It all works just fine. But when I open the Access file, I can't see some of the
longer fields' names entirely, they are truncated and it looks really ugly. One solution I found was to loop through all the rows in the problem field, find the longest
string and then set the field column width to that value. But it does not help me - I have a field called "NUMBER MOVING PARTS", obviously a
long name, and the values can be between 1 and 50. So this little idea is of no use: the length of the field name is much longer that the length of the variables
it contains. Sticking to my example, when I open the newly created table it shows "NUMBER MOV" and of course, if I double-click on the top right point of the field it auto-expands, but I would like to do it via code as opposed to the manual solution.
This is one issue...the second one, is it possible to alternate the row color with code?
Thank you very much!! Any feedback is greatly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:18
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure if this will help you but check out the ColumnWidth property and use the value -2.
 

GPGeorge

Grover Park George
Local time
Today, 04:18
Joined
Nov 25, 2004
Messages
1,776
Ouch. "My main code creates tables, then populates them with data."

That's pretty unusual in a relational database application, except of course, for temp tables that can be interim for a longer process. And OPENING tables for users to see is even more rare.

Perhaps there is more to this than meets the eye, but it might be helpful to get the big picture here so someone can help you find a more viable soluton.
 

StephanL

New member
Local time
Today, 13:18
Joined
Feb 18, 2022
Messages
14
Ouch. "My main code creates tables, then populates them with data."

That's pretty unusual in a relational database application, except of course, for temp tables that can be interim for a longer process. And OPENING tables for users to see is even more rare.

Perhaps there is more to this than meets the eye, but it might be helpful to get the big picture here so someone can help you find a more viable soluton.
I understand your point here, and yes, maybe I was not specific enough. My Access files are multiple and have the same structure simply because they hold a lot of data. I am seriously considering learning SQLServer, but for the moment this is what I can do. The database files are not meant to be relational at all, they are more like CSV files but containing checked+sanitized data and they are queried independently. Sure, I don't have to OPEN the tables, but I like to do it just to apply the extra check of the human eye. And, being a bit of a maniac with cleanliness, I feel better to proceed this way.
 

GPGeorge

Grover Park George
Local time
Today, 04:18
Joined
Nov 25, 2004
Messages
1,776
In that case, I think we can suggest a compromise that will work.
Create a form and open the table in datasheet view in a subform control on it. Then you can use the form's Current event, to manipulate the column widths. theDBGuy suggested the expression.

Here's the code I use for this. It runs on the Current event.

Code:
Public Function DataSheetControls(ByRef frm As Form) As Variant
    '---------------------------------------------------------------------------------------
    ' Procedure : DataSheetControls
    ' Author    : GPC Data
    ' Date      : 9/25/2010
    ' Purpose   :
    '---------------------------------------------------------------------------------------
    Dim ctl As control
    
    On Error GoTo errHandler
    
    For Each ctl In frm.Controls
        If ctl.Tag = "Hidden" Then
            If ctl.ColumnHidden = False Then ctl.ColumnHidden = True
        ElseIf ctl.Tag = "Fixed" Then
            ctl.ColumnWidth = -2 ' -2 is sets column width to fit displayed text exactly
        End If
    Next ctl
    
    frm.RowHeight = 0.1667 *1440 ' set rows to default height
    
CleanUp:

    On Error Resume Next

exitProc:

    Exit Function

errHandler:

   MsgBox "Error"

    Resume CleanUp

End Function
 

StephanL

New member
Local time
Today, 13:18
Joined
Feb 18, 2022
Messages
14
In that case, I think we can suggest a compromise that will work.
Create a form and open the table in datasheet view in a subform control on it. Then you can use the form's Current event, to manipulate the column widths. theDBGuy suggested the expression.

Here's the code I use for this. It runs on the Current event.

Code:
Public Function DataSheetControls(ByRef frm As Form) As Variant
    '---------------------------------------------------------------------------------------
    ' Procedure : DataSheetControls
    ' Author    : GPC Data
    ' Date      : 9/25/2010
    ' Purpose   :
    '---------------------------------------------------------------------------------------
    Dim ctl As control
   
    On Error GoTo errHandler
   
    For Each ctl In frm.Controls
        If ctl.Tag = "Hidden" Then
            If ctl.ColumnHidden = False Then ctl.ColumnHidden = True
        ElseIf ctl.Tag = "Fixed" Then
            ctl.ColumnWidth = -2 ' -2 is sets column width to fit displayed text exactly
        End If
    Next ctl
   
    frm.RowHeight = 0.1667 *1440 ' set rows to default height
   
CleanUp:

    On Error Resume Next

exitProc:

    Exit Function

errHandler:

   MsgBox "Error"

    Resume CleanUp

End Function
Thank you. Let me give it a go.
 

Users who are viewing this thread

Top Bottom