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"
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
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Commented: