Compile on Demand check on uncheck?


Recently, while trying to Debug some code a colleague of mine suggested I leave Compile on Demand1 unchecked.  Since in all my years of developing I have always left it checked, I asked why and here’s what he said got…

Many years ago, Michael Kaplan mentioned that this setting was known to cause problems, at a Pacific NW Access Developer’s Group meeting.

Here is how having this Option checked can bite you in the patooka’s!  Suppose you have unfinished code, or some other compile error, in code that you are not currently using. You forget to do a final Debug > Compile, to force all code to Compile. You deliver your app. to your customer. They immediately get a Run-Time error, with the same unused code. Why?  Because this setting does not “travel” with your database; it is a global VBA environment setting. With this Option unchecked, ALL code must be compilable before any code can be run. Checking this option (the stupid default setting), allows for partial compliable; in other words, only the Modules that have code that you are running must be compilable. You could still have that experimental new module with code that does not Compile. Personally, I’d rather find these issues myself, instead of having a customer find them.  <smile>

Additional comments here…  Say it aint’s so, Joe.

Now, with that being said, I am still leaving mine checked (and I am not alone), not because I like to skirt danger but because I Compile my code if I just look at it.  (Though nothing to do if you make no change, I check anyway.)  So, the choice is up to up but I would heed the advice if you are not *religious* about Compiling your code!

51 total views, no views today

Share Button

Sort by Descriptions is gone in Access 2007 and up but…

…you can see them, thanks to a post by JoelS_999 in the Microsoft Community Forums.  And, while this may not be exactly what you want, this neat little piece of code will allow you to see them in a table, tblSysObjProps.  This is especially helpful when upgrading from Access 2003 and below, where you have used Descriptions to store information pertinent to your Object. 

‘***Start of code
Public Function ListObjProps()
‘ Created by Joel S. on 12/05/2013

‘ This function lists MS Access 2010 objects (i.e. tables, queries, forms, reports,
‘ etc.) and their top-level properties as shown in the navigation pane. The output
‘ is written into a table, which allows an open, grid-like view of the object
‘ properties, similar to the navigation pane in Access 2000-2003.  Also, the output
‘ table makes it possible to sort on the Description field, which the Access 2010
‘ navigation pane does not allow.

‘ CAVEATS: The DateCreated and LastUpdated fields will reflect the values stored
‘ in the MSysObjects system table, not the values shown in the navigation pane
‘ (which sometimes appear to be incorrect).  Also, the Type field will show “Tables” not
‘ “Queries” for query objects, so this could cause confusion if some of the tables
‘ and queries in the database use exactly the same names.  Finally, this function should
‘ be run whenever the user deems he/she has added/deleted/modified enough
‘ MS Access objects to warrant refreshing the output table.

On Error GoTo Err_ListObjProps

Dim db As Database ‘Variable for current database
Dim ctnr As Container ‘High-level containers for MS Access objects
Dim obj As Document ‘Individual MS Access Objects–tables, queries, forms, etc.
Dim prop As DAO.Property ‘Object properties
Dim tdf As TableDef ‘Output table definition
Dim strTbl As String ‘Output table name
Dim rst As Recordset ‘Recordset for updating output table
Dim boolRstOpen As Boolean ‘Flag to indicate whether recordset is open

‘Turn off warnings
DoCmd.SetWarnings False
DoCmd.Hourglass True

boolRstOpen = False ‘Initiate flag to indicate recordset hasn’t been opened.
Set db = CurrentDb()
strTbl = “tblSysObjProps” ‘Name output table. (Can be renamed if user wishes.)

On Error Resume Next
‘Close and delete previous output table if needed
DoCmd.Close acTable, strTbl, acSaveNo
DoCmd.DeleteObject acTable, strTbl
On Error GoTo Err_ListObjProps

‘Create new output table definition
Set tdf = db.CreateTableDef(strTbl)
With tdf
  .Fields.Append .CreateField(“Name”, dbText)
  .Fields.Append .CreateField(“Type”, dbText)
  .Fields.Append .CreateField(“Description”, dbText)
  .Fields.Append .CreateField(“DateCreated”, dbDate)
  .Fields.Append .CreateField(“LastUpdated”, dbDate)
End With
‘Append new output table definition to database
db.TableDefs.Append tdf

‘Open output table for updating
Set rst = db.OpenRecordset(tdf.Name)
boolRstOpen = True
If (rst.RecordCount > 0) Then
End If

‘Cycle through all container types–tables, queries, forms, reports, etc.
For Each ctnr In db.Containers
  ‘Cycle through each object in the container–individual tables, queries, etc.
  For Each obj In ctnr.Documents
    ‘Create new record in output table
      ‘Print object name to immediate window if needed
      ‘Debug.Print obj.Name
      ‘Cycle through each top-level property for the object in question
      ‘and write the value for each specified property into output table.
      For Each prop In obj.Properties
        Select Case prop.Name
          Case “Container”
            rst!Type = prop.Value
          Case “DateCreated”
            rst!DateCreated = prop.Value
          Case “Description”
            rst!Description = prop.Value
          Case “LastUpdated”
            rst!LastUpdated = prop.Value
          Case “Name”
            rst!Name = prop.Value
        End Select
        ‘Print object properties to immediate window if needed
        ‘Debug.Print vbTab & prop.Name & ” = ” & prop.Value
      Next prop
      ‘Update output table for last record entered.
  Next obj
Next ctnr

‘Clean up and close function
If boolRstOpen Then
End If
‘MS Access quirk requires the hidden output table to be explicitly hidden then unhidden.
Application.SetHiddenAttribute acTable, strTbl, True
Application.SetHiddenAttribute acTable, strTbl, False
DoCmd.Hourglass False
DoCmd.SetWarnings True
MsgBox (“ListObjProps function completed successfully. See table ” _
  & strTbl & ” for output.”)
Exit Function

  ‘Clean up and close function
  On Error Resume Next
  If boolRstOpen Then
  End If
  DoCmd.Hourglass False
  DoCmd.SetWarnings True
  MsgBox (“ERROR: ListObjProps function did not complete successfully.”)
End Function
‘***End of code

Still missing the Database Window?  Here are some options…
AAD Consulting’s Database Window for Access 2007 and Access 2010
Avenius Gunter’s Database Window for Access 2007, Access 2010 and Access 2013
Access Junkie’s Database Window for Access 2007

169 total views, no views today

Share Button

Adding *continued…* to a Multicolumn Report…

After seeing a post in a Forum asking about how to add the word *continued…* to a Multicolumn Report I thought it would be fun to create a sample database showing off the feature, to download click here (Access 2007 or above needed and an unzipping program.  Images are not included!).  And, of course, like everything else, I could not just stop at just doing a simple Multicolumn Report… so, I added the ability to add pictures at the beginning of each Articles (or not), printing Articles or Publications, like Newsletters!  (To read more about that click here.)
Figure 1

Figure 1


The How…Continued

In this example I am using a 3 column report.  Fields needed are…


Controls needed on the report are…

txtTitle- Unbound  and in the aTitle Header section
txtTitleHidden – Bound to the field in the table that holds the Title, placed in the aTitle Header section.

Place both controls, txtTitle and txtTitleHidden in the aTitle Header section.  Set the aTitle Header section to Can Grow and Can Shrink properties to Yes and set the Repeat Section property to Yes.  Then in aTitle Header On_Format event place…

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

    Static strLastValue As String
    ‘Posted by here Pat Hartman (Access MVP)  11.19.2003
    ‘Modified by Gina Whipp 11.13.2013 to change color, size and position when Article is continued
    If Me.txtTitleHidden = strLastValue Then
        Me.txtTitle.Visible = True
        Me.txtTitle.Properties(“TopMargin”) = 0.05 * 1440
        Me.txtTitle = Me.txtTitleHidden & ” …continued”
        Me.txtTitle.ForeColor = RGB(140, 140, 140)  ‘Grey
        Me.txtTitle.FontSize = 10
        ‘Author is optional
        ‘Me.txtAuthor.Visible = False
        Me.txtTitle.Visible = True
        strLastValue = Me.txtTitleHidden
        Me.txtTitle = Me.txtTitleHidden
        Me.txtTitle.ForeColor = RGB(0, 0, 0)  ‘Black
        Me.txtTitle.FontSize = 11
       ‘Author is optional
       ’Me.txtAuthor.Visible = True
    End If

End Sub 

All done, you can run your report!

146 total views, 1 views today

Share Button

Where does she get those wonderful Toons?


Ron Leishman (c)

Every once in a while I get an eMail asking about the illustrations (clip art) used on my websites and my blogs.  Well, it’s only the best illustrations I have found, to date, on the internet…!  Digital Clipart by Ron Leishman.


90 total views, no views today

Share Button

Announcing Access…entials…


Yep, I started another blog, just couldn’t resist.  I wanted someplace to start *dumping* all my little tips, tricks and what nots about Microsoft Access that are just big enough for a web page.  And, a little something extra… you can Submit a Tip about Microsoft Access!

P.S. I finally found something to do with those Wordle Cloud Words!

354 total views, no views today

Share Button