访问 vba 修改查询定义以更改显示的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6297520/
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 vba modify query def to change columns displayed?
提问by MAW74656
I'm using the CurrentDb.QueryDefs object to alter the sql in my query. This works well, until I change the select clause. I want the query to now show only the fields named in my new select clause. Instead, the column headers are still showing, but there are no values. I'm displaying the query in a subform.
我正在使用 CurrentDb.QueryDefs 对象来更改查询中的 sql。这很有效，直到我更改了 select 子句。我希望查询现在只显示在我的新选择子句中命名的字段。相反，列标题仍在显示，但没有值。我在子表单中显示查询。
How can I force the subform/query to only show the specified columns that can change on a button click?
如何强制子表单/查询仅显示可以在单击按钮时更改的指定列？
REASON: This is an advanced search form where checkboxes represent each field, and the user can remove and add fields each time they search.
原因：这是一个高级搜索表单，其中复选框代表每个字段，用户可以在每次搜索时删除和添加字段。
回答by downwitch
You can't requery, you have to refresh the subform's source object:
你不能重新查询，你必须刷新子表单的源对象：
MySubformControl.SourceObject = ""
MySubformControl.SourceObject = "Query.MyQuery"
For testing, I created a table Table1 with fields Field1,...,Field4, then a form with 4 check boxes and a subform, then a query Query1 which the fields from Table1. Here is the code behind the form (I let Access name all my objects, so the subform is called Child8):
为了测试，我创建了一个包含字段 Field1、...、Field4 的表 Table1，然后是一个带有 4 个复选框和一个子表单的表单，然后是一个查询 Query1，其中包含来自 Table1 的字段。这是表单背后的代码（我让 Access 命名我所有的对象，所以子表单被称为 Child8）：
Private Sub Check0_AfterUpdate()
Rewrite_Query
End Sub
Private Sub Check2_AfterUpdate()
Rewrite_Query
End Sub
Private Sub Check4_AfterUpdate()
Rewrite_Query
End Sub
Private Sub Check6_AfterUpdate()
Rewrite_Query
End Sub
Private Sub Rewrite_Query()
Dim qdf As QueryDef
Dim strSQL As String
Set qdf = CurrentDb.QueryDefs("Query1")
If Check0.Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & ","
strSQL = strSQL & "Field1"
End If
If Check2.Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & ","
strSQL = strSQL & "Field2"
End If
If Check4.Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & ","
strSQL = strSQL & "Field3"
End If
If Check6.Value = True Then
If Len(strSQL) > 0 Then strSQL = strSQL & ","
strSQL = strSQL & "Field4"
End If
strSQL = "SELECT " & strSQL & " FROM Table1"
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Child8.SourceObject = ""
Child8.SourceObject = "Query.Query1"
End Sub
