Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and exploring the core foundations for app libraries.
Dim Db As DAO.Database, Qry As DAO.QueryDef, Field As DAO.Field, Property As DAO.Property Set Db = CurrentDb Set Qry = Db.QueryDefs("MyQueryName") Set Field = Qry.Fields!MyFirstFieldName Set Property = Field.Properties!ColumnWidth Property = 1440 Set Field = Qry.Fields!MySecondFieldName Set Property = Field.Properties!ColumnWidth Property = 2880 Set Field = Qry.Fields!MyThirdField Set Property = Field.Properties!ColumnWidth Property = 4320 Set Property = Nothing Set Field = Nothing Set Db = Nothing DoCmd.OpenQuery "MyQueryName"
While the user can name columns, Access assigns a number to each column, starting at the first column on the left with 0, and the number for each column to the right is incremented by 1. Line 10 tells Access that we want to regard the query open and with focus as a Form. Line 20 uses the number of columns to give Access the numbers of the columns we want to work with: Column(0) through Column(Number of columns - 1), and how many times to loop. Line 30 identifies the specific column number we want to affect, and Line 40 sets ColumnWidth for that column to -2 (best fit). Line 50 tells Access to use the next integer for column number, and return to Line 20 repeat the process.
Public Sub RightsizeQueryColumns() 'By Paul Cook-Giles, 11/2/2015 'This code assumes that a query is opened and has focus, and sets column widths to display complete title/data Dim intColumNumber As Integer, ctl As Control 10 Set frm = Screen.ActiveDatasheet 20 For intColumNumber = 0 To frm.Controls.Count - 1 30 Set ctl = frm.Controls(intColumNumber) 40 ctl.ColumnWidth = -2 50 Next intColumNumber 60 DoCmd.Save End Sub