Get Form Recordsource without opening the form

Question

Does MS Access allow to get the recordsource value of the form without opening the form itself? I'm trying to optimize my code as of now, what I did is I just hide the form then get the Recordsource form query but it takes time to load since some of the forms trigger a code upon onload.

Answer 1

I'm late to the game here - I sometimes post answers months or years after the original question was posted, as I post my own solutions when a quick search of the 'Stack finds questions relevant to my own problem of the day, but no answers that I can actually use.

[UPDATE, 06 June 2016]

The 'NameMap' property is not available in document objects from Access 2010 onwards. However, 'Stacker Thunderframe has pointed out that this is now available in the 'MsysNameMap' table.

I have amended the code, and this works in Access 2010 and 2013.

[/UPDATE]

Most of a form's properties are only available when the form is open, but some are available in the form's entry in the DAO Documents collection.

The DAO 'document' is a horrible object: it won't persist in memory and you have to refer to it explicitly every time you use it:

FormName = "MyForm"
For i = 0 To Application.CodeDb.Containers("Forms").Documents(FormName).Properties.Count - 1
    Debug.Print i & vbTab & Application.CodeDb.Containers("Forms").Documents(FormName).Properties(i).Name & vbTab & vbTab & Application.CodeDb.Containers("Forms").Documents(FormName).Properties(i).Value
Next                                     

Run that snippet for your form, and you'll see a 'NameMap' property that contains a list of the form's controls, and some of the form's properties.

...In a truly horrible format which needs a binary parser. You might want to stop reading and take an aspirin, right now, before continuing.

Health Warnings:

The NameMap Property is undocumented. It is therefore unsupported and there is no guarantee that this solution will work in future versions of Microsoft Access.

The solution in my code below will stop working if the NameMap's two-byte binary label for a Record Source ever changes, or if it's locale-specific.

This is a horrible hack: I accept no liability for any effects on your sanity.

OK, here's the code:

A VBA function to return the Record Source from a closed MS-Access form:

Private Function FormRecordSource_FromNameMap(FormName As String) As String

' Reads the Record Source from the NameMap Property of the Document object for the form.


' WARNING: there is a potential error here: if the form's RecordSource property is blank
'          and it has one or more list controls with a .RecordSource property populating
'          the list, this function will return the first list control's Record Source.

' This won't work if you're using non-ASCII characters (Char > 255) in your form name.


Dim i As Integer
Dim j As Integer
Dim k As Integer

Dim arrByte() As Byte

Dim strOut As String
If Application.Version < 12 Then

    arrByte = Application.CodeDb.Containers("Forms").Documents(FormName).Properties("NameMap").Value

    For i = 1 To UBound(arrByte) - 2 Step 2

        ' 2-byte marker for a querydef in the NameMap:

        If (arrByte(i) = 228 And arrByte(i + 1) = 64) Then


            j = i + 2
            Do While arrByte(j) = 0 And arrByte(j + 1) = 0 And j < UBound(arrByte)
                ' loop through the null chars between the marker and the start of the string
                j = j + 2
            Loop


            strOut = ""
            Do Until (arrByte(j) = 0 And arrByte(j + 1) = 0) Or j >= UBound(arrByte) - 2
                If arrByte(j) = 0 Then j = j + 1
                ' loop until we reach the null char which terminates this string
                ' appending the Bchars (not unicode Wchars!) of the table or query
                strOut = strOut & Chr(arrByte(j))
                j = j + 2
            Loop


            Exit For ' we only want the first datasource
        End If


    Next i

Else

    arrByte = Nz(DLookup("[NameMap]", "[MSYSNameMap]", "[Name] = '" & FormName & "'"), vbNullChar)


    If UBound(arrByte) < 4 Then Exit Function


    strOut = ""
    For j = 60 To UBound(arrByte) - 2 Step 2


        If arrByte(j) = 0 And arrByte(j + 1) = 0 Then Exit For


        strOut = strOut & Chr(arrByte(j))


    Next j


End If


frmRecordSource_FromNameMap = strOut

Erase arrByte

End Function

If you use the RecordSource in (say) OpenRecordset or a DCOUNT function, I would advise you to encapsulate it in square brackets: you might get the name of a hidden query object saved from a 'SELECT' statement in the RecordSource, and that name will contain '~' tilde characters which need special handling.

And now, something extra that you didn't ask for, but other people will be looking for if they Googled their way here for 'MS Access RecordSource for a closed form':

Getting an MS-Access form's RecordSource, whether it's open or not

Most times, your form will be open. Problem is, you don't know that... And if it's a subform, it might not be visible in the Forms() collection. Worse, a form that's hosted as a subform might exist as multiple instances in several open forms.

Good luck with that, if you're looking to extract dynamic properties... Like filters, or the Record Source if it's set 'on the fly' by VBA.

Public Function GetForm(FormName As String, Optional ParentName As String = "") As Form
' Returns a form object, if a form with a name like FormName is open
' FormName can include wildcards.
' Returns Nothing if no matching form is open.

' Enumerates subforms in open forms, and returns the subform .form object if
' it has a matching name. Note that a form may be open as multiple instances
' if more than one subform hosts it; the function returns the first matching
' instance. Specify the named parent form (or the subform control's name) if
' you need to avoid an error arising from multiple instances of the form.

Dim objForm As Access.Form

If ParentName = "" Then
    For Each objForm In Forms
        If objForm.Name Like FormName Then
            Set GetForm = objForm
            Exit Function
        End If
    Next
End If

If GetForm Is Nothing Then
    For Each objForm In Forms
        Set GetForm = SearchSubForms(objForm, FormName, ParentName)
        If Not GetForm Is Nothing Then
            Exit For
        End If
    Next
End If

End Function

Private Function SearchSubForms(objForm As Access.Form, SubFormName As String, Optional ParentName As String = "") As Form
' Returns a Form object with a name like SubFormName, if the named object SubFormName is subform
' of an open form , or can be recursively enumerated as the subform of an open subform.

' This function returns the first matching Form: note that a form can be instantiated in multiple
' instances if it is used by more than one subform control.

Dim objCtrl As Control
For Each objCtrl In objForm

    If TypeName(objCtrl) = "SubForm" Then
    
        If objCtrl.Form.Name Like SubFormName Then
            If ParentName = "" Or objForm.Name Like ParentName Or objCtrl.Name Like ParentName Then
                Set SearchSubForms = objCtrl.Form
                Exit For
            End If
        Else
            Set SearchSubForms = SearchSubForms(objCtrl.Form, SubFormName, ParentName)
            If Not SearchSubForms Is Nothing Then
                Exit For
            End If
        End If
    
    End If

Next objCtrl

End Function

Public Function FormRecordSource(FormName As String, Optional ParentName As String = "") As String
' Returns the Recordsource for a form, even if it isn't open in the Forms() collection

' This will look for open forms first. If you're looking for a subform, you may need a
' parent name for the form which hosts the subform: your named form might be open as a
' subform instance in more than one parent form.

' WARNING: there is a potential error here: if the form isn't open, and it has a blank
'          RecordSource property, and it has one or more controls with a .RecordSource
'          property populating a list, a list control's RecordSource could be returned

Dim objForm As Form

If FormName = "" Then
    Exit Function
End If

Set objForm = GetForm(FormName, ParentName)

If objForm Is Nothing Then
    FormRecordSource = FormRecordSource_FromNameMap(FormName)
Else
    FormRecordSource = objForm.RecordSource
    Set objForm = Nothing
End If

End Function

Share and enjoy: and please accept my apologies for any unwanted line breaks in the code sample.

Answer 2

One option would be to save the Record Source of the form as a Query. Say you have a form named [AgentForm] whose Record Source is

SELECT ID, AgentName FROM Agents

In your development .accdb copy of the database, open the form in Design View and open the Record Source in the Query Builder. Click the "Save As" button ...

and save the query as "AgentForm_RecordSource". Now the Record Source property of the form is just a reference to the saved query, and the query itself can be accessed directly through a QueryDef object. So, you could retrieve the SQL statement for the form's Record Source with

Dim cdb As DAO.Database, qdf As DAO.QueryDef, sql As String
Set cdb = CurrentDb
Set qdf = cdb.QueryDefs("AgentForm_RecordSource")
sql = qdf.SQL

or you could go ahead and open a Recordset with

Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
Set cdb = CurrentDb
Set qdf = cdb.QueryDefs("AgentForm_RecordSource")
Set rst = qdf.OpenRecordset

Answer 3

If the form's Record Source is a SELECT statement rather than the name of a table or saved query, you can check the QueryDefs collection for the hidden QueryDef which Access created for that Record Source statement.

If it exists, you can check its .SQL property.

strFormName = "Form15"
? CurrentDb.QueryDefs("~sq_f" & strFormName).SQL
SELECT DISTINCTROW *
FROM [DB Audits];

You can trap error #3265, "Item not found in this collection", which will be thrown if that QueryDef does not exist.

Answer 4

Since you can't open your form in design view and opening your form regularly is causing performance issues, there are but a few more workarounds:

Depending on how you want to check for the closed form's recordsource, you can set a global variable in the following way, in a separate module:

Public glb_getrecordsource As String

Afterwards, depending on how you call the code, you can do the following:

Private Sub Command1_Click()
glb_getrecordsource = "Yes"
DoCmd.OpenForm "Form1"

'... Do something

End Sub

Then, as the final step, put the following at the beginning of your form's OnLoad event:

Private Sub Form_Load()
If glb_getrecordsource = "Yes" Then
    glb_getrecordsource = Me.Form.RecordSource
    DoCmd.Close acForm, "Form1", acSaveYes
    Exit Sub
End If

'... Usual OnLoad events

End Sub

This will at least solve the performance issues, since you will not trigger any of the time consuming events, in the form's load event.

Another workaround: You can export your form to a .txt file and then search the text file for the recordsource. The following code will export your forms to .txt files in a specified folder:

Dim db As Database
Dim d As Document
Dim c As Container
Dim sExportLocation As String

Set db = CurrentDb()

sExportLocation = "C:\AD\" 'Do not forget the closing back slash! ie: C:\Temp\
Set c = db.Containers("Forms")
    For Each d In c.Documents
        Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
    Next d

Code partly borrowed from this forum. Afterwards, you only have to open the file and search for the recordsource. If the recordsource is empty it will not be exported, so keep that in mind. Also, I doubt this will improve perfomance, but who knows!