How do i run a query with VBA code

[Wayneyh]

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

[DonRayner]

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.

Public Sub DoSQL()

    Dim SQL As String
    
    SQL = "UPDATE Employees " & _
          "SET Employees.Title = 'Regional Sales Manager' " & _
          "WHERE Employees.Title = 'Sales Manager'"

    DoCmd.RunSQL SQL

End Sub

[Wayneyh]

Hi DonRayner

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

Thanks

[NeoPa]

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).

'RunQuery executes the query and displays the query name.
Public Function RunQuery(strQuery As String) As Boolean
    On Error GoTo RQError
    RunQuery = False
    Call Echo(True, "Running query '" & strQuery & "'.")
    Debug.Print "RunQuery(" & strQuery & ")", _
                "Started at "; Format(Now(), "HH:nn:ss"),
    Call DoCmd.OpenQuery(strQuery, acViewNormal, acEdit)
    Debug.Print "and finished at "; Format(Now(), "HH:nn:ss")
    Call Echo(True, "Query '" & strQuery & "' finished.")
    Exit Function

RQError:
    RunQuery = True
    Call MsgBox("Unable to run query """ & strQuery & """.", _
                vbExclamation Or vbOKOnly, "RunQuery")
End Function

'RunSQL executes the SQL code and displays the query name.
Public Function RunSQL(strSQL As String, _
                       Optional strQuery As String = "", _
                       Optional blnUseTrans As Boolean = False) As Boolean
    If strQuery = "" Then strQuery = strSQL
    On Error GoTo RSError
    RunSQL = False
    Call Echo(True, "Running SQL query '" & strQuery & "'.")
    Debug.Print "RunSQL(" & strQuery & ")", _
                "Started at "; Format(Now(), "HH:nn:ss"),
    Call DoCmd.RunSQL(SQLStatement:=strSQL, UseTransaction:=blnUseTrans)
    Debug.Print "and finished at "; Format(Now(), "HH:nn:ss")
    Call Echo(True, "SQL query '" & strQuery & "' finished.")
    Exit Function

RSError:
    RunSQL = True
    Call MsgBox("Unable to run query """ & strQuery & """.", _
                vbExclamation Or vbOKOnly, "RunSQL")
End Function

[Wayneyh]

Thankyou for your input. It is very much appreciated.

Wayne

[NeoPa]

No problems Wayne.

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