Tag Archives: MS Access Forms

Mutually Exclusive Check Boxes in a Microsoft Access Continuous Form

A very common UI requests I hear from Microsoft Access developers goes something like this:

I have a continuous form with a check box, and only one record should be allowed to have it checked at a time.

Think :

  • Primary Phone
  • Default Address
  • Main Contact
  • Preferred Email
  • etc.

In a perfect world, we’d just use option buttons. But continuous forms don’t work that way where each record is rendered independently, so Access gives us many check boxes, not one logical group.

So how do we enforce mutual exclusivity?

Let’s walk through this step by step, starting with a straightforward After Update solution and progressively refining it into something cleaner, reusable, and faster. You pick the approach that suits your needs.
 
Continue reading →

MS Access VBA – Not In List Event

The On Not in List event in Microsoft Access is triggered when a user types a value into a combo box that doesn’t currently exist in its list of available options if the Limit To List property (on the Data tab of the Property Sheet) is set to Yes. This event allows you to manage how the application responds, most commonly by offering the user a way to add the new value directly to the combo box’s data source which is most often a table.

For instance, imagine you have a combo box that lists available product categories from a Categories table. When a user enters a new category say, “Eco-Friendly” that isn’t already in the list, the Not In List event can prompt the user to confirm whether they’d like to add it. If they agree, your VBA code can insert the new category into the Categories table and then requery the combo box so that the new option appears immediately.
This approach helps maintain consistent data while giving users flexibility to expand predefined lists dynamically, improving workflow and usability.
 
Continue reading →

How To Add a Custom Icon To ‘Pop Up’ Form and ‘Overlapping Windows’ Forms

Designing a professional Microsoft Access application often comes down to the small details like the icons that appear on your pop-up forms and overlapping window forms. By default, Access displays a generic icon, which can make even a well designed database feel unfinished.

Adding custom form icons in Microsoft Access not only enhances the visual appeal of your application, but also improves usability by helping users quickly identify open forms in the Windows taskbar or Alt-Tab view. For client-facing databases, custom icons also reinforce branding and professionalism, especially when distributing Access front ends.

In an ideal world, Microsoft Access would expose a simple, built-in property that allows developers to specify a custom icon for each form. Unfortunately, no such property exists. Despite Access offering extensive control over form appearance and behavior, the form icon remains hard-coded to the default Access icon, leaving developers with no native way to change it. As a result, implementing custom form icons requires working around this limitation by leveraging Windows APIs and graphics libraries approaches that are powerful, but far from obvious. Fortunately, with a bit of Windows API magic, we can still achieve exactly the result we want.

In this article, you’ll learn multiple techniques for setting custom icons on Access forms, including:

  • Using native Windows APIs with .ico files
  • Supporting PNG, JPG, and other image formats with GDI+
  • Loading icons dynamically from table attachments

All examples work with Popup forms and databases using Overlapping Windows (not Tabbed Documents), and are compatible with both 32-bit and 64-bit Access.
 
Continue reading →

Goto A New Record Having It Show At The Bottom Of A Continuous Form

Trying to help out in a forum question

Going to Last Record Only Shows One Record Needing to Scroll Down
I have a continuous form with 50 records.

When it opens, I want the new record to be at the bottom of the screen.

in which James had an interesting issue he wanted to address.

He had a continuous form and wanted to go to a new record, but wanted to still display records in the form rather than the default where the new entry is at the top of the form and you can’t see any of the previous records.

Now I know this is somewhat of a niche, uncommon need, but I still thought it was worth taking a moment to explore together and see how it can be resolved.
 
Continue reading →

Adding Spell Check To Your Microsoft Access Forms

Spell checking is a valuable feature to incorporate in Microsoft Access forms, ensuring data accuracy and professionalism. Access provides built-in spell checking capabilities that can be triggered both by keyboard shortcuts and programmatically through VBA. Here’s how you can implement and optimize spell checking in your Access forms
 
Continue reading →

Mastering Microsoft Access’ DLookUp Function

Outside of queries, DLookup is another built-in versatile and essential function in Microsoft Access that allows users to retrieve specific field values from tables or queries based on a defined criteria. This function is particularly useful when you need to access data that isn’t directly available in your current form, report, or query.
 

Syntax and Structure

The basic syntax of the DLookup function is as follows:

DLookup(expr, domain, [criteria])
  • expr
    The field or expression you want to retrieve.
  • domain
    The table or query name containing the data.
  • criteria
    Optional. The criteria to filter the records with.

 
Continue reading →

Utilizing In-Memory Virtual RecordSets in Microsoft Access

Some time ago, I wrote an article about creating Virtual Queries, which you can find here:

Access - Virtual Query
It doesn’t happen too often, but in certain scenario it may be necessary to build a query based on static values. I don’t know if there is a technical term that already exist, but I’m going with the term ‘Virtual Query’, ‘Static Value Query’ or ‘Hardcoded Value Query’. Say you perform a series of data manipulation/extraction and wish to push these static values out as a query for use in other functions. So how can this be done? A Concrete example: For use in the SQL Code below I am trying to build a query around the following data Well,…

Continue reading →

Today, I’d like to delve into the topic of Virtual Recordsets, commonly known as In-Memory Recordsets.

While working on a personal project, I needed to display a list of files without repeatedly importing this data into Access. Constantly importing would lead to unnecessary data duplication and bloat in the database. Instead, I realized that using a Virtual Recordset would be an ideal solution. This approach allows me to manage and manipulate the list of files dynamically without cluttering my Access database, ensuring that my application remains efficient and responsive.
 
Continue reading →