SQL 作为访问表单字段的控制源
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1311885/
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
SQL as Control Source for Access Form field
提问by Jambobond
Is there any way populate an Access Form's text feild's value using SQL?
有什么方法可以使用 SQL 填充 Access Form 的文本字段的值吗？
I have read that it is not possible to simply enter SQL as the Control Source. Is this true?
我已经读到不能简单地输入 SQL 作为控制源。这是真的？
thanks for any halp :)
感谢您的帮助 :)
--edit--
- 编辑 -
I need to perform this query;
我需要执行这个查询；
SELECT tblCaseIssues.IssueDesc FROM tblCaseIssues INNER JOIN tblCaseNewHS_Issues ON tblCaseIssues.ID = tblCaseNewHS_Issues.IssueID WHERE(tblCaseNewHS_Issues.HS_ID = 81))
采纳答案by JeffO
Pretty sure that is true SQL, but you could use the function:=DLookUp("field_name","table_name","any_fieldname = 'value'")
很确定这是真正的 SQL，但您可以使用该函数：=DLookUp("field_name","table_name","any_fieldname = 'value'")
回答by David Walker
You can set the control source of your field to a function name. That function can easily execute your SQL, and/or pass in a variable. Here's my simple boiler plate for a function to execute a SQL statement into a recordset and return the first value. In my world I'm usually including a very specific where clause, but you could certainly make any of this function more robust for your needs.
您可以将字段的控件源设置为函数名称。该函数可以轻松执行您的 SQL，和/或传入一个变量。这是我的简单样板，用于在记录集中执行 SQL 语句并返回第一个值的函数。在我的世界中，我通常会包含一个非常具体的 where 子句，但是您当然可以根据您的需要使任何此函数更加健壮。
=fnName(sVariable, iVariable)
Public Function fnName( _
sVariable as String, _
iVariable as Integer _
) As String
On Error GoTo Err_fnName
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
sSQL = ""
Set con = Access.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open sSQL, con, adOpenDynamic, adLockOptimistic
If rst.BOF And rst.EOF Then
'No records found
'Do something!
Else
'Found a value, return it!
fnName = rst(0)
End If
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
Exit_fnName:
Exit Function
Err_fnName:
Select Case Err.Number
Case Else
Call ErrorLog(Err.Number, Err.Description, "fnName", "", Erl)
GoTo Exit_fnName
End Select
End Function
回答by Fionnuala
It might be easiest to use a combobox and set the Row Source to your query, alternatively, DAO is native to Access.
使用组合框并将行源设置为您的查询可能是最简单的，或者，DAO 是 Access 的本机。
Private Sub Form_Current()
''Needs reference to Microsoft DAO 3.x Object Library
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strResult As String
strSQL = "SELECT ci.IssueDesc FROM tblCaseIssues ci " _
& "INNER JOIN tblCaseNewHS_Issues cni ON ci.ID = cni.IssueID " _
& "WHERE cni.HS_ID = 81"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Do While Not rs.EOF
strResult = strResult & ", " & rs!IssueDesc
rs.MoveNext
Loop
strResult = Mid(strResult, 3)
Else
strResult = "Not found"
End If
Me.TextBoxName = strResult
End Sub
回答by Albert D. Kallal
Just take your sql query and save it as a query.
只需将您的 sql 查询保存为查询即可。
Then in the text box, just place:
然后在文本框中，只需放置：
=(dlookup(“IssuesDesc”,”name of query”))
=（dlookup（“问题描述”，“查询名称”））
I at a rather large loss as to all these posters suggesting whacks of code where as none is needed at all . Just save your sql as a query and then use the dlookup() function as the text box's data source and you are done.
我对所有这些海报都提出了一些根本不需要的代码感到相当大失所望。只需将您的 sql 保存为查询，然后使用 dlookup() 函数作为文本框的数据源，您就完成了。
回答by user7331043
Private Sub Form_Load()
Me.Text0 = CurrentDb.OpenRecordset("SELECT COUNT(name) AS count_distinct_clients FROM (SELECT DISTINCT name FROM Table1 WHERE subject='Soc') AS tmp;").Fields(0)
End Sub
回答by Daniel L. VanDenBosch
I have created the following function to solve this very problem. I like this solution because you don't have to deal with saved queries clogging up your navigation pane or long workarounds.
我创建了以下函数来解决这个问题。我喜欢这个解决方案，因为您不必处理阻塞导航窗格的已保存查询或冗长的解决方法。
Public Function DAOLookup(SQLstatement As String)
'once you are finished with your SQL statement, it needs to be
'formatted for VBA and it also needs to be on one line.
'example, you would set the control source of a text box to the following
'=DAOLookup("Select ls_number FROM FROM ls INNER JOIN ls_sort ON ls.ls_id = ls_sort.ls_id WHERE ls_sort.number =" & forms!frmMenu!combo_sort & ";")
'Please note, this function only work for single column single row sql statements
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(SQLstatement)
If Not rs.BOF Then rs.MoveFirst
If rs.BOF And rs.EOF Then Exit Function
DAOLookup = rs(0)
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
This was the way I explained it to my boss. "You can have a DLookUp() function as a control source of a text box. Why not just write a function that does a query and use the function as the control source?" Give it a try, it fixed my situation.
这是我向老板解释的方式。“您可以将 DLookUp() 函数用作文本框的控件源。为什么不编写一个执行查询的函数并将该函数用作控件源？” 试一试，它解决了我的情况。
