As you migrate from version to version, working with Microsoft Access database forms becomes more intuitive and easier to design and implement using the pre-built templates, wizards and improving richer set of formatting tools.

Conditional formatting (once it was introduced) started life as a setting for up to three conditional formats which satisfied most requirements but now the with the later versions, it’s layered and can exceed this number for true flexibility and complex conditioning.

The point here is what level of setting conditional criteria for this format do you know?

In this simple example, mixing some Access VBA code into the mix and using just one condition, you get your Access forms (and reports) to flag by formatting values or records based on a true matched state and here is a way to highlight the current record.

Before I commence, this was an idea I found on another website from another but knowledgeable author so cannot take the full credit!

Microsoft Access Database Forms: Highlight Current Record For A Continuous Form

This method of course can be extended and utilised for a more realistic and richer interface than my example that follows but hopefully you will get the idea and more importantly, know the how.

This is a simple form based a table of ‘Customers’ in a continuous form layout showing basic contact information. I want to pick or choose an account from the drop-down box which will send the cursor and navigate to the matching record which all this is available in Access as standard but with custom addition of highlight the current record so it clearly stands out.

In the forms design view, add a Text box control to roughly the size or area you want to highlight so in my example, it’s going to cover the width of the entire record. Give it a meaningful name so it will help with coding and referencing later. I’m calling my newly added control; txtBackgroundBar.

Notice the above added control is bound to a matching field (Customer ID) you intend to locate with the search method. I’m using the unique identifier so it will be clear there can only be one matching value.

Make a note of the name of your Combo box, drop-down control (if that’s the type of control you are using) so this can be referenced in the conditional formatting setting next and for simplicity, you set the background colour to the colour you wish to use for your highlight – you will see the reason why with the code later.

With the conditional formatting tool loaded (this will vary depending on which version you use – I’m using Access 2013), set the following:

Next, we normally need to add some Access VBA code to enhance Microsoft Access database forms and with this example, it’s going to dynamically highlight the current record.

There are many events to a form and individual controls and the event that will change state when navigating to a record whether it be manually via the navigation keys or via the drop-down box is called ‘On Current‘. The code looks like:

Private Sub Form_Current()
   If Nz(Me.Customer_ID) <> "" Then
      Me.cboCurrentRec = Me.Customer_ID
   End If
End Sub

For each control on the form (as part of the record) needs to appear highlighted with your chosen colour and to avoid the background turning white (when clicking on the field), the following function code is called:

Private Function ChangeBackColour()
   On Error Resume Next
   Screen.ActiveControl.BackColor = Me.cboCurrentRec.BackColor
End Function

You the choose the trigger event ‘On Got Focus‘ (and repeat for each control) the above function by typing “=ChangeBackColour()” (make sure you include the brackets at the end).

Save and test your form.

Remember, the added Text box control which acts as the background can also be edited and you may want to avoid the ability to enter (enabled and lock properties) or add more VBA code to prevent such changes including the possibility setting the focus elsewhere.

You may need to tweak and tidying both arrangement of control fields and general formatting but in essence you now have a dynamic current record highlighter.

Microsoft Access database forms is an art and takes time to really master. You may wish to look at some of my eBook offers including how to build forms for further ideas and tips.

Tags: access form designs, access vba code, microsoft access database forms, microsoft access form, ms access conditional formatting, using access constitutional formatting