Trying to help out in a forum where the user was looking for an automated way to make all the labels, within a form, Proper Case.
Below was the solution
Category Archives: MS Access Forms
Access – Let’s Talk Colors
Just a quick post to discuss colors in Access.
Colors can be an asset to designers, if used correctly, to greatly enhance your application(s), no doubt!
But how can we define colors is the question I thought I’d quickly touch upon today.
All too often I see in code people defining thing along the lines of
'Set the font color of a control
Me.ControlName.ForeColor = vbRed
where developer use one of the predefined ColorConstants, which include:
- vbBlack
- vbBlue
- vbCyan
- vbGreen
- vbMagenta
- vbRed
- vbWhite
- vbYellow
There are 2 major issues with the above (excluding the use of vbBlack and vbWhite): the choices are very limited and the colors are very aggressive (not colors I’d recommend to use as they are hard to look at, so not a good user experience).
Access – Password Protect a Form or a Report
Here’s another common question that we see in Access forums:
How can I password protect a form/report?
As per with most thing Access related, there are numerous ways to handle this and in the post and sample database I cover 3:
- Hardcoded password
- Table based password (enable the admin to change it without needing to edit a form)
- Authorized User table (recognize the user without needing any password)
Access – Textbox Hint Text
I was trying to help out in a recent forum discussion regarding wanting to add “hints” to controls like you can do in HTML forms.
While Access provide a few means to add certain information, things like: Format, Status Bar text and Control Tips. Sadly, these are often simply unreliable, illegible or simply overlooked by users. If you wish to learn more about these approached then look over my Access – Providing Control Hints and Suggestions article.
Access – VBA – Inserting New Records
I thought I’d cover the subject of creating/adding/inserting new records into a table using VBA.
As per pretty much anything with Access, there are a number of way to do this and I thought I’d cover three in this post
Access Attachment Gallery
This is yet another proof of concept that I developed while trying to help someone in a forum. The simple idea here was to be able to display multiple attachments in one view rather than needing to go through them, one by one.
In the example below, we have a main ‘Product’ form with a ‘Product Images’ subform which will display the various image associated to the currently shown product.
What I’m Working On
The above proof of concept is very simple and satisfied the person’s need, but I wanted to take thing further and make it a complete attachment management tools. So I’ve been working, and am currently testing, the next version which will include
- Having no limit on the number of attachment that are displayed
- Ability to navigate – scroll through the attachments
- Ability to save the attachments to a location of your choosing
- Ability to Add new attachments to the current record
- Ability to Delete the selected record
- Selected attachment gets highlighted
- etc…
Here’s what it will look like
MS Access – Continuous Form Data Entry Approaches
Anyone that has created a continuous form is well aware of the new entry blank row appearing at the bottom of the form.
Although this is functional, let’s face it, it is less than ideal! Continuously having to scroll down to the bottom, or even having the new row hidden completely out of sight does not enhance the user experience. Furthermore, if you apply default values to your controls it can end up confusing your users as they will think they are actual rows of data rather than a row for performing data entry.
So I thought I’d create a very simple demo database to illustrate a couple alternative approaches to implement within a database to simplify your end-users’ lives and up your game as a developer.
Access Form Resizing Based on Screen Resolution
Here’s another question we see ever so often in the discussion forums.
As technology has advanced, screen resolution continues to grow making existing forms no longer acceptable to work with. So what can be done about it? How can we distribute a database so that it work on any PC with varying screen resolutions?
I first faced this issue myself about 10 years ago. After developing a complex database for one of my client’s and having it in production for about 2 years, they decided to include a new department in its usage, problem being they all used laptops with smaller screens. My database had been developed for one department and their screens and now ported onto this new department’s laptops, my forms no longer fit nicely within the monitor and users needed to continuously scroll. Not acceptable!
Access Calculated Control Blank/Empty/Null
Now the following is for a very niche market, but I thought I’d share none the less.
Access 2010 (although I seen reports in other versions such as Access 2013) had a major issue in which calculated control would not display their content. Yet, if you clicked on the control, it would then display the proper value. Now Microsoft eventually remedied the issue by issuing an update (I believe https://support.microsoft.com/en-us/help/2827138/access-encounters-multiple-issues-if-the-pc-has-been-running-more-than). So there is a real solution available, which is to update your installation.
However, I had a client, whose IT Dept. would not, will not, install the updates and I left me with no choice but to come up with a workaround of my own. As such, I created a routine that simply goes through a form and sets the focus on each control, one by one. It’s not ideal, but it works.
MS Access Security Demo Database
Security and Access, where to start? What to do?
Security can be simple and complicated, it all depends on your needs and abilities. It can be superficial, as it can be very granular. As such, I thought I’d present a couple options to help people get going in the right direction.
What does this demo illustrate?
In the demo, I demonstrate 3 different techniques:
The Static Password Approach
- How to password protect a single command button using a hard-code VBA password
The Dynamic Automatic Authentication
This use a set of security tables that you setup once and then the database can automatically authenticate and apply security as required. Although this example demonstrates restricting access to a command button, it can be used to control access to forms, reports, …