sql - 在 MS Access 中,创建动态查询后,如何使用记录集中的相应值更新表单上的文本框?
问题描述
按照此处的示例:https: //support.microsoft.com/en-us/help/304302/how-to-build-a-dynamic-query-with-values-from-a-search-form-in-access我创建了一个搜索按钮来搜索一个表,并且似乎提取了正确的 SQL 语句。但是,在此表单上,我有几个文本框(用户 ID、名字、姓氏、部门),它们与数据库中各自的列相关联。如何更新表单 frmSearchUsers 中的这些文本框以反映过滤/查询表的结果?
我原以为 Me.Requery 就足够了,但是文本框保持空白(保存为 txtSQL)
Private Sub cmdSearch_Click()
On Error Resume Next
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String
'Initialize the Where Clause variable.
sWhereClause = " Where "
'Start the first part of the select statement.
sSQL = "select * from customers "
'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acTextBox
.SetFocus
'This is the function that actually builds
'the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbtext, .Text)
Else
sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbtext, .Text)
End If
End Select
End With
Next ctl
'Set the forms recordsource equal to the new
'select statement.
Me.txtSQL = sSQL & sWhereClause
Me.RecordSource = sSQL & sWhereClause
Me.Requery
结束子
有 4 个文本框,用户 ID、名字、姓氏、部门。
假设我知道表客户中有一个 Jane Doe,但不知道她的 ID 或部门。
在 FirstName 文本框中输入 Jane,在 LastName 文本框中输入 Doe,然后点击搜索似乎会产生适当的 SQL 查询(并已确认这在表的 SQL 视图中正确过滤): SELECT * FROM Customers Where FirstName="Jane" 和姓氏="Doe"
但是附加字段不会更新 - 我在这里做错了什么?是因为我拥有绑定到表格列的文本框的控制源吗?
解决方案
想出了答案。我必须定义记录集和数据库,然后在按钮更新后引用记录集结果以显示到文本框。守则大致相同:
Private Sub btnSearch_Click()
On Error Resume Next
Dim strSQL As String
Dim ctl As Control
Dim strWhereClause As String
Dim db As Database
Dim rs As DAO.Recordset
Dim textBox As Control
Dim finalSQL As String
Set db = CurrentDb
'Init beginning of SQL select statement
sWhereClause = " Where "
sSQL = "SELECT * FROM Customers "
'Loop through filled in Controls on form to get value
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
.SetFocus
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
Else
sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl
Me.txtSQL = sSQL & sWhereClause
finalSQL = sSQL & sWhereClause
Set rs = db.OpenRecordset(finalSQL)
If rs.RecordCount > 0 Then
'If Matching Record(s) are found, pull result into appropriate fields
Me.UserID = rs!UserID
Me.FirstName = rs!FirstName
Me.LastName = rs!LastName
Me.Department = rs!Department
MsgBox "No Users Found Matching Specified Search Criteria.", vbOKCancel, "No Results Found"
End If
推荐阅读
- python - 将多行传递给python脚本中的程序
- react-native - 从嵌套在 Stack Navigator 中的 Tab Navigator 导航
- javascript - react redux 中间件无法访问 utils 中定义的变量
- javascript - 确定相邻重复数组元素的索引
- r - R ggplot 和 ggpubr,如何计算/显示 facet_wrap 图中的 p 值,不同图中的不同组?
- javascript - 如何同时和异步进行多个ajax调用
- perl - 仅当两个模式都匹配时,如何删除连续的 2 行
- javascript - 如何获取标签外的文本值
- c - 使用分治算法从给定数组中找到最小值和最大值
- python - 无法从字典列表中提取数据,“NoneType”对象不是下标