vba 设计一个以动态 SQL 作为记录源的访问表单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20989301/
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
Design an Access Form with Dynamic SQL as Recordsource
提问by got2nosth
I am pretty new to Access VBA programming. Here is one problem I have when creating a form in Access. I need to link the form's recordsource to a query object which I have already defined. Let's say I have field1, field2, field3, etc. in my query. The end product I would like to present is
我对 Access VBA 编程很陌生。这是我在 Access 中创建表单时遇到的一个问题。我需要将表单的记录源链接到我已经定义的查询对象。假设我的查询中有 field1、field2、field3 等。我想展示的最终产品是
- on top of the form, there are several text boxes for user to input filtering criteria of field1, field2, field3 ... and
- on a click of a button, a datasheet displays at the bottom of the form with the filtering criteria applied
- besides the existing fields in my query, I need to include at the end of the datasheet a customized column based on certain calculation result from field1 and field2. Some text could be displayed, for example "Late", "Early" or "On Time"
- 在表单顶部，有几个文本框供用户输入字段1、字段2、字段3 ...和
- 单击按钮后，表格底部会显示一个数据表，其中应用了过滤条件
- 除了查询中的现有字段外，我还需要在数据表的末尾包含一个基于 field1 和 field2 的特定计算结果的自定义列。可以显示一些文本，例如“晚”、“早”或“准时”
I have come up with two design approaches, but due to the lack of experience in VBA programming I am not able to accomplish either one of them right now...
我提出了两种设计方法，但由于缺乏 VBA 编程经验，我现在无法完成其中任何一种......
Design 1:
设计1：
I have a main form with the criteria input text boxes. This main form is not linked to any data source. Then I have a subform at bottom whose data source is dynamically set by reading the text boxes' values in the main form.
我有一个带有标准输入文本框的主表单。此主表单未链接到任何数据源。然后我在底部有一个子表单，它的数据源是通过读取主表单中文本框的值来动态设置的。
Design 2:
设计二：
I have a single form, but I shift all the criteria boxes to the Form Header section. This form has a record source from my query object. The detail section is used for displaying the data sheet with the form's filter property set to user criteria.
我只有一个表单，但我将所有条件框都移到了“表单标题”部分。此表单具有来自我的查询对象的记录源。详细信息部分用于显示表单的过滤器属性设置为用户条件的数据表。
I am not able to do design 1 because I don't know how to set the data source for my sub form dynamically. I am also not able to do design 2 because my form's header section does not appear even I have checked in the design view the form header/footer is enabled.
我无法进行设计 1，因为我不知道如何为我的子表单动态设置数据源。我也无法进行设计 2，因为即使我在设计视图中检查了表单页眉/页脚已启用，我的表单页眉部分也没有出现。
What can I do to realize my form design? Any alternative design suggestions?
我可以做些什么来实现我的表单设计？任何替代设计建议？
I am using Access 2003 for this development, but I hope my code can be upgradable to Access 2010 in future.
我正在使用 Access 2003 进行此开发，但我希望我的代码将来可以升级到 Access 2010。
Thanks in advance for your advice.
预先感谢您的建议。
采纳答案by mnieto
With both designs you should build the datasource dynamically, something like this:
对于这两种设计，您应该动态构建数据源，如下所示：
sql = "SELECT * FROM MyTableOrQuery WHERE 1=1"
If Not IsNull(textBox1) Then
sql = sql & " And Field1 = '" & textBox1 & "'"
End If
If Not IsNull(textBox2) Then
sql = sql & " And Field2 = '" & textBox2 & "'"
End If
And, finally, assign the sql to the form datasource.
最后，将 sql 分配给表单数据源。
Design1
设计1
The below code goes in the main form, just next to the above:
下面的代码进入主窗体，就在上面的旁边：
SubFormControlName.Form.RecordSource = sql
Design2
设计2
To show the controls in the header section you need to set the property DefaultView to running forms(I don't know the extact translation, I work with Access in spanish) In this case, the datasource is assigned to the main form
要在标题部分显示控件，您需要将属性 DefaultView 设置为运行表单（我不知道确切的翻译，我使用西班牙语的 Access）在这种情况下，数据源被分配给主表单
Me.recourdsource = sql
For your third point: You can build calculated field using
对于您的第三点：您可以使用构建计算字段
Iif(fieldA<5,"Early",Iif(fieldA>10,"Late","OnTime"))
or you can use conditional formatting. I think this option is most clear
或者您可以使用条件格式。我觉得这个选项最清楚
