When I develop, I always, initially, build my SQL statements directly within my form’s Record Source. Now, it is a best practice to always create an independent query and base form’s off of the query rather than use an embedded SQL Statement. This is all fine a dandy when your db has 5-10 forms, but when you are working on databases with 10s or 100s of forms, this can become quite a tedious task to convert all of the SQL statements into queries and then reassign the newly created queries to their respective forms. So, I thought about it for a couple minutes and quickly realized that this could actually all be automated quite easily. As such, I developed the following procedure.

'---------------------------------------------------------------------------------------
' Procedure : ConvertSQL2QRY
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Save recordsource to queries and update recordsource to use the query
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
' Dependencies: CreateQry()
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-30              Initial Release
' 2         2022-11-18              Fixed Error Handler Issue
'---------------------------------------------------------------------------------------
Sub ConvertSQL2QRY()
On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
'    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim dbo              As AccessObject
    Dim dbp              As Object
    Dim sFrmName         As String
    Dim sFrmRS           As String
    Dim sQryRS As String

    Set db = CurrentDb
    Debug.Print "ConvertSQL2QRY Begin"
    Debug.Print "================================================================================"

    'Check Forms
    For Each frm In CurrentProject.AllForms
        sFrmName = frm.Name
        DoCmd.OpenForm sFrmName, acDesign
        sFrmRS = Forms(sFrmName).RecordSource
        Debug.Print "Processing Form: " & sFrmName
        If Len(sFrmRS) > 0 And Left(sFrmRS, 4) <> "qry_" Then
            Debug.Print "   Converting Form: " & sFrmName
            If Left(sFrmRS, 7) = "SELECT " Then
                sQryRS = sFrmRS
            Else
                sQryRS = "SELECT [" & sFrmRS & "].* FROM [" & sFrmRS & "];"
            End If
            'Create a query based on the active RS and name it based on the form name for
            '   traceability
            CreateQry "qry_" & sFrmName, sQryRS
            'Change the form RS to use the newly created query
            Forms(sFrmName).RecordSource = "qry_" & sFrmName
        End If
        DoCmd.Close acForm, frm.Name, acSaveYes
    Next frm

    Debug.Print "================================================================================"
    Debug.Print "ConvertSQL2QRY End"

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set dbp = Nothing
    Set dbo = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: ConvertSQL2QRY" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

What does this procedure do? Well, it will go through all the forms in your database and create a query using the current record source and then reassign the newly created query as the form’s record source. I run this query just before deploying a database. It is also intelligent enough to only process those forms that don’t already have a query created, so it can be rerun whenever needed should I add new forms, etc…

Please note this procedure is dependent on my CreateQry procedure, so be sure to get it as well!