代码之家 › 专栏 › 技术社区 › Jambobond

SQL作为访问表单字段的控制源

access-vba ms-access sql

Jambobond · 技术社区 · 16 年前

是否有任何方法可以使用SQL填充访问表单的文本feild值?

我已经读到,不可能简单地输入SQL作为控制源。这是真的吗?

谢谢你的帮助:)

--编辑——

我需要执行这个查询;

SELECT tblCaseIssues.IssueDesc FROM tblCaseIssues INNER JOIN tblCaseNewHS_Issues ON tblCaseIssues.ID = tblCaseNewHS_Issues.IssueID WHERE(tblCaseNewHS_Issues.HS_ID = 81))

6 回复 | 直到 9 年前

JeffO 16 年前

很确定这是正确的SQL,但您可以使用以下函数:
=DLookUp("field_name","table_name","any_fieldname = 'value'")

David Walker 16 年前

您可以将字段的控制源设置为函数名。该函数可以轻松地执行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

Albert D. Kallal 16 年前

只需接受您的SQL查询并将其保存为查询即可。

然后在文本框中,只需放置:

=(dlookup(issuesdesc,查询名称))

我对所有这些海报的损失相当大,这些海报暗示了什么代码都不需要。只需将SQL保存为一个查询,然后使用dlookup()函数作为文本框的数据源即可。

Fionnuala 16 年前

使用组合框并将行源设置为查询可能是最简单的,或者DAO是本地访问的。

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

EJoshuaS - Stand with Ukraine 9 年前

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

Daniel L. VanDenBosch Harry S 9 年前

我创建了以下函数来解决这个问题。我喜欢这个解决方案,因为您不必处理保存的查询阻塞导航窗格或长时间的解决方案。

    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

我就是这样跟老板解释的。”可以使用dlookup()函数作为文本框的控制源。为什么不编写一个执行查询并使用该函数作为控制源的函数呢?” 试一试,它改变了我的处境。