Cannot get Sql to run in VBA

[bogglebeats]

Hello i cannot get this statement to execute in vba:

Private Sub Operation_AfterUpdate()

DoCmd.RunSQL ("SELECT Last(UnitRate.UnitRate) AS LastOfUnitRate FROM UnitRate RIGHT JOIN EmpTicket ON UnitRate.Operation = EmpTicket.Operation;")

End Sub

i get the error: a run sql action requires an argument consisting of an sql statement.


Please help!

ameen

 

[Mile-O]

To use the RunSQL method you need to use an action query (i.e. UPDATE, DELETE, APPEND, MAKETABLE)

The SQL you have above is a basic SELECT query (not action) and therefore won't work.

As it's just a bit of SQL - and because it's not dynamic in any way, you'd be best just making the query and saving it along with the others. Constant running of such code can cause the size of a database to bloat if not compacted regularly, and a stored query when run doesn't take up anymore space than what it does already.

 

[bogglebeats]

the reason i wanted to use this is so i can actually have a query run. when i use docmd.openquery in vba or even open a query myself manually, access opens the query without running it so i don't have updated information. My problem is that the query is not updated based on new values entered into the table. Is there another was to RUN a query based on a new value entered in the table without having to open it, THEN run it?