Putting SQL statement into control source of MS Access unbound text box

Hi experts,

I am trying to place an SQL into an unbound field on my form.  The SQL is intended to return the maximum date in a field where moduleID = 1.   My SQL is as follows:

=(SELECT Max([DCTargetCompletionDate]) AS Expr1 FROM KControl WHERE ModuleID=1;)

I am getting #Name as a result and do not know how to get round this.

Can anybody help?

Thank you.
Terry
TerenceHewettAsked:
Who is Participating?
 
pmctrekCommented:
The problem is that bound objects like text boxes can only access the forms recordset.  If the data you want is outside the recordset then you need to look it up some other way.  The attached code is a simple way to set the vaule of a field as the form is being rendered.
Private Sub Form_Current()
    Dim intI As Integer
    Dim fld As Field
 
    Set rst = New ADODB.Recordset
    rst.Open "SELECT Max([DCTargetCompletionDate]) AS CompDate FROM KControl WHERE ModuleID=1;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Text0.Value = rst.Fields("CompDate")
End Sub

Open in new window

0
 
pmctrekCommented:
Why dont you add the SELECT to the datasource for the form and bind the test box to that new field?
0
 
JimFiveCommented:
Try =DMax("[DCTargetCompletionDate]", "KControl","ModuleID=1")
--
JimFive

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.