SQL as Control Source for Access Form field

Question

Is there any way populate an Access Form's text feild's value using SQL?

I have read that it is not possible to simply enter SQL as the Control Source. Is this true?

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))

Lumumba

Is there some reason the data shouldn't be included in the form's underlying recordsource? – Zonate

Answer 1

Pretty sure that is true SQL, but you could use the function:
=DLookUp("field_name","table_name","any_fieldname = 'value'")

Colleen

Is it possible to do something as complex as SELECT tblCaseIssues.IssueDesc FROM tblCaseIssues INNER JOIN tblCaseNewHS_Issues ON tblCaseIssues.ID = tblCaseNewHS_Issues.IssueID WHERE (tblCaseNewHS_Issues.HS_ID = 81) with such a function? – Lumumba

Create a query in Access where all the tables are joined. Then in your DBLookup, you can substitue table_name with the name of the query (Sorry indicating a table and assuming a query would work as well). Note: there are better ways of doing in VBA. – Colleen

Answer 2

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.

=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

Recital

Answer 3

It might be easiest to use a combobox and set the Row Source to your query, alternatively, DAO is native to 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

Treatment

Answer 4

Just take your sql query and save it as a query.

Then in the text box, just place:

=(dlookup(“IssuesDesc”,”name of query”))

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.

Lith

Because the first answer already suggested your solution, yet the OP was still seeking a method that he could utilize a SQL statement as the source of his query. – Recital

Answer 5

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

Galloglass

It's better to include some explanation/context rather than just having a code-only answer. – Slide

Welcome to Stack Overflow! While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. Code-only answers are discouraged. – Gallaway

Answer 6

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.

Nock