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

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

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

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.

Microsoft Access Form

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.

Microsoft Access Form

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:

Microsoft Access Form


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).

Microsoft Access Form

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: , , , , ,

12 Responses so far.

  1. Giorgio says:

    Once you select an account from the drop-down box, what makes the cursor navigate to the matching record on the continuous form?

  2. Giorgio says:

    Hi, could you please explain what
    If Nz(Me.Customer_ID) “” Then
    Me.cboCurrentRec = Me.Customer_ID
    End If
    I would also like to know what sends the cursor to navigate to the matching record once you select a value in the cboCurrentRec combo box.

    • Ben says:

      Hi Giorgio,

      The VBA code you ask tests to see if the Customer ID field is empty (string) ans also using the NZ function (Null to Zero) just in case the value is not empty but a Null instead which can happen (but not likely due to Customer ID can not be left blank at data entry point) – it’s just a fail safe!

      If it’s not empty then set the combo box value with the customer ID which is what the conditional formatting element will eventually need.

      The cursor moves to the selected record based on the default embedded macro I created (Access 2013) which simply uses the customer ID first record match using the expression

      =”[Customer ID] = ” & “‘” & [Screen].[ActiveControl] & “‘”

      BTW: In order to all comments to appear on my blog must firts be approved by myself – hence the delay in seeing your posts.

  3. Giorgio says:

    Thanks Ben, what triggers the embedded macro and are embedded macros only available from Access 2010 onwards?
    Also you choose the value from the combo box so why set the cboCurrentRec combo box to Customer ID?

    • Ben says:

      Embedded macros wee introduced from 2010 but any macro from previous versions can be used to find a record match. The wizard find action for a drop-down will pretty much build it for you. The event that triggers the macro is the After Update event for the combo box control.

      It could be an option to set the customer ID value from the combo box but I wanted to use the colour formatting presented here and just an easier way to call the backcolor property in the VBA code.

      There are many ways to achieve this task and for more complex procedures, one would probably use VBA!

      I’m happy to zip over an example file from this article if that will help. Which version do you have?

  4. Giorgio says:

    Thanks, I use Access 2007/2010 and looking at the example file would clear things up since I’m not sure how else you would set the Customer ID value other than selecting it via the cboCurrentRec combo box which I believe is what
    =”[Customer ID] = ” & “‘” & [Screen].[ActiveControl] & “‘”
    does so Me.cboCurrentRec = Me.Customer_ID seems counterintuitive since even conditional formatting sets Customer ID = cboCurrentRec and it’ll also allow me to see why txtBackgroundBar doesn’t hide the data in the text boxes beneath it.

  5. Giorgio says:

    I’ve never used embedded macros 😉
    What makes txtBackgroundBar not hide the data in the text boxes beneath it?
    Why do you have those XML statements in the embedded macro comments?

  6. Giorgio says:

    I tried to post what follows four days ago but I get the message: “Duplicate comment detected; it looks as though you’ve already said that!”, then why doesn’t it show amongst the comments?

    I’ve never used embedded macros 😉
    What makes txtBackgroundBar not hide the data in the text boxes beneath it?
    Why do you have those XML statements in the embedded macro comments?

  7. Giorgio says:

    Thanks Ben,
    “The file I sent will have the macros and can be exported if necessary.”
    I’m not sure which of my last two questions this answers.
    By the way, when are you going to tackle Access Web Apps?

    • Ben says:

      Access web apps is not currently on my radar as I consider this level of database for the web not always the best way tool for the job and that other stronger and better database engines are considered better.
      MS Access has a place in LAN based systems and not WAN based and with the scope of web apps in Access allowing for better wide area use, there are too many limitations still to consider this a real winner coupled with also having to adapt another service like SharePoint (or the equivalent).