Category Archives: MS Access Forms

MS Access Forms

Access – VBA – Convert ControlType to Name

If ever you created code to loop over controls to identify their type (.ControlType), if you’re like me, at times, you wanted to report the data in English, not as numbers.

It’s nice to know the ControlType is 104, but what the hell does that truly mean?!

So then you go looking through the Object Browser and eventually review the AcControlType Enum to find out that 104 = “Command Button”.

Now, while it is pleasant to do this manually every time, we need to automate this for our sanity.

Need not worry, we can simply create a simple function like:

Continue reading →

Access – VBA – Find Images In Forms and Reports

Experienced developers know that we always want to minimize the file size of a database so it loads faster. We always want to squeeze the most performance out of every element and also ensure we never near Microsoft Access’ 2GB file size limit.

To that end, whenever I am asked to look over a database, one aspect I always review is the use of images. Novice developers often insert images without first resizing them for their use within the database.

For instance, I’ve shrunk a db by over 250MB just by resizing 3 images! In that specific instance, I reduced the database by 1.2GB once I had properly optimized all the images within the database. So this can have huge implications.

Continue reading →

Microsoft Access – Get Object Created Or Modified Date/Time

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:

Select All
Select None
Select First
Select Last
Select a Specific Value

Continue reading →

Access Pivot Tables and Pivot Charts

Surprised to see someone talking about using Pivot Tables and Pivot Charts, aren’t you!

For those that aren’t already aware, Microsoft did away with these, too much protest I must say, with the release of Access 2013.

To Much Protest ....

When I mention “to mush protest” I was referring to the deluge (by Access standards) on Uservoice asking to have the feature reinstated. There were numerous suggestions all asking for the same thing and 2 of which were the 2nd and 5th most supported requests, yet both were ‘No Current Plan’. You can view all the details for yourself by perusing:

access.uservoice.com 2020 review

I was recently in a discussion in which I stated that I don’t use or believe in uservoice.com as a whole and was told that I make broad statements without ever backing them up. As some of you know, I have already done a couple reviews of access.uservoice.com in years past: access.uservoice.com A Year In…

Continue reading →

Over the years I’ve seen numerous questions relating to how we could get them back…. So that got me thinking and I came up with a couple possible solutions:

use the WebBrowser control to replicate the features
automate Excel and create them there instead

In this article, I will concentrate on the latter and perhaps in the future (if there is some interest) I will explore the automation of Excel option.
Continue reading →

Access – Bar Code Generator

After my last article on QR Codes:

Access - QR Code Generator

I saw a question in a forum which made me want to play around with QR Codes in Access. I’ve seen QR Code ActiveX controls for sale, solutions that use Excel and copy the generated code from there, … but I wanted something that could simply be integrated easily within Access (or any other application…

Continue reading →

I wanted to explore QR Code’s sibling, the Bar Code.

Now, for some bar codes you can install special fonts and simply use that font to display the bar code. That said, like with the QR Code generator I set out to see what I could do using the WebBrowser control so that I didn’t need to install anything, didn’t need to pay anything and didn’t require any special dependencies to work.

Google eventually led me to really impressive bar code javascript (js) library:

GitHub - lindell/JsBarcode: Barcode generation library written in JavaScript that works in both the browser and on Node.js

Barcode generation library written in JavaScript that works in both the browser and on Node.js - GitHub - lindell/JsBarcode: Barcode generation library written in JavaScript that works in both the ...

This js library already allows for all the following Bar Code types:

CODE128
- CODE128 (automatic mode switching)
- CODE128 A/B/C (force mode)
EAN
- EAN-13
- EAN-8
- EAN-5
- EAN-2
- UPC (A)
- UPC (E)
CODE39
ITF
- ITF
- ITF-14
MSI
- MSI10
- MSI11
- MSI1010
- MSI1110
Pharmacode
Codabar

and, much, much more. Review the GitHub repo to learn more about all of its capabilities.

Thus, with this in hand and my QR Code Generator as a template I was all set!

Continue reading →

Access – The Mysterious Case Of The Listbox Order Exception

Just a quick note.

I was working on a little sample I wanted to post here and thought I was loosing it when I couldn’t get a textbox to go in front of a listbox. I tried using the ‘Bring to front’ & ‘Send to back’ commands numerous time, nothing worked. I was as perplexed as our turkey friend here!

I thought it had to be me doing something blatantly wrong so I reached out to the Access Dev Team only to have them confirm the behavior. They informed me that it wasn’t possible.

Say What?! Is someone pulling my leg?

I can successfully place a textbox on top of a textbox, combobox, checkbox, …,command button, web browser, …, but not a listbox? Seriously.

Continue reading →

Access – QR Code Generator

I saw a question in a forum which made me want to play around with QR Codes in Access.

I’ve seen QR Code ActiveX controls for sale, solutions that use Excel and copy the generated code from there, … but I wanted something that could simply be integrated easily within Access (or any other application for that matter) that didn’t involve installing things, paying money, interacting with outside applications… So a simple standalone solution.

How hard could it be?!

I knew that there existed a few javascript (js) QR Code libraries, no point reinventing the wheel! so I did a quick Google search and found a simple js library that had no dependencies (bonus!):

GitHub - davidshimjs/qrcodejs: Cross-browser QRCode generator for javascript

Cross-browser QRCode generator for javascript. Contribute to davidshimjs/qrcodejs development by creating an account on GitHub.

with this in hand, it was just a question of using the knowledge found on my WebBrowser control article:

Everything You Never Wanted to Know About the Access WebBrowser Control

Continue reading →

to put it all together in a WebBrowser control within a form.

Continue reading →

Change Attachment Dialog Default Directory

I thought I’d share the solution I provided to a member in a recent UtterAccess thread:

When adding files to Attachment field can I open a dialog box in a predefined location?

... I would like to have the dialog box, which opens when you click "Add" in Attachment, to open that particular vendor's folder to chose from and not to navigate to that folder from some other default location ...

Continue reading →

Microsoft Access Gauges

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 →