在MS Access中，创建动态查询后，如何使用记录集中的相应值更新窗体上的文本框？
在MS Access中，创建动态查询后，如何使用记录集中的相应值更新窗体上的文本框？
提问于 2018-08-01 01:04:18
按照这里的示例: SQL我已经创建了一个搜索按钮，它可以搜索表，并且似乎可以拉入正确的https://support.microsoft.com/en-us/help/304302/how-to-build-a-dynamic-query-with-values-from-a-search-form-in-access语句。但是，在这个表单上，我有几个文本框(UserID、FirstName、LastName、Department)，它们都绑定到数据库中各自的列。如何更新表单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个文本框，UserID，FirstName，LastName，Department。
假设我知道在表客户中有一个无名氏，但不知道她的ID或部门。
在FirstName文本框中键入Jane，在LastName文本框中键入Doe，然后点击搜索似乎会产生适当的SQL查询(并且已经确认这在表的SQL视图中进行了正确的过滤)：SELECT * FROM Customers Where FirstName="Jane“和LastName="Doe”
但是，附加字段不会更新-我在这里做错了什么？是不是因为我拥有绑定到表列的文本框的控件源？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-01 05:31:39
找到了答案。我必须定义记录集和数据库，然后引用记录集结果，以便在按钮更新后显示到文本框中。代码基本上是相同的：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51618635
复制相关文章
相似问题