CODEWITHSUNDEEP
sqlms-access
tomovam
tomovam

Reputation: 51

Docmd.Runquery Access - Expected Function or variable error

I have a SQL query in Access that looks like this:

SELECT Max(pluquo) AS Expr1 
FROM SYNC002_ACCESS;

I run this query, it is working I have my result. What I am trying to do is assign the result of that query to a Text Box. I am trying to do that by right click on the text box -> Build Event -> and writing a code in VBA.

What I though it would be a simple task, it isn't for me at least. The code in VBA looks like:

Private Sub Text5_Click()

Me.Text5 = DoCmd.OpenQuery("MAX_PLUQUO")

End Sub

I tried also:

Private Sub Text5_Click()

Me.Text5.Value = DoCmd.RunSQL("SELECT MAX(pluquo) FROM SYNC002_ACCESS")

End Sub

What ever I try I always get an error message saying: "Compile error: Expected Function or variable"

Can somebody tell me what I am doing wrong.

Upvotes: 0

Views: 563

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

Per the documentation, the OpenQuery method of the DoCmd object will either open a select query in datasheet view (in the same way as you might by double-clicking on the query in MS Access), or will run an action query - this method will not return the results of a query.

To populate a form control with the result of your query, you can either open the query as a recordset and assign the value held by the first record to your control, e.g.:

With CurrentDb.OpenRecordset("SELECT MAX(pluquo) AS Expr1 FROM SYNC002_ACCESS")
    Me.Text5 = !Expr1
    .Close
End With

(No EOF check is required since select max() will always return a record, even for an empty dataset)

Or alternatively, you can obtain the maximum value using the DMax domain aggregrate function:

Me.Text5 = DMax("pluquo","SYNC002_ACCESS")

Upvotes: 1

Related Questions