' MakeMyQuery
' http://www.utteraccess.com/wiki/index.php/MakeMyQuery
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2011-10-20                    
'
'--------------------- Run_MakeMyQuery
Sub Run_MakeMyQuery()
'code to modify for making or changing a query
'111020 Crystal (strive4peace)

  'press CTL-G before you run if you want to watch
  'the SQL statement being recorded

  'click HERE    press F5 to Run
  '              or choose : Run, Run Sub/Userform : from menu
 
  On Error GoTo Proc_Err
 
  Dim sSQL As String _
     , varWhere As Variant
     
  varWhere = Null
 
  '-------------------------------
    'YOUR statements to determine WHERE clause
  varWhere = "Left(NameLast,1) = 'A'"
  '-------------------------------
 
  'this is YOUR SQL statement before the WHERE clause
  sSQL = "SELECT t_PEOPLE.PID, t_PEOPLE.NameLast, t_PEOPLE.NameFirst " _
     & " FROM t_PEOPLE "
     
  If Not IsNull(varWhere) Then
     sSQL = sSQL & " WHERE " & varWhere
  End If
 
  'this is YOUR SQL statement after the WHERE clause
  sSQL = sSQL _
     & " ORDER BY t_PEOPLE.NameLast, t_PEOPLE.NameFirst;"
     
  MakeMyQuery "MyQuery", sSQL

  'remove this statement if you do not want to open the query
  DoCmd.OpenQuery "MyQuery"
 
Proc_Exit:
  Exit Sub
 
Proc_Err:
  MsgBox Err.Description, , _
    "ERROR " & Err.Number & "  Run_MakeMyQuery"
   
  Resume Proc_Exit

  'if you want to single-step code to find error, CTRL-Break at MsgBox
  'then set this to be the next statement
  Resume
End Sub

'--------------------- MakeMyQuery
Sub MakeMyQuery( _
  ByVal qName As String _
  , ByVal pSql As String _
  )

  'if query already exists, update the SQL statement
  'otherwise, create the query

  'modified 10-20-11
  'crystal (strive4peace)

  On Error GoTo Proc_Err

' note: after this runs, press Ctrl-G to look at your full SQL statement
' paste it into a query and run from the SQL window to debug your logic
Debug.Print pSql

  Dim db As DAO.Database
  Set db = DBEngine(0)(0)
 
   If Nz(DLookup("[Name]", "MSysObjects", _
       "[Name]='" & qName _
       & "' And [Type]=5"), "") = "" Then
       db.CreateQueryDef qName, pSql
   Else
      'if query is open, close it
      On Error Resume Next
      DoCmd.Close acQuery, qName, acSaveNo
      On Error GoTo Proc_Err
      db.QueryDefs(qName).SQL = pSql
   End If
 
Proc_Exit:
  On Error Resume Next
     If Not db Is Nothing Then
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
        Set db = Nothing
     End If
  Exit Sub
 
Proc_Err:
  MsgBox Err.Description, , _
    "ERROR " & Err.Number & "  MakeMyQuery"
   
  Resume Proc_Exit

  'if you want to single-step code to find error, CTRL-Break at MsgBox
  'then set this to be the next statement
  Resume
End Sub
'--------------------------
'--------------------------