Find which form field points to which database fields in MS Access

Question

Let me explain the WEIRDEST client requirement, we're scratching our heads for:

We have an MS Access VBA application with thousands of forms fields in hundreds of forms.

A few fields in these forms populates data from few tables/queries.

A few other fields in forms inserts data to few tables through queries/direct code.

Notice that these tables are linked tables to SQL Server tables.

Is there a way to find which form field is related to which table column in?

Hence, we need some tool/macro to do this.

How do we find which form field points to which database fields in MS Access?

Based on @ClintB's answer, we have prepared the following code. The values in ctl.ControlSource doesn't seems to be referring to actual database objects:

Sub GetFormFieldToDBFieldMapping()
Dim frm As Object
Dim LiveForm As Form
Dim ctl As control
Dim i As Integer
Dim fso As Object
Dim ctlSource As String
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile("D:\ControlSources.txt")

    For Each frm In Application.CurrentProject.AllForms

        'To access form controls, open it
        DoCmd.OpenForm frm.Name, acViewDesign
        Set LiveForm = forms(frm.Name)
        For i = 0 To LiveForm.Controls.Count - 1
        Set ctl = LiveForm.Controls.Item(i)
        If ctl.ControlType = 106 Or ctl.ControlType = 111 Or ctl.ControlType = 110 Or ctl.ControlType = 109 Then
        ctlSource = ctlSource & vbCrLf & "Form Name :" & LiveForm.Name & ": Control Name :" & ctl.Name & ": Control Source :" & ctl.ControlSource
        End If
        Next i
        'Do not forget to close when you are done
        DoCmd.Close acForm, frm.Name
    Next

oFile.WriteLine ctlSource
oFile.Close
Set fso = Nothing
Set oFile = Nothing

End Sub

That will be close to impossible, as fields can be used in recordsets and DLookup and functions, which again can be used in queries, which can be aliased in other queries and used in expressions before reaching a control as the ControlSource. — Gustav
For a standard form bound to a table or a simple query, ControlSources will be field names, so some can be found this way. The tricky part is to catch all occurrences. The only method I've found is hard work - and brute force by renaming a query and see what's happening. Not nice. You can use the function SaveAsText to save objects to text files which can make searching a bit easier. — Gustav

ClintB ClintB · Accepted Answer · 2016-12-05T06:33:49

I Would do something like this. (not actual code)

For each form in db
   For each control in form
       'Write a record to a table stating which formName, controlName and the control.controlSource
   Next
Next

Edit: ControlSource, not RowSource

Find which form field points to which database fields in MS Access

2 Answers