Category Archives: MS Access Reports

MS Access Reports

MS Access – Missing Images, Images not showing

UPDATE

There’s finally an official update from Microsoft to fix the problem, see: http://www.devhut.net/2015/12/08/finally-a-fix-for-the-images-not-showing-in-forms-and-report-for-access-2016/ for all the details and links.


There has been a MAJOR screw up and sadly we are seeing countless posts/questions regarding images not showing up in forms/reports.  Sadly, even several months after being flagged, we are still waiting upon an official fix from Microsoft!  🙁

Proposed Solution 1

One proposed solution is to set your database options (File -> Options -> Current Database)

12-5-2015 6-57-31 AM

and then reinsert all your images, but this will still not help if you are working with bmp images, so you’d need to ensure they are of another image format first.

Proposed Solution 2

Another fix has been to simply change the image’s Size Mode to Stretch or Clip and changing the Picture Type to Linked.  In many cases, it was as simple as that.

12-5-2015 7-03-34 AM

Below is the beginning of code to loop through all the forms and reports within a database and switch the properties in question.  The issue being that one way or another, you will still need to manually intervene since by switching them from Embedded to Linked, you will need to supply a path/filename.

'---------------------------------------------------------------------------------------
' Procedure : FixImages
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Try to start fixing MS' mess with images
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-30              Initial Release
'---------------------------------------------------------------------------------------
Sub FixImages()
    On Error GoTo Error_Handler
    Dim DbO                   As AccessObject
    Dim DbP                   As Object
    Dim frm                   As Access.Form
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control

    Set DbP = Application.CurrentProject

    'Review/Fix Forms
    Debug.Print "Processing Forms"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllForms
        Debug.Print vbTab & DbO.Name
        DoCmd.OpenForm DbO.Name, acDesign    'Open the form in design view so we can work with it
        Set frm = Forms(DbO.Name)
        With frm
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = acImage Then    'If an image control is found then apply our settings
                    Debug.Print vbTab & vbTab & ctl.Name
                    ctl.SizeMode = acOLESizeClip    'could also be acOLESizeStretch - https://msdn.microsoft.com/en-us/library/office/ff837281(v=office.15).aspx
                    ctl.PictureType = 1    '0 would be embedded - https://msdn.microsoft.com/en-us/library/office/ff197027(v=office.15).aspx
                End If
            Next
        End With
        DoCmd.Close acForm, DbO.Name, acSaveYes    'Close and save our changes
    Next DbO

    'Review/Fix Reports
    Debug.Print "Processing Reports"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllReports
        Debug.Print vbTab & DbO.Name
        DoCmd.OpenReport DbO.Name, acDesign    'Open the report in design view so we can work with it
        Set rpt = Reports(DbO.Name)
        With rpt
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = acImage Then    'If an image control is found then apply our settings
                    Debug.Print vbTab & vbTab & ctl.Name
                    ctl.SizeMode = acOLESizeClip    'could also be acOLESizeStretch - https://msdn.microsoft.com/en-us/library/office/ff837281(v=office.15).aspx
                    ctl.PictureType = 1    '0 would be embedded - https://msdn.microsoft.com/en-us/library/office/ff197027(v=office.15).aspx
                End If
            Next
        End With
        DoCmd.Close acReport, DbO.Name, acSaveYes    'Close and save our changes
    Next DbO
    
    Debug.Print "Operation Complete!"

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Set rpt = Nothing
    Set DbO = Nothing
    Set DbP = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FixImages" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

What might also be useful is a simple procedure to quickly identify the problematic forms and reports. Below is such a procedure, it will loop through all the forms and reports looking for the specified control type; in our case acImage.

'---------------------------------------------------------------------------------------
' Procedure : IdentifyImageObjects
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Identify Forms/Reports with the specified control type
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage     : Call IdentifyImageObjects(acImage)
'           : Call IdentifyImageObjects(acLabel)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-30              Initial Release
'---------------------------------------------------------------------------------------
Sub IdentifyImageObjects(sCtlType As AcControlType, Optional bLeaveOpen As Boolean = True)
    On Error GoTo Error_Handler
    Dim DbO                   As AccessObject
    Dim DbP                   As Object
    Dim frm                   As Access.Form
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control
    Dim bCtlTypeFound         As Boolean

    Set DbP = Application.CurrentProject

    'Review/Fix Forms
    Debug.Print "Processing Forms"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllForms
        bCtlTypeFound = False
        DoCmd.OpenForm DbO.Name, acDesign    'Open the form in design view so we can work with it
        Set frm = Forms(DbO.Name)
        With frm
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = sCtlType Then    'If an image control is found then apply our settings
                    bCtlTypeFound = True
                    Exit For    'Stop the loop once we find 1 instance of the Control Type we are searching for
                End If
            Next
        End With
        If bCtlTypeFound = True Then
            Debug.Print vbTab & DbO.Name
            If bLeaveOpen = False Then DoCmd.Close acForm, DbO.Name, acSaveNo
        Else
            DoCmd.Close acForm, DbO.Name, acSaveNo
        End If
    Next DbO

    'Review/Fix Reports
    Debug.Print "Processing Reports"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllReports
        bCtlTypeFound = False
        DoCmd.OpenReport DbO.Name, acDesign    'Open the report in design view so we can work with it
        Set rpt = Reports(DbO.Name)
        With rpt
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = sCtlType Then    'If an image control is found then apply our settings
                    bCtlTypeFound = True
                    Exit For    'Stop the loop once we find 1 instance of the Control Type we are searching for
                End If
            Next
        End With
        If bCtlTypeFound = True Then
            Debug.Print vbTab & DbO.Name
            If bLeaveOpen = False Then DoCmd.Close acReport, DbO.Name, acSaveNo
        Else
            DoCmd.Close acReport, DbO.Name, acSaveNo
        End If
    Next DbO

    Debug.Print "Operation Complete!"

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Set rpt = Nothing
    Set DbO = Nothing
    Set DbP = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: IdentifyImageObjects" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Proposed Solution 3

I also found an interesting statement (taken from: http://answers.microsoft.com/en-us/office/forum/office_365hp-access/embedded-images-disappeared-after-upgrade-to/2c84727f-89e8-48f8-9096-eb330379d4c6?page=1) in which user sLaeYa says:

If you export your Form:

  • Right Click on the Form in Navigation Pane
  • Select Export to XML File
  • Choose Destination
  • Select Data and Presentation

In the destination folder there is an images folder, you will find that the names of your main embedded image has changed to “Form_Name.BMP”

If you change the file name to the new one it starts working again.

I cannot confirm or deny this claim as I haven’t had the time to test it out yet.  Feel free to leave a comment should you have any more relevant information on the subject.

Count of the objects in your MS Access Database

Have you ever needed to get a simple count of the number of :

  • Tables
  • Queries
  • Forms
  • Macros
  • Reports
  • Modules

I recently needed to get some summary data regarding a database I was working on.  The following single line VBA commands will get you the count(s) you are after.

'Number of Tables
CurrentDb.TableDefs.Count 'includes system tables
'Number of Queries
CurrentDb.QueryDefs.Count
'Number of Forms
Currentproject.AllForms.Count
'Number of Macros
Currentproject.AllMacros.Count
'Number of Reports
Currentproject.AllReports.Count
'Number of Modules
Currentproject.AllModules.Count 'does not include object modules

Easy as can be!

MS Access – VBA – Set the Report Ribbon Property Using VBA

With the replacement of the command bars with the ribbon in Access 2007, I have started to always use a standardized ribbon for all my reports.  Now, I typically used 2 template reports which I have configured my typical layout, colors, header & footer formatting, etc.  and of course defined the Ribbon Name.  But like everyone else, sometimes, I start a new report from scratch, modify an existing one and forget to add in the Ribbon Name property and thus the user would not have displayed the ribbon allowing the to print, export or merely close the report; forcing them to shutdown the database just to be able to exit/close the report.  So I needed a simple means to ensure all my reports included this ribbon.

I already have a DeployMe function that I run everytime prior to deploying a new version of any database I create to my client’s which sets certain properties, ensures things like auto compact is disabled, auto correct is disables, and much more, so I simply wanted to create a function the I could add to this routine.  Below is what I came up with.  A simple loop that open each report one by one, sets the RibbonName property and then closes and saves the change.  Short, sweet and simple.  Best, it work.

'---------------------------------------------------------------------------------------
' Procedure : SetReportRibbon
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the reports in the current database and assign the
'             specified ribbon as the report's ribbon
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call SetReportRibbon
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Dec-07                 Initial Release
'---------------------------------------------------------------------------------------
Function SetReportRibbon()
On Error GoTo Error_Handler
    'Ensure that all the reports are set to use the ReportUtilities ribbon
    Dim DbO             As AccessObject
    Dim DbP             As Object
    Dim Rpts            As String

    Set DbP = Application.CurrentProject

    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acViewDesign
        If Reports(DbO.Name).Report.RibbonName = "" Then
            Reports(DbO.Name).Report.RibbonName = "ReportUtilities"
        End If
        DoCmd.Close acReport, DbO.Name, acSaveYes
    Next DbO

Error_Handler_Exit:
    On Error Resume Next
    Set DbP = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: SetReportRibbon" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access – VBA – Export Database Objects to Another Database

I while back, I wanted to unsecure a database.  Instead of messing around with accounts….  I simply decided to export everything, all the database objects: tables, queries, forms, reports, macros, modules into a new unsecured database.  Now you can right-click on each object, one at a time, select export, browse to find the database, click ok, ok…   but this is simply a complete waste of time.

Don’t ask me why you can’t, using multiple selected objects, perform an export?!  this to me is the type of oversight made by MS’ development team, but this is another discussion altogether.

The good news is that we can easily accomplish a complete export using the power of VBA and a few very simple lines of code!

'---------------------------------------------------------------------------------------
' Procedure : ExpObj2ExtDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Export all the database object to another database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sExtDb    : Fully qualified path and filename of the database to export the objects
'             to.
'
' Usage:
' ~~~~~~
' ExpObj2ExtDb "c:\databases\dbtest.accdb"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-Sep-27                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub ExpObj2ExtDb(sExtDb As String)
    On Error GoTo Error_Handler
    Dim qdf             As QueryDef
    Dim tdf             As TableDef
    Dim obj             As AccessObject

    ' Forms.
    For Each obj In CurrentProject.AllForms
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acForm, obj.Name, obj.Name, False
    Next obj

    ' Macros.
    For Each obj In CurrentProject.AllMacros
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acMacro, obj.Name, obj.Name, False
    Next obj

    ' Modules.
    For Each obj In CurrentProject.AllModules
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acModule, obj.Name, obj.Name, False
    Next obj

    ' Queries.
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then    'Ignore/Skip system generated queries
            DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                                   acQuery, qdf.Name, qdf.Name, False
        End If
    Next qdf

    ' Reports.
    For Each obj In CurrentProject.AllReports
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acReport, obj.Name, obj.Name, False
    Next obj

    ' Tables.
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then    'Ignore/Skip system tables
            DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                                   acTable, tdf.Name, tdf.Name, False
        End If
    Next tdf

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set tdf = Nothing
    Set obj = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ExpObj2ExtDb" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Voilà, nothing to it (once you do it once).

MS Access – VBA – Rename attachment in SendObect Method

Everyone knows that the quickest and easiest method for sending an e-mail in MS Access is to use the SendObject method

DoCmd.SendObject acSendReport, "YourReportName", , "Someone@somewhere.com", _
                     , , "Subject", "E-mail Body"

This is all find an dandy, but there is one flaw, it uses the name of your MS Access report: “rpt_SalesForecast” or “rpt_SalesByRegion” access as the attachment name.  This simply is not acceptable for most purposes.  We want to have a name that reflects better what the report is about: “Sales forecast 2012”, “Average Sale for Montreal in July”.

I recently was in a discussion and theDBguy was kind enough to share a little trick to do exactly this without requiring a whole lot of extra coding and automation.  I thought I’d share it for everyone to benefit from. Below is the code in question:

    Dim sExistingReportName As String
    Dim sAttachmentName As String

    'Input variables
    sExistingReportName = "YourMSAccessReportName"    'Name of the Access report Object to send
    sAttachmentName = "AttachmentNameToBeUsedInTheEmail"    'Name to be used for the attachment in the e-mail

    'The code to make it happen
    DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden
    Reports(sExistingReportName).Caption = sAttachmentName    'by changing the report caption
                                                        'you effectively change the name
                                                        'used for the attachment in the
                                                        '.SendObject method
    DoCmd.SendObject acSendReport, sExistingReportName, , "Someone@somewhere.com", _
                     , , "Subject", "E-mail Body"
    DoCmd.Close acReport, sExistingReportName

Short, sweet and simple! Thank you theDBguy!

MS Access – VBA – Set Report to Use Default Printer

It is easy during development to inadvertently change the page setup setting of a report (or reports) to use a local printer. Once deployed your database will throw an error message nagging the user to switch from the one specified to his default printer. Why not avoid this issue altogether?!

I created a very simply procedure that simply go through the report collection and ensure that all the report are set to use the default printer. I then call this procedure (along with turn off SubDataSheets, deactivate AllowZeroLength property, etc.) in my deploy procedure I run before deploying any database to my users.

'---------------------------------------------------------------------------------------
' Procedure : RptPrntSetDef
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Ensure that all the report apge setups are set to use the Default Printer
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-05-23              Initial Release
'---------------------------------------------------------------------------------------
Sub RptPrntSetDef()
    On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim DbP              As Object
    Dim DbO              As AccessObject

    Set db = CurrentDb
    DoCmd.SetWarnings False
    Debug.Print "RptPrntSetDef Begin"
    Debug.Print "================================================================================"
    'Check Reports
    Set DbP = application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If Reports(DbO.Name).Report.UseDefaultPrinter = False Then
            Debug.Print "Editing Report '" & DbO.Name & "'"
            Reports(DbO.Name).Report.UseDefaultPrinter = True
            DoCmd.Close acReport, DbO.Name, acSaveYes
        Else
            DoCmd.Close acReport, DbO.Name, acSaveNo
        End If
    Next DbO
    Debug.Print "================================================================================"
    Debug.Print "RptPrntSetDef End"

Error_Handler_Exit:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set DbO = Nothing
    Set DbP = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: RptPrntSetDef" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

<

MS Access – VBA – Determine Where a Field Is Used

I recently had to make a minor change to a poorly designed, but large database and had to determine where certain fields were being used so I could go make the necessary changes. In this case, I had to review hundreds of MS Access objects, so a manual approach was just not acceptable. As such, I created a VBA to let the computer do the checking for me and report back. Below is the fruits of my labor.

'---------------------------------------------------------------------------------------
' Procedure : FindFieldUsedWhere
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Locate where a field is used within queries, forms and reports
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFieldName    : Field Name to search for in the various Access objects
'
' Usage:
' ~~~~~~
' FindFieldUsedWhere("Type A")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-08                 Initial Release
'---------------------------------------------------------------------------------------
Function FindFieldUsedWhere(sFieldName As String)
On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim DbO              As AccessObject
    Dim DbP              As Object

    Set db = CurrentDb
    Debug.Print "FindFieldUsedWhere Begin"
    Debug.Print "Searching for '" & sFieldName & "'"
    Debug.Print "================================================================================"

    'Check Queries
    For Each qdf In db.QueryDefs
        'qdf.Name    'The current query's name
        'qdf.SQL     'The current query's SQL statement
        sSQL = qdf.SQL
        If InStr(sSQL, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Query: " & qdf.Name
        End If
    Next

    'Check Forms
    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acDesign
        If InStr(Forms(frm.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Form: " & frm.Name
        End If
        'Loop throught the Form Controls
        For Each ctl In Forms(frm.Name).Form.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acForm, frm.Name, acSaveNo
    Next frm

    'Check Reports
    Set DbP = Application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If InStr(Reports(DbO.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Report: " & DbO.Name
        End If
        'Loop throught the Report Controls
        For Each ctl In Reports(DbO.Name).Report.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acReport, DbO.Name, acSaveNo
    Next DbO

    Debug.Print "================================================================================"
    Debug.Print "FindFieldUsedWhere End"

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set DbP = Nothing
    Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FindFieldUsedWhere" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occurred
    Resume Error_Handler_Exit
End Function

This is my first draft and I haven’t looked at optimizing my code. It was more about getting results than developing a procedure to be reused often. Hopefully it can help someone else in the same boat as I found myself! Simply execute the procedure and it will return a listing of what Access objects use the specified field in the immediate window of the VBE console.

MS Access – Report – Change a Report’s RecordSource

The following procedure can be used to change the RecordSource of a Report.

'---------------------------------------------------------------------------------------
' Procedure : RedefRptSQL
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Redefine an existing report's recordsource
'             Requires opening the Report in design mode to make the changes
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sRptName    ~ Name of the Report to redefine the Record Source
' sSQL        ~ Table name, Query name or SQL Statement to be used to refine the 
'               Record Source with
'
' Usage:
' ~~~~~~
' RedefRptSQL "Report1", "SELECT * FROM tbl_Contacts ORDER BY LastName;"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-07-13              Initial Release
' 2         2017-10-11              Cleanup of the Procedure header
'---------------------------------------------------------------------------------------
Function RedefRptSQL(sRptName As String, sSQL As String)
On Error GoTo Error_Handler
    Dim Rpt     As Report

    DoCmd.OpenReport sRptName, acViewDesign, , , acHidden 'Open in design view so we can
                                                          'make our changes
    Set Rpt = Application.Reports(sRptName)
    Rpt.RecordSource = sSQL                               'Change the RecordSource
    DoCmd.Close acReport, sRptName, acSaveYes             'Save our changes

Error_Handler_Exit:
    On Error Resume Next
    Set Rpt = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "     Error Number: " & Err.Number & vbCrLf & _
            "     Error Source: RedefRptSQL" & vbCrLf & _
            "     Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access Sample – Colors Converter

Microsoft Access Color Picker & Converter Utility

This straightforward Access database offers a user-friendly form that makes converting colors effortless. Whether you’re working with OLE Color values, RGB components, or HEX codes, this utility lets you seamlessly switch between all three formats.

MS Access Color Utility

As you can see by the above you can enter a OLE Color, RGB Color, HEX color value, move sliders (if you were trying to find a color) and they get converted back and forth between all the formats in real-time and you get a visual of the color in question at the top of the form.
 
Continue reading

MS Access Change -1/0 to Yes/No in a Report

If you ever made a report and bound a textbox to a Yes/No field you will have been surprised to see the output returned as 0s and 1s!

So how can we change it so they get returned as Yes/No values in the report?

I have seen people resort to IIF() statements to convert -1 to Yes and 0 to No, but you need not even do this!

The easiest, and I suppose proper way to handle this case, is to set the textbox’s Format property to Yes/no in the Format tab of the Properties window. Voila! You now have Yes/No value returned in your report.