Category Archives: MS Access Reports

MS Access Reports

Microsoft Access – Get Object Created Or Modified Date/Time

Microsoft Access - Object - Created and Modified Dates

Getting the Creation Date/Time

So have you ever tried to retrieve the Created Date/Time. Pretty easy right as there are a couple ways to attack this!

You can simply query the MSysObjects table:

SELECT MSysObjects.DateCreate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or perhaps use DAO Containers and Documents:

CurrentDb.Containers("Forms").Documents("Form1").Properties("DateCreated").Value

 

Getting the Last Modified Date/Time

But what about the Modified Date/Time?

So you would think that we could simply modify the above query of the MSysObjects table to:

SELECT MSysObjects.DateUpdate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or the DAO Containers and Documents to:

CurrentDb.Containers("Forms").Documents("Form1").Properties("LastUpdated").Value

But those are not reliable and don’t always work properly! (especially for Forms and Reports). Instead, they seem to return the Created Date/Time again.
 
Continue reading →

Microsoft Access – Quick Tips – The List Box Control

In this post, which will evolve with time, I hope to slowly build up a series of useful tidbits of information and code samples relating to working with List boxes. This will hopefully become something similar to what I did when I created my article on the Web Browser control:

Everything You Never Wanted to Know About the Access WebBrowser Control
The Access WebBrowser control is one of the greatest and most frustrating controls available to any Access developer! The beauty of this control is it enables you to gain the power of the web within your database. On the other hand, I have found it very frustrating to program as many properties/methods available to the…

Continue reading →

which, surprisingly enough to me, has turned out to be one of my most popular posts.

In the this article, the major highlights include:

Continue reading →

Access – Bug – DoCmd.OutputTo acFormatXLSX Doesn’t Work

Software Bug

Part of a recent discussion in UtterAccess:

Export Report out to Excel
I have upgraded a client form Access 2003 to Access 2016 but when I go to export the report to excel I am getting the attached error, it looks like it is no longer available

and after some testing, I can confirm that exporting a report as acFormatXLSX fails with the error:

Access - OutputTo Report acFormatXLSX - Error 2282You can successfully use DoCmd.OutputTo to export:

  • tables
  • queries
  • forms

as acFormatXLSX, but not reports!

Continue reading →

Access – Bug – Report Borders Not Showing

Software Bug

Yep, one more to add to the long list Microsoft Access bugs!

The Problem

As reported:

Access 365 v2206 (15330.20196) report formatting regression
Updated this morning, ZERO notes about Access updates so I figured I was safe: Went to generate a report... group header usually looks something like: Now looks something like: Confirmed a rollback to 2205 (15225.20288) fixes it. I’m not doing anything weird or zany with the border...

 

We seem to have a new bug that has surfaced in version 2206 (build 15330.20196) in which reports don’t seem to be properly rendering.

Fixed

Microsoft has supposedly fixed the issue by flipping flags at their end.  So you need not do anything beyond close and restart Access.

Yes, it’s like magic.  Microsoft can remotely alter your installation without your knowledge or consent.

Continue reading →

Microsoft Access Gauges

Microsoft Access Gauge

In my continuing push to improve what can be done in Access, I decided to see how I could create gauges in Access to show progress, statuses, percentages, … in a visual manner.

I won’t bore you with all the details, but the end result was to leverage the power that the WebBrowser control has to offer and use one of many JavaScript libraries.

In my demo file, I offer 2 ways to approach its usage:

    • create a template index.html file with all the setting pre-established and simply automate pushing the value to display
    • use a class to gain full control of the gauge in real-time

So the choice is yours!

Continue reading →

Access – Determine If a Control Exist Or Not

It is a bit of a weird need, but there are certain situations that can require one to need to determine whether or not a specific control exists within a form or report.

I was recently working on automating a modularized form setup and needed to exactly that. As such, I created a simple function to perform such a check and thought I’d share it.

I started creating 2 different functions, one for forms and one for reports, but eventually and revised my code to the following function which works for both.

Continue reading →

GUI Design Basics

I’m starting this post, which will grow in time, because of feedback through a recent blog poll.

What are some basic design principles that should be remembered when designing an application, any application?

Most design principles are universal and not Access specific.  They can apply just as much for Access Forms & Reports, as Excel Userforms, PowerPoint presentations or … Web Application. Here are a few of my basic recommendations.

I’ve split this discussion up into the following sections:

Continue reading →

Access – Report to Fit Page

Size To Fit Report

One major aspect that trips up many novice developers is managing to fit the content within a letter page. As such, we see questions relating to scaling or sizing to fit a page. Sadly, this is one function that Access does not possess.

The obvious solution is to setup the report page to the proper page size and then resize the content to fit those boundaries (minus the margins).

That said, there is a simple little trick that you can employ to get around this limitation should you be in a situation that you can’t redesign a report or need to enlarge or shrink a report for some reason.

  1. Generate the report in whatever page size that it is design for
  2. Export the report as a PDF (External Data -> Export -> PDF or XPS)
  3. Open the generated PDF and then print it, selecting Fit to page as the Scale

This way you can print any size Access report on any other size page.

MS Access – Improved Charting

Here’s a demo I’ve been wanting to share for years and finally got around to it!

Charting has always been one of Access’ greatest weaknesses in my opinion. When compared to the ease of charting in Excel, Access has been lagging behind for years/decades even. I find the dialogs confusing, and understanding how data series relate to my selected table/fields not to be obvious. Then add to that the fact that Microsoft added new charting capabilities, but not all versions have it… it just a nightmare to manage (open the new charts in older versions and get a blank area!).

Continue reading →