vba 在 Access 2010 中构建搜索表单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15670881/
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
building a search form in Access 2010
提问by Daytron
I am trying to make search customer form for access 2010.
我正在尝试制作用于访问 2010 的搜索客户表单。
I like to have an option group based on a query search. I made a search query looking for first name or last name. two textboxes are also present on the form to fill up the query inputs (1st and last name)
我喜欢有一个基于查询搜索的选项组。我进行了搜索查询以查找名字或姓氏。表单上还有两个文本框来填写查询输入（第一个和姓氏）
I need option group so that I can select the resulted name for booking purposes.
我需要选项组，以便我可以选择结果名称进行预订。
In the past I have made an indirect way of doing this using subform and a checkbox. Then loading both results on a subform and checkbox (requery) so the user only has to select on the checkbox. But this time I want the options to be the query result itself! Please help.
在过去，我使用子表单和复选框间接地做到了这一点。然后在子表单和复选框（重新查询）上加载两个结果，因此用户只需在复选框上进行选择。但是这次我希望选项是查询结果本身！请帮忙。
回答by Gord Thompson
Here is a simple example that uses a List Box:
这是一个使用列表框的简单示例：
Table: Clients
表：客户
ID - AutoNumber
LastName - Text(255)
FirstName - Text(255)
Email - Text(255)
ID - 自动编号
LastName - Text(255)
FirstName - Text(255)
Email - Text(255)
Test data:
测试数据：
ID LastName FirstName Email
-- ---------- -------------- ------------------
1 Thompson Gord [email protected]
2 Loblaw Bob [email protected]
3 Kingsley Hank [email protected]
4 Thompson Hunter S. [email protected]
5 Squarepants Spongebob [email protected]
6 O'Rourke P. J. [email protected]
7 Aldrin Edwin "Buzz" [email protected]
Form layout:
表格布局：
VBA module for this form:
此表单的 VBA 模块：
Option Compare Database
Option Explicit
Private Sub Form_Load()
Me.lstSearchResults.RowSource = ""
End Sub
Private Sub btnSearch_Click()
Me.lstSearchResults.SetFocus
Me.lstSearchResults.Value = Null
Me.lstSearchResults.RowSource = _
"SELECT ID, LastName, FirstName FROM Clients " & _
"WHERE LastName LIKE ""*" & DQ(Me.txtSearchLastName.Value) & _
"*"" AND FirstName LIKE ""*" & DQ(Me.txtSearchFirstName.Value) & "*"""
End Sub
Private Function DQ(s As Variant) As String
' double-up double quotes for SQL
DQ = Replace(Nz(s, ""), """", """""", 1, -1, vbBinaryCompare)
End Function
Private Sub btnLookupEmail_Click()
If IsNull(Me.lstSearchResults.Value) Then
Me.txtEmail.Value = ""
Else
Me.txtEmail.Value = DLookup("Email", "Clients", "ID=" & Me.lstSearchResults.Value)
End If
End Sub
When the form is first opened, everything is empty.
当表单第一次打开时，一切都是空的。
Typing "thompson" (without the quotes) and clicking btnSearchpopulates the List Box with clients WHERE LastName LIKE "*thompson*". (If you look at the code you'll see that it will also match on FirstName if you supply one.)
键入“thompson”（不带引号）并单击btnSearch将使用 clients 填充列表框WHERE LastName LIKE "*thompson*"。（如果您查看代码，您会发现如果您提供 FirstName，它也会与 FirstName 匹配。）
Select one of the items in the List Box and click btnLookupEmailand the email address is displayed in the Text Box below.
选择列表框中的一项并单击btnLookupEmail，电子邮件地址将显示在下面的文本框中。
