vba 访问子表单，如何调整列的大小以最适合？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13020534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access subform, how to resize columns to best fit?
提问by MAW74656
I have a form with a subform. This subform displays the results of a query that is created dynamically (user enters criteria, I build the SQL, then update the querydef and display). Problem is since the columns are dynamic the width of the columns isn't working out, some are cutting off text.
我有一个带有子表单的表单。此子表单显示动态创建的查询的结果（用户输入条件，我构建 SQL，然后更新 querydef 并显示）。问题是由于列是动态的，因此列的宽度无法确定，有些会切断文本。
Is there a way to programmatically loop through the columns (or do the same without loop) and set them all to bestfit width after the query is refreshed?
有没有办法以编程方式循环遍历列（或在没有循环的情况下执行相同操作）并在刷新查询后将它们全部设置为最佳宽度？
EDIT: Here is what my code looks like now:
编辑：这是我的代码现在的样子：
CurrentDb.QueryDefs("SearchResults").sql = sql
CurrentDb.QueryDefs.Refresh
Dim qdf1 As DAO.QueryDef
Dim fld1 As DAO.Field
Set qdf1 = CurrentDb.QueryDefs("SearchResults")
For i = 0 To qdf1.Fields.Count - 1
Set fld1 = qdf1.Fields(i)
fld1.CreateProperty "ColumnWidth", dbInteger
fld1.Properties("ColumnWidth") = -2 'Throws error
Set fld1 = Nothing
Next i
Me.Child20.SourceObject = "Query.SearchResults"
回答by Fionnuala
You can set column widths like so:
您可以像这样设置列宽：
Sub SetColumnWidth()
Dim qdf1 As DAO.QueryDef
Dim fld1 As DAO.Field
Set qdf1 = CurrentDb.QueryDefs("query3")
For i = 0 To qdf1.Fields.Count - 1
Set fld1 = qdf1.Fields(i)
fld1.CreateProperty "ColumnWidth", dbInteger
'very narrow indeed
'fld1.Properties("ColumnWidth") = 200
'Or -2 : Sizes the column to fit the visible text
'but it is not quite as useful as it would seem
fld1.Properties("ColumnWidth") = -2
Set fld1 = Nothing
Next i
End Sub
回答by Mike
So I've run into this same problem just now. I was fortunate enough to have half of my queries work and the other half not. I've been using this code:
所以我刚才遇到了同样的问题。我有幸让我的一半查询有效，另一半无效。我一直在使用这个代码：
Sub QueryData(strSQL As String)
Dim qryData As DAO.QueryDef
Dim intcount As Integer
Set qryData = CurrentDb.QueryDefs("DataQuery")
qryData.SQL = strSQL
qryData.CreateProperty "ColumnWidth", dbInteger
qryData.Fields(0).Properties("ColumnWidth") = 5760
DoCmd.OpenQuery "DataQuery", , acReadOnly
End Sub
Which generated the error on half of the queries I tried to run with it. I traced it back to this odd, but simple truth: Columns built using an Alias (i.e. all formula columns and expressions) kick out this error. If the column is just a straight data pull, it works fine. If the column is, however, a formulated display.... it spits the no columwidth propertyerror.
这在我尝试使用它运行的一半查询上产生了错误。我将其追溯到这个奇怪但简单的事实：使用别名构建的列（即所有公式列和表达式）消除了这个错误。如果该列只是一个直接的数据拉取，它工作正常。但是，如果该列是公式化的显示......它会吐出no columwidth property错误。
Hopefully this'll help someone out! I know this questions about a year old, but it was the first result Google found for me on the topic.
希望这会帮助别人！我知道这个问题大约一岁，但这是谷歌为我找到的关于该主题的第一个结果。
