Track Changes to Access Form Properties in Source Control (Github)

Last month I blogged about some code I borrowed and changed and used to track changes to code in Access databases in Github. Since then I have also added code to extract Access Queries along with Properties on Forms and Reports to track all of those in Github also. As maintainers of Access databases know, it is often difficult to identify what changes you intentionally or unintentionally made that may be causing the app to no longer behave as it once did. The code below helps.

I put this code in a github repo, so you can see the current version there: AccessUtilities/TrackAccessChanges.bas at main · RobKraft/AccessUtilities (github.com)

But you can also just copy and paste the code below and stick it in a .bas file and run it.

Run this code to export Form Properties, Report Properties, code from Forms, modules, and reports, along with query sources to external files. Then you can check the files into a source control repo. Do this daily and it gives you a good way to keep track of all the changes you made to your access database over time.

Note that this does not include ALL form and report properties, but you can change the code below if you want ALL of them.

Option Compare Database
    Dim debuggin As Boolean
    Dim filepath As String

Sub GatherInfo()
    debuggin = False
    filepath = CurrentProject.Path & "\"
    
    ExportAllCode
    robListAllFormProps
    robListAllReportProps
    robListAllQuerySQL
End Sub
Sub robListAllReportProps()
    Dim rpt As Report
    Dim reportIsLoaded As Boolean
    Dim outputThisProp As Boolean
    
    On Error Resume Next
    
    For Each rptHolder In Application.CurrentProject.AllReports
        reportIsLoaded = False
        For Each aLoadedReport In Application.Reports
            If aLoadedReport.Name = rptHolder.Name Then
                reportIsLoaded = True
            End If
        Next aLoadedReport
        
        If reportIsLoaded = False Then
            DoCmd.OpenReport rptHolder.Name, acViewDesign, , , acHidden
            If Err.Number <> 0 Then
                If debuggin Then
                    Debug.Print "Unable to analyze report: " & rptHolder.Name & " probably because of needing a specific printer. " & Err.Description
                Else
                    Print #1, "Unable to analyze report: " & rptHolder.Name & " probably because of needing a specific printer. " & Err.Description
                End If
            End If
        End If
        
        Set rpt = Application.Reports(rptHolder.Name)
        If debuggin Then
            Debug.Print rpt.Name
            Debug.Print "RecordSource = " & Trim(rpt.RecordSource)
            Debug.Print "Filter = " & Trim(rpt.Filter)
            ProcessFormOrReportMethods rpt.Properties
            Debug.Print ""
        Else
            Open filepath & "REPORTPROPSfor_" & rpt.Name & ".txt" For Output As #1
            Print #1, "RecordSource = " & Trim(rpt.RecordSource)
            Print #1, "Filter = " & Trim(rpt.Filter)
            ProcessFormOrReportMethods rpt.Properties
            Print #1, ""
        End If
        
        ProcessControls rpt.controls
        
        DoCmd.Close acReport, rpt.Name, acSaveNo
    
        If debuggin Then
        Else
            Close #1
        End If
    Next rptHolder
End Sub

Sub robListAllFormProps()
'https://docs.microsoft.com/en-us/office/vba/api/access.accontroltype
    Dim frm As Form
    Dim formIsLoaded As Boolean
    Dim outputThisProp As Boolean
    
    For Each frmholder In Application.CurrentProject.AllForms
        formIsLoaded = False
        For Each aLoadedForm In Application.Forms
            If aLoadedForm.Name = frmholder.Name Then
                formIsLoaded = True
            End If
        Next aLoadedForm
        
        If formIsLoaded = False Then
            DoCmd.OpenForm frmholder.Name, acDesign, , , acFormReadOnly, acHidden
        End If
        
        Set frm = Application.Forms(frmholder.Name)
        
        If debuggin Then
            Debug.Print frm.Name
            Debug.Print "RecordSource = " & Trim(frm.RecordSource)
            Debug.Print "Filter = " & Trim(frm.Filter)
            ProcessFormOrReportMethods frm.Properties
            Debug.Print ""
        Else
            Open filepath & "FORMPROPSfor_" & frm.Name & ".txt" For Output As #1
            Print #1, "RecordSource = " & Trim(frm.RecordSource)
            Print #1, "Filter = " & Trim(frm.Filter)
            ProcessFormOrReportMethods frm.Properties
            Print #1, ""
        End If

        ProcessControls frm.controls
        
        
        DoCmd.Close acForm, frm.Name, acSaveNo
    
        If debuggin Then
        Else
            Close #1
        End If
    Next frmholder
    
    

End Sub
Private Sub robListAllQuerySQL()
    For Each qryd In Application.CurrentDb.QueryDefs
        If Left(qryd.Name, 1) <> "~" Then
            Open filepath & "QUERY_" & qryd.Name & ".qry" For Output As #1
            Print #1, Trim(qryd.SQL)
            Close #1
        End If
    Next qryd
End Sub
Private Sub ProcessFormOrReportMethods(ctl As Properties)
    For Each prp In ctl
        outputThisProp = False
        If Left(prp.Name, 2) = "On" Then
                If Trim(prp.Value) <> "" Then
                    outputThisProp = True
                End If
            End If
            If (prp.Name = "BeforeUpdate" Or prp.Name = "AfterUpdate") Then
                If Trim(prp.Value) <> "" Then
                    outputThisProp = True
                End If
            End If
            If outputThisProp = True Then
                If debuggin Then
                    Debug.Print prp.Name & " " & Trim(prp.Value)
                Else
                    Print #1, prp.Name & " " & Trim(prp.Value)
                End If
            End If
        'End If
    Next prp
End Sub
Private Sub ProcessControls(controls As controls)
        For Each ctl In controls
            If ctl.ControlType <> acLabel And ctl.ControlType <> acRectangle And ctl.ControlType <> acPage And ctl.ControlType <> acLine _
                And ctl.ControlType <> acObjectFrame And ctl.ControlType <> acPageBreak And ctl.ControlType <> acTabCtl _
                And ctl.ControlType <> acCommandButton Then
                If debuggin Then
                    Debug.Print TypeName(ctl) & " - Name = " & ctl.Properties("Name")
                Else
                    Print #1, TypeName(ctl) & " - " & ctl.Properties("Name")
                End If
        
                For Each prp In ctl.Properties
                    outputThisProp = False
                    If prp.Name = "LabelName" Or prp.Name = "Text" Or prp.Name = "SelText" Or prp.Name = "SelStart" Or prp.Name = "SelLength" Or prp.Name = "ListCount" Or prp.Name = "ListIndex" Then
                    Else
                        If ctl.ControlType = acTextBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "DefaultValue" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acCheckBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "DefaultValue" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acListBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "ColumnCount" Or prp.Name = "RowSource" Or prp.Name = "RowSourceType" Or prp.Name = "BoundColumn" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acComboBox Then
                            If prp.Name = "ControlSource" Or prp.Name = "ColumnCount" Or prp.Name = "RowSource" Or prp.Name = "RowSourceType" Or prp.Name = "BoundColumn" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acOptionGroup Or ctl.ControlType = acOptionButton Then
                            If prp.Name = "ControlSource" Then
                                outputThisProp = True
                            End If
                        ElseIf ctl.ControlType = acSubform Or ctl.ControlType = acToggleButton Then
                            If prp.Name = "SourceObject" Or Left(prp.Name, 4) = "Link" Then
                                outputThisProp = True
                            End If
                        Else
                            If ctl.ControlType = acRectangle Or ctl.ControlType = acPage Or ctl.ControlType = acLine Or ctl.ControlType = acObjectFrame Or ctl.ControlType = acPageBreak Or ctl.ControlType = acTabCtl Then
                            Else
                                outputThisProp = True
                            End If
                        End If
                        If Left(prp.Name, 2) = "On" Then
                            If Trim(prp.Value) <> "" Then
                                outputThisProp = True
                            End If
                        End If
                        If (prp.Name = "BeforeUpdate" Or prp.Name = "AfterUpdate") Then
                            If Trim(prp.Value) <> "" Then
                                outputThisProp = True
                            End If
                        End If
                        If outputThisProp = True Then
                            If debuggin Then
                                Debug.Print vbTab & prp.Name & " " & Trim(prp.Value)
                            Else
                                Print #1, vbTab & prp.Name & " " & Trim(prp.Value)
                            End If
                        End If
                    End If
                Next prp
            End If
        Next ctl
 
End Sub



Public Sub ExportAllCode()

    Dim c As Variant
    Dim Sfx As String
    Dim filen As String

    For Each c In Application.VBE.VBProjects(1).VBComponents
        Select Case c.Type
            Case 2 'vbext_ct_ClassModule, vbext_ct_Document
                Sfx = ".cls"
            Case 100 'vbext_ct_MSForm
                Sfx = ".frm"
            Case 1 'vbext_ct_StdModule
                Sfx = ".bas"
            Case Else
                Sfx = ""
        End Select
        
        filen = c.Name
        If Sfx <> "" Then
            c.Export _
                FileName:=CurrentProject.Path & "\" & _
                filen & Sfx
        End If
    Next c

End Sub

Forcing dynamic content to render Client-Side on a Netlify Gridsome SSR Vue Site

I inherited a site build on Netlify using Gridsome and Vue Server-Side Rendering (SSR).  The site is pretty good.  It loads very fast and the content is easily maintained by people that are not programmers.  However, we occasionally want to embed a form from another site, such as OpenForms.com, and that is challenging.  The idea behind Vue SSR is that the server will render and load ALL the content then provide it to the browser, and trying to run JavaScript on the client is challenging because traditional events used by SPA and web page JavaScript programmers don’t fire.

Furthermore, the Gridsome CMS uses Markdown for web page content, but the ability to place HTML and JavaScript in these pages is limited.  I battled for days to create a solution and am sharing how I made it work.  I’ll admit it may not be the best solution; please let me know if there is a better way; but this solution works robustly.

Step 1: In the Gridsome MarkDown page, add the anchor tag that links to your form as provided by OpenForms.  Something like this:

<a class="openforms-embed" href="https://us.openforms.com/Form/{your form ID here}">Click here to view form.</a>

I did not include the script tag to load the JavaScript here because it won’t load reliably here, so I load it elsewhere.

Step 2: On the Vue page that renders the MarkDown page from Step 1, add code in the mounted() and updated() events. (The console.log is not necessary, but I use it to help me understand what is going on.)

export default {
   mounted() {
     console.log("mounted basic: " + window.location.pathname);
    evalScripts()
    
  },
  updated() {
    console.log("updated basic: " + window.location.pathname);
    evalScripts()
  },

Step 3: The important piece is calling the evalScripts() method I wrote which is this:

function evalScripts() {
  //This SeamlessOpenForms is specific to USOpenForms to get an openform to render every time 
  //the page is refreshed or viewed.
  if (typeof(SeamlessOpenForms) != 'undefined')
  {
      SeamlessOpenForms.loadOpenForms();
  }
  else {
      const openforms = document.querySelectorAll(".openforms-embed");
      if (openforms.length>0)
      {
        console.log("missing SeamlessForms: " + window.location.pathname);
        const scriptPromise = new Promise((resolve, reject) => {
          var scriptElement = document.createElement('script');  
          document.body.appendChild(scriptElement);
          scriptElement.src = 'https://us.openforms.com/Scripts/embed-iframe.js';  
          scriptElement.onload = resolve;
          scriptElement.async = true;
        });
        scriptPromise.then(() => { SeamlessOpenForms.loadOpenForms();});
    }
  };
}

I will attempt to explain what I think is going on with USOpenForms and the code above.  First of all, this code is risky because I reviewed the JavaScript in https://us.openforms.com/Scripts/embed-iframe.js provided by OpenForms to figure out what to do here, and it is very possible that OpenForms will change their JavaScript and what I am doing here will no longer work (our fallback is to put this in an Iframe, but that causes two vertical scroll bars).

When the JavaScript file (embed-iframe.js) loads it executes and looks for any anchor tags in the DOM with a class of .openforms-embed.  If it finds an item with that tag, it uses the src property to pull in the form and render it within the current page.  However, if a user navigates first to another MarkDown page, based on the same Vue Page, the embed-iframe.js looks for those anchor tags on that MarkDown page and does not find them.  When the user navigates to the MarkDown page containing the anchor tag, the embed-iframe.js does not load and run to look for anchor tags because it already did so when the first MarkDown page for that Vue component loaded.

The script above, gets called by either the mounted() or updated() event, one of which will fire every time a MarkDown page is loaded or refreshed.  The script will render the OpenForm via SeamlessOpenForms.loadOpenForms() if the JavaScript to do so is already loaded, but if not it will dynamically load that JavaScript, then perform the render code (SeamlessOpenForms.loadOpenForms();)

FYI – Here is a simple example of loading the form in an IFrame, which is what we did initially in the MarkDown until I got the embedded form JavaScript to work.

<iframe height="600px" width="100%" style="border:none;" src="https://us.openforms.com/Form/{your form id}"></iframe>

The explanation:

In the Gridsome/Vue SSR architecture, some window/DOM events only fire when the first web page (the first MarkDown page) based on that Vue Page is loaded.  So if you have dozens of MarkDown files that all use the same Vue Page (such as BasicPage.Vue), some javascript methods and Vue events only run when the first MarkDown page based on the Vue Page is loaded.  But the mounted() or updated() events always fire when a MarkDown page is loaded, rendered, or refreshed.

Type Mismatch Error in VBA for Microsoft Access Parameters Collection

I ran into a strange problem today with an project that I took over where code in Microsoft Access VBA is being converted. The original database, with untouched source code, has the following lines of code to cycle over all of the parameters in a querydef. The code below has been working for 15 years:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next

However, our current Access database project (*.mdb) that has the exact same code, but we have modified “something” now gets a “Type Mismatch” error number 13 when we try to “For Each” over the results of qdf.Parameters.

If I open a watch on qdf.Parameters I can view the parameters without a problem. There is only one parameter and I even confirmed that all properties of the parameter are identical when they run in the old version of our Access database app.

I could not figure out why the code no longer works. Even the query that runs is identical. However, I was able to fix the code to work again for all cases by replacing the For Each block with the following:

Dim i As Integer
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i).Value = Eval(qdf.Parameters(i).Name)
Next i