By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

How do i run a query with VBA code

P: 67
Hello everyone

I want to run a query from a field event like OnLostFocus. Could someone please show me how this is coded. I am also trying to open the query in a new form in datasheet view but it opens in form view. Do i need to make this a subform or is there a way round it. Please advise.

Regards

Wayne
Feb 12 '09 #1
Share this Question
Share on Google+
5 Replies


DonRayner
Expert 100+
P: 489
Hi Wayne;

Take a look at the RunSQL method in Access help. It provides a good explanation on running your SQL code through VBA. If you are not confident in your ability to generate the SQL code yourself you can always use the Query builder to create your query and then copy the SQL code from it. Here is the example directly from Access Help.

Expand|Select|Wrap|Line Numbers
  1. Public Sub DoSQL()
  2.  
  3.     Dim SQL As String
  4.  
  5.     SQL = "UPDATE Employees " & _
  6.           "SET Employees.Title = 'Regional Sales Manager' " & _
  7.           "WHERE Employees.Title = 'Sales Manager'"
  8.  
  9.     DoCmd.RunSQL SQL
  10.  
  11. End Sub
Feb 12 '09 #2

P: 67
Hi DonRayner

I will give it a try and see what i can come up with.

Thanks
Feb 12 '09 #3

NeoPa
Expert Mod 15k+
P: 31,398
There are a number of different ways, for different types of query, but the two routines below should get you going either with a pre-defined QueryDef object (Query) or some actual SQL (for action queries only).
Expand|Select|Wrap|Line Numbers
  1. 'RunQuery executes the query and displays the query name.
  2. Public Function RunQuery(strQuery As String) As Boolean
  3.     On Error GoTo RQError
  4.     RunQuery = False
  5.     Call Echo(True, "Running query '" & strQuery & "'.")
  6.     Debug.Print "RunQuery(" & strQuery & ")", _
  7.                 "Started at "; Format(Now(), "HH:nn:ss"),
  8.     Call DoCmd.OpenQuery(strQuery, acViewNormal, acEdit)
  9.     Debug.Print "and finished at "; Format(Now(), "HH:nn:ss")
  10.     Call Echo(True, "Query '" & strQuery & "' finished.")
  11.     Exit Function
  12.  
  13. RQError:
  14.     RunQuery = True
  15.     Call MsgBox("Unable to run query """ & strQuery & """.", _
  16.                 vbExclamation Or vbOKOnly, "RunQuery")
  17. End Function
  18.  
  19. 'RunSQL executes the SQL code and displays the query name.
  20. Public Function RunSQL(strSQL As String, _
  21.                        Optional strQuery As String = "", _
  22.                        Optional blnUseTrans As Boolean = False) As Boolean
  23.     If strQuery = "" Then strQuery = strSQL
  24.     On Error GoTo RSError
  25.     RunSQL = False
  26.     Call Echo(True, "Running SQL query '" & strQuery & "'.")
  27.     Debug.Print "RunSQL(" & strQuery & ")", _
  28.                 "Started at "; Format(Now(), "HH:nn:ss"),
  29.     Call DoCmd.RunSQL(SQLStatement:=strSQL, UseTransaction:=blnUseTrans)
  30.     Debug.Print "and finished at "; Format(Now(), "HH:nn:ss")
  31.     Call Echo(True, "SQL query '" & strQuery & "' finished.")
  32.     Exit Function
  33.  
  34. RSError:
  35.     RunSQL = True
  36.     Call MsgBox("Unable to run query """ & strQuery & """.", _
  37.                 vbExclamation Or vbOKOnly, "RunSQL")
  38. End Function
Feb 12 '09 #4

P: 67
Thankyou for your input. It is very much appreciated.

Wayne
Feb 13 '09 #5

NeoPa
Expert Mod 15k+
P: 31,398
No problems Wayne.

I'd already had to knock something up for my own use, so I figured I may as well share it.
Feb 13 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.