Category Archives: MS Access Forms

MS Access Forms

Pagination on an Access Form

What’s a developer sitting in front of his keyboard going to do with a few free hours? That’s right, have some fun and try and replicate Web Page pagination in his Access form.

Access Pagination Random PageMicrosoft Access Pagination
Access Pagination Last Page
Access Pagination First Page

In the demo, I have it displaying 10 records per page (as shown in the images above), but you can change that by simply editing the VBA line

Private Const lRecPerPage As Long = 10

and changing the 10 to whatever number of records you wish to display at a time.

There are no ActiveX controls, no APIs, … so this will work in both 32 and 64-bit versions of Access.

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 →

Access – AutoSize SubForm with Main Form Detail Section

I was working on a Form with an embedded subform.

In the Form’s header were a series of controls to perform a search and the subform would display the results to the user.  The issue was that I would setup the design, maximizing the subform within the main form’s detail section, at least within my screen resolution, but when distributed to my users it would present blank area because they had higher resolution/DPI.

Thus, I set out to resize the subform automatically based on the users screen.
 
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 – Color Picker

HTML Color Picker
In my first part of this exploration of how to enable users to make color selections within a database, I demonstrated how we can use the built-in ChooseColor API dialog. This time round, I wanted to explore using an HTML color picker. Why, you might ask, well, I wanted to see what could be used that didn’t require APIs. Furthermore, I have seen many very good JavaScript color picker and simply wanted to have some fun!

Beside not needing to mess around with APIs, conditional compilation directives, … another advantage of this approach is the simple fact that since this is built upon plain HTML, JavaScript & CSS, we can make any changes we wish to the appearance of the dialog, unlike when using the built-in Windows ChooseColor API dialog approach.
 
Continue reading →

Access – Auto-Closing Forms

In a forum, the question was

Is there a way to automatically close a form once the cursor was moved off of it.

I don’t know why, but this was one of those forum questions that truly got me curious and so I needed to find a way to do this for my own personal knowledge.

I came up with an initial solution, but it wasn’t foolproof and wouldn’t necessarily work for all setups and so I slept on it and woke up with a fundamentally simple solution. I have to admit, the solution only came to me because of some recent work I had to do for a client which required me to override the system cursor (in an Outlook project) and so I had become aware of certain available cursor APIs. Armed with this basic knowledge, I figure there had to be a way to compare the cursor’s position against the form’s boundaries and so the solution was born! With 2 simple APIs and the form’s timer event, it became pretty straightforward to accomplish and can be ported to pretty much any setup.

The following demo has everything you need (both 32 and 64-bit API declarations) with a working example so you can dissect the concept to be in a position to integrate it into your own project.

Continue reading →

MS Access – Improved HTML

Here’s yet another long standing demo I’ve been wanting to provide the community for a couple years now.

Origins

Several years ago I developed an e-mailing database.  I originally started out using the Rich Text Format (RTF) provided by Access, but anyone that gets into anything remotely advanced will quickly find out the Microsoft’s implementation falls very short of what is considered proper HTML or nor do RTF fields provide all the bells an whistles you’d expect in any modern tool.

For instance, with RTF, you can’t do simple things like:

  • Justify text
  • Insert links
  • Insert tables
  • Insert images/media
  • Format text
    • Strikethrough
    • Superscript
    • subscript
  • Use predefined formats (p, h1, h2, h3, …)
  • and the list goes on, and on, and …

Then add to that Microsoft doesn’t provide a way to view the raw source code behind the RTF fields and you need to resort to coding to access such information.

Continue reading →

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 →