locked
Find which form field points to which database fields in MS Access RRS feed

  • 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?

    Monday, December 5, 2016 6:33 AM

All replies

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

    Hi xameeramir,

    You can look at the ControlSource of the control in relation to the RecordSource of the form.

    Imb.

    Monday, December 5, 2016 7:01 AM
  • Hi,

    You could use macro:

    Sub listFieldSource()
    Set db = Application.CurrentProject
    For Each frms In db.AllForms
    Set frm = Forms(frms.name)
    For Each ctl In frm.Controls
    On Error Resume Next
    Debug.Print ctl.name & " is from " & frm.RecordSource & "!" & ctl.ControlSource
    Err.Clear
    Next
    Next frms
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 7, 2016 12:08 PM
  • Actually, your question is not really much strange at all.

    When you are introduced to an un-familiar appclation, then obviously the requirement exists to figure out what form is attached to what table. This allows one to determine how the application was designed and MORE important allow one to determine how the given form works and thus opens the door as to how one can add new features and make changes to the application in question.

    Note how in the above I stated something RATHER important:

    In general, a form is attached to ONE table, and then on that form we have “controls” on that form that point to ONE column in that forms data source.

    What the above means that in general, it is MORE important to determine what table (or query) a given form is attached to. And as noted in 99% of the cases that form will be attached to ONE table, and then each control on that form points to one column.

    So you want to “dump” this general idea that a form has many controls each pointing to different tables/databases – this is NOT the case and RARELY is the case. So do NOT go down such a road of discovery – you only find that every control on a given form is 99% of cases will point to the SAME and SINGLE ONE table that all other controls point to – that “one table” is called the forms data source.

    A form can only have ONE data source. So you not need to determine which of say 50 controls on a form point to the underlying table – since they all will.

    The only exception to the above is when a form has a sub form. However, even when a sub form is used, that sub form follows the SAME above rule: One form (or sub form) ONLY points to one table. This rule is rare broken.

    For say a form that displays a customer invoice, you will note that typically such a setup has a main form (the invoice main information such as customer, date of invoice etc.). And then you have a “repeating” part – this would be the invoice details table and thus represented by what we call a sub form. Once again, note how there is not just willy nilly a bunch of controls that can point to multiple tables/databases – that is NEVER a common design. So you have something like this:

    In above, you have the main part with invoice number, invoice date, PO etc. That ALL COMES from one table. And then the lower “repeating” part with Qty and price is another form (a sub form, and ALL OF the controls again point to ONE table – the invoice details table).

    So in general:
    One form points to one table.
    That form will have multiple controls that each point to the above SINGLE table.

    For forms that display information from MORE than one table, or the so called classic “one to many” database design, you will STILL find that EACH form STILL points to ONE table, but the form is comprised of two parts:

    Main form part: - this would point or the correct term in Access is called “bound to” a single table. In this case the invoice “main” information, or say table tblInvoices.

    You then will have invoice details, likely a continues form bound to another table called tblInvoiceDetails.

    So for 99% of cases, you ONLY require to find out what table/query the FORM is bound to, since then EVERY and ALL controls on that form will ALWAYS point to that given SINGLE AND ONE table/query.

    The easy way to do above is to simply open up the form in question into design mode and look at the forms data source. (Display the forms property sheet).

    You can also use the database documenter that will print out each form, the data source and all controls and their data source for the given application. You can find this option in the ribbon area under
    Database Tools, and then choose database documenter:

    So the above IS A BUILT IN tool that will display and print out all of this information. However, the above option will typically print out a whole paper tray of information – most of which is over kill. But if you like cool mountains of paper, then the database documenter is your friend!

    I don’t think such an approach is a good way to discover and learn how an application works. I mean, if you need to learn the streets and how to get around in YOUR neighborhood, a printout of every street and avenue for your WHOLE city will do you VERY little in terms of helping you get around the one neighborhood.

    The same goes for learning an access application.

    I don’t suggest a control by control approach and determine where “each” control points to in terms of its data source. As I stated, you are FAR better off to determine the data source of the form and then ASSUME that all controls point to that table (since that is the case 99% of the time). So learn where the ONE form points to, and then assume every control on that form points to an underlying column of that ONE table/query the form is based on.

    By adopting a form by form approach, you will reduce your learning workload by 1000+ times to determine how the underlying application works. So key concept in Access is what table/query is a form bound to? And keep in mind that form will ONLY be bound to the one table or query - a single table in most cases.

    Good luck.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Chenchen Li Thursday, December 8, 2016 9:11 AM
    Thursday, December 8, 2016 1:22 AM
  • So in general:
    One form points to one table.

    Albert, do you really mean this?  I have seen many applications, and built many applications, where some forms are based on queries of multiple tables.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, December 8, 2016 7:25 PM
  • Here's simple procedure I once wrote to list all the bound controlsources on a given open form:

    Sub ListBoundControlSources(frm As Access.Form, Optional outputFile As String)
    
        Dim ctl             As Access.Control
        Dim fld             As DAO.Field
        Dim objRSQDF        As Object  ' may be set either to a recordset or to a querydef
        Dim strCtlSource    As String
        Dim strRecordSource As String
        Dim fileNo          As Integer
        Dim blnOpened       As Boolean
        
        If Len(outputFile) > 0 Then
            fileNo = FreeFile()
            Open outputFile For Append As #fileNo
        End If
        
        If frm.RecordSource <> "" Then
                
            If fileNo Then
                Print #fileNo, "+++ Form '" & frm.Name & "'"
            Else
                Debug.Print "+++ Form '" & frm.Name & "'"
            End If
            
            If frm.CurrentView = 0 Then
                ' in design view
                strRecordSource = Trim(frm.RecordSource)
                If Right(strRecordSource, 1) = ";" Then
                    strRecordSource = Left(strRecordSource, Len(strRecordSource) - 1)
                ElseIf Mid$(strRecordSource, Len(strRecordSource) - 1) = ";" Then
                    strRecordSource = Left(strRecordSource, Len(strRecordSource) - 2)
                End If
                Set objRSQDF = CurrentDb.CreateQueryDef("", "SELECT * FROM (" & strRecordSource & ") WHERE 0")
                blnOpened = True
            Else
                Set objRSQDF = frm.Recordset
                blnOpened = False
            End If
        
            For Each ctl In frm.Controls
            
                strCtlSource = vbNullString
                On Error Resume Next
                strCtlSource = ctl.ControlSource
                On Error GoTo 0
        
                If Len(strCtlSource) > 0 And Left(strCtlSource, 1) <> "=" Then
                    
                    On Error GoTo ERR_NO_FIELD
                    
                    With objRSQDF.Fields(strCtlSource)
                
                        If fileNo Then
                            Print #fileNo, "Control: "; ctl.Name, "Table: "; .SourceTable, "Field: "; .SourceField
                        Else
                            Debug.Print "Control: "; ctl.Name, "Table: "; .SourceTable, "Field: "; .SourceField
                        End If
                        
                    End With
    NEXT_CONTROL:
                    On Error GoTo 0
    
                End If
            
            Next ctl
                    
            If blnOpened Then
                objRSQDF.Close
            End If
            
            Set objRSQDF = Nothing
    
        End If
        
    Exit_Point:
        If fileNo Then
            Close fileNo
        End If
        Exit Sub
        
    ERR_NO_FIELD:
        If fileNo Then
            Print #fileNo, "*** ERROR: Control '" & ctl.Name & "' is bound to unknown field '" & strCtlSource & "'"
        Else
            Debug.Print "*** ERROR: Control '" & ctl.Name & "' is bound to unknown field '" & strCtlSource & "'"
        End If
        Resume NEXT_CONTROL
       
    End Sub
    

    And here's a procedure to call that procedure for all forms:

    Sub ListAllFormBoundControlSources(Optional outputFile As String)
    
        Dim ao          As Access.AccessObject
        Dim blnOpened   As Boolean
        
        For Each ao In CurrentProject.AllForms
        
            If Not ao.IsLoaded Then
                DoCmd.OpenForm ao.Name, acDesign, WindowMode:=acHidden
                blnOpened = True
            Else
                blnOpened = False
            End If
        
            ListBoundControlSources Forms(ao.Name), outputFile
        
            If blnOpened Then
                DoCmd.Close acForm, ao.Name, acSaveNo
            End If
            
        Next ao
        
        MsgBox "Done!"
    
    End Sub
    

    That's pretty quick & dirty, and I make no warranty as to its function or fitness, but maybe you'll find it useful.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, December 8, 2016 8:59 PM