SQL as Control Source for Access Form field
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
Pretty sure that is true SQL, but you could use the function:=DLookUp("field_name","table_name","any_fieldname = 'value'")
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