How to enable Rich Text for Text Boxes in Access Linked to SQL Server

Text Boxes on Forms and Reports in Microsoft Access allow you to configure the field to display “Rich Text” instead of “Plain Text” by setting the “Text Format” property of the control. This allows you to use HTML formatting in the field and display portions of the field using bold, italics, underline, and other enhancements.

However, when your data is coming from a SQL Server (or other DBMS), through linked tables, you can only choose to configure the control to support “Rich Text” when the field has a size of 256 bytes or greater.

Therefore, you need to go back to your source database (SQL Server), increase the size of the field so that it is 256 or larger, then go back to Access and Relink the table. After that, you should be able to choose Rich Text for the field on the form.

When the linked field length is too short, you will get this message from Microsoft Access “The setting you entered isn’t valid for this property.” when you try to change the control.

Git for Microsoft Access Databases

I wrote about this project previously (Track Changes to Access Form Properties in Source Control (Github) « Rob Kraft’s Software Development Blog), but am happy to share a more complete solution. If you would like to have Source Code Control such as Git or Subversion or TFS to track all the changes to a Microsoft Access database, the code in the github repo mentioned below has the code you need. This code is free for you to use in any respect (MIT license). Just copy the code into a module in your Microsoft Access database, run it, and it will export everything.

By everything, I mean it will export the following information from your Microsoft Access database into files:

• Code behind Forms
• Code behind Reports
• Code in Modules
• Code in Classes
• Properties of Forms
• Macros
• Properties of Reports
• Table Definitions
• Queries
• Database Option Values
• Database Property Values

You can get the latest version of this code https://github.com/RobKraft/AccessUtilities

Fix Unable to Resize Microsoft Access Navigation Bar

When you create a new Microsoft Access database from a template you may discover that you are unable to see very much of the Navigation Bar on the left. Perhaps this only happens when your monitor is a High DPI monitor such as mine. The fix is simple, simply close and re-open the Access database and the arrows to make the Navigation Bar wider should return as in this dem0: https://www.screencast.com/t/Ig5YErfhdkN8

Disable Right-Click Menus in Microsoft Access But Keep the Copy/Paste Menus

I hope someone comments on this article to tell me that there is a better solution. I was unable to find it. The application requirements were:

• Hide the right-click menus on the form and report title bars (shown below) to prevent users from entering “Design View”

• Keep the right-click menus with textbox controls (shown below) to allow users to use it for Copy and Paste.

Microsoft Access has an option in the Current Database tab of Options to disable all the right-click menus and that option is called “Allow Default Shortcut Menus”. However, when I unchecked that, I could not figure out a way to enable the right-click menus solely within textbox controls to allow Copy and Paste.

My solution is to keep this option (“Allow Default Shortcut Menus”) set to checked (true), then run some code as the users open the application in AutoExec to disable the right-click on the form and report headers. I found no documentation that clearly described how to disable the right-click on the form headers, but with much trial and error I found that if I disabled all CommandBars that did not have a name, I got the result I was looking for.

Private Sub DisableCommandMenus()
    For Each Item In CommandBars
        If Item.Enabled = True Then
            'The menus we want to disable are those that don't have names
            If Trim(Item.Name) = "" Then
                Item.Enabled = False
            End If
        End If
    Next
End Sub

The code above disables menus that don’t have names. It is possible that I will need to disable more of the CommandBars, but at the moment my solution appears to be complete.

Do you know of a better solution to this?

Programmatically Invoke Microsoft Access SpellChecker using VBA – acCmdSpelling

Microsoft Access has a built-in ability to run a spellcheck on your controls. It works best to call the VBA code to perform the spellcheck from the Exit method of the controls. I recommend calling DoCmd.SetWarnings to False prior to running the spellcheck because this will suppress “The spelling check is complete” message box from showing if there are not spelling errors in the control.

Private Sub Text5_Exit(Cancel As Integer)
    MySpellCheck Me!Text5
End Sub

Private Sub Text8_Exit(Cancel As Integer)
    MySpellCheck Me!Text8
End Sub

Private Sub MySpellCheck(ctl As Control)
    DoCmd.SetWarnings False
    With ctl
        .SetFocus
        If Len(.Value) > 0 Then
            .SelStart = 1
            .SelLength = Len(.Value)
            DoCmd.RunCommand acCmdSpelling
            .SelLength = 0
        End If
    End With
    DoCmd.SetWarnings True
End Sub

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

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

Tracking Microsoft Access Code Changes in GitHub; And Searches with Visual Studio

I will admit up front that this approach is not as awesome as the post title may make it sound, but I do find it to be very useful and fairly simple, just a little tedious.

As a consultant, I occasionally get the Access database project that needs some fixes. Of course we can often easily make copies of the .mdb or .accdb files regularly, but as a programmer I want more. I want to be able to see the history of my code changes over time so that I can identify where changes had unintended consequences. I do this by exporting all of the source code out of the Microsoft Access database into flat files, then importing the flat files into a git repo. I have a bit of code below that I use for this, which I found elsewhere on the Internet and modified a little.

I create a new module and place the code below in that module. Then I execute the subroutine. Following that I import or update the files in my git repo. The approach works well but the export does not include macros, and it does not include properties and events on forms and reports, nor table structures. Still, I have found it useful. I also use this approach to pull up all the files I exported in Microsoft Visual Studio which has search tools I like better than those built into the Access database.

I run the code below about once per day to track the latest changes I made, then apply the updates to the git repo.

Public Sub ExportAllCode()

    Dim c As Variant
    Dim Sfx 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

        If Sfx <> "" Then
            c.Export _
                FileName:=CurrentProject.Path & "\" & _
                c.Name & Sfx
        End If
    Next c

End Sub