Coloured Tabs for Your Access Forms
The Tab Control is a great way to get a lot of stuff into
a small space on an Access form. It looks good too, as long as you
are satisfied with the "Standard" colour scheme. But if you want to
give your forms a custom colour scheme you will find that some
things can't be given custom colours, and the Tab Control is one of
I was asked recently if this could be done, and my first reaction
was to say "No", but in this business you soon learn that you should
never say "never". So I set about figuring out a way to do it.
Here's what I wanted to end up with...
In this tutorial you can follow the steps I took to achieve
something that, whilst not being a real tab control, works just like
one. And has the added benefit of blending in with any colour scheme
you choose. Along the way you will learn some form design tricks,
and how to manipulate form control properties with VBA. You'll also
see an example of form validation - making sure the user supplies
all the data required.
The Task at Hand
I have an Access table containing information about people. It
contains 17 fields, divided into three topics: Personal information,
Business information and Contact details. I'd like to see each group
of fields on a separate page of a tab control, and I want each page
and its corresponding tab to be a different colour. I also want to
make sure that the user fills in all of the fields before proceeding
to the next page.
Here's an interactive demo of what it is going to look like.
Click the coloured tabs to move from page to page:
for the image above won't work. You can see a composite image of the
different pages by clicking the thumbnail below (click the Back
button on your browser to return to the tutorial):
A Tab Control on an Access form is like a set of filing card
dividers. They sit in a stack on the form and you can see only what
is on the one that is on the top of the stack. But you can see all
the tabs. Clicking a tab brings that card to the top of the stack so
you can see what's on it.
The Tab Control has a has a different surface (called a "page")
for each tab, so when you are building your form you can place what
you want on the appropriate page. As you will see, we have to take a
different approach here. We are going to build something that looks
like a tab control but it's all "smoke and mirrors". There will be
only one "page" and clicking a tab will change both its colour and
the fields it displays.
Prepare the Data
You can use your own data for this tutorial if you wish, adapting
the instructions to your own needs. If you want to follow each step
exactly, you need to create a table upon which to base your form.
You can work in an existing Access database or create a new one.
- Create a new table containing the following fields:
ID, FirstName, LastName, Address,
City, PostCode, Country,
EmployeeID, JobTitle, CompanyName, Department,
HomePhone, WorkPhone, Mobile, Fax, eMail,
They can all be Text fields except ID which is
an AutoNumber and also the Primary Key.
- Close and save the table. Call it tblData.
Build the Form
In this step we build a form based on this data. Click the
thumbnail images to display full-sized illustrations of the various
steps (click the Back button on your browser to return to the
||From the database window go to Insert > Form
to display the New Form dialog and choose Design
View. Choose tblData from the drop-down list at the
bottom and click OK. Access creates a new, empty form.
||Drag the edges of the detail section (the form
background) to width of 9cm and height of 6cm.
the Rectangle Tool to draw a rectangle, a few centimetres
square (the size doesn't matter now), on the form. After
releasing the mouse don't click anywhere else. Leave the
||With the rectangle selected press the F4
key on your keyboard to display the Properties window for
your rectangle (alternatively go to View > Properties or
right-click the edge of the rectangle and choose Properties).
The reason I didn't suggest an exact size for the rectangle is
that we are going to do that accurately by typing dimensions
into the Properties window. Click the Format tab of the
Properties window and enter the following values:
1cm, Top: 1cm, Width: 7cm, Height: 4cm,
Special Effect: Flat, Border Width: Hairline
use the Fill/Back Color Tool to select a colour
for the rectangle. I chose Pale Green. If you don't see a
suitable colour on the palette, go back to the Properties
window and look at the Back Color property. This is shown
as a long number. Click on the Back Color text box to
display the build button (
Clicking the build button displays a full colour chart from
which you can make your choice.
Use the Name property to give the rectangle a sensible
name. In this example I am using the name boxMain.
the Label Tool to draw a label about 2cm wide and 1cm
high anywhere on the form. Without deselecting the label, type
the heading for the first page. In this example I'm using the
heading "Personal". Press the Enter key to accept your
text (at the same time selecting the label) then open the
Properties window as before and enter the following values:
Left: 1cm, Top: 0.5cm, Width: 2cm,
Height: 0.5cm, Border Style: Solid, Border Width:
Fill the label with the same colour that you used for the
rectangle in the previous step. You might notice that the text
is a little too close to the upper left corner of the label.
Adjust the properties as follows to make it look better:
Left Margin: 0.1cm, Top Margin: 0.05cm
Small adjustments like this make all the difference when you
want to create professional looking forms...
NOTE: If your headings are longer than will fit, adjust the
text box size (and other dimensions quoted here) to suit your
Use the Name property to give the label a sensible
name. In this example I am using the name lblTab1.
||Now we have to get rid of the line along the
bottom edge of the label, so that it looks as if the rectangle
and label are a single item. We can't just rub out the line, so
instead we'll cover it up. Draw another rectangle, fill it with
the same colour as before, and set the following property
Left: 1.025cm, Top: 0.95cm, Width:
1.95cm, Height: 1cm, Special Effect: Flat,
Border Style: Transparent
If you take a look at the form in Form View you will
see that the dividing line between the label and the rectangle
has disappeared (if you can still see any of it, try adjusting
some of the dimensions of the new rectangle)...
Use the Name property to give the new
rectangle a sensible name. In this example I am using the name
||We don't need any more pages (we are going to
use the same page all the time, using code to change its
appearance) but we need some more tabs. The easiest way to do
this is to make copies of the original label (the one with the
text Personal in my example).
Click on the label to
select it and make a copy. I use the keyboard shortcut Ctrl+C
followed by Ctrl+V. Alternatively go to Edit > Copy,
then Edit > Paste. This creates an exact copy of the
first label. Now fill it with a different colour (I used Pale
Yellow in this example) and set its properties as follows:
Left: 3cm, Top: 0.5cm, Caption:
Business, Name: lblTab2
(You can use your own Caption and Name as you choose).
Repeat the process to create a third tab, filling it with a
different colour (I used Pale Blue) and set its
properties as follows:
Left: 5cm, Top: 0.5cm, Caption: Contact,
||We also need two more of the small rectangle
colour patches. Create them by making copies of the original and
filling them with the correct colours. Here are the property
values you need:
Left: 3.025cm, Top: 0.95cm,
Visible: No, Name: boxPatch2
Left: 5.025cm, Top: 0.95cm, Visible: No,
The reason for setting the Visible property of these
two rectangles to No can be seen in the illustration
below. When you look at the form in Form View they
disappear (we will use VBA code to make the rectangles appear when
we need them).
||Finally, we add the data controls to the form.
In this case they are all text boxes. I have organized my data
so that the Personal category requires 6 text boxes, the
Business category requires 5 text boxes, and the
Contact category requires 6 text boxes.
The plan is to
create the maximum number of controls that will be required at
one time (i.e. 6). As different pages require different numbers
of controls, we will use code to hide and unhide controls as
necessary. The controls will initially be unbound (i.e.
they will not be connected to specific fields in the underlying
table). This will be done later with code, when a Control
Source for each text box will be defined as well as an
appropriate Caption for its label.
Use the Text Box tool to place a text box on the
centre of the form. Click the Text Box tool then click somewhere
near the centre of the main coloured rectangle. Select the text
box and set the following properties:
Left: 4cm, Top: 1.5cm, Width: 3.5cm,
Then select the label and set the following properties:
Left: 1.5cm, Top: 1.5cm, Width: 2.4cm,
Name: lblField1, Caption: Field 1
The remaining text boxes can be added by making copies of the
first one. To do this, select the first text box and Copy
then Paste (keys: Ctrl+C then Ctrl+V). This
places an exact copy of the first text box just below it. I want
to move the second text box a little closer to the first. Do
this by clicking the Up Arrow on the keyboard a few times
(I thought 4 clicks did the job).
||We need four more text boxes. It isn't
necessary to copy again, just paste four times. Not only does
Access remember the correct dimensions of the objects being
pasted, but also the adjusted spacing!
To make sure that all
the controls are perfectly aligned, select all of the textboxes
by holding down the Shift key and clicking on each one
until all are selected. Then go to Format > Align > Left.
Do the same to align all the labels.
Finally, change the Properties to give each text box a
suitable name (I used txtField1 to txtField6), and
each label a suitable name and caption (I used lblField1
to lblField6 and Field 1 to Field 6).
||The last form design task is to remove any
features that aren't needed and add a suitable caption. Open the
Properties window for the form itself. To do this
double-click the form selector in the upper left corner of the
form design window...
On the Format tab set the following properties:
Caption: Coloured Tabs Demo; Scrollbars:
Neither; Record Selectors: No; Dividing Lines: No
Write the Code
The building of the form is now complete. The next task is to add
the VBA code to make everything work. The code has to do do several
- Change the colour of the main box to match the tab that the
user clicked, and at the same time hide or unhide the appropriate
colour patches so that the box and its matching tab appear to be
- Change the control source of each text box so that any data
entered goes into the correct field of the underlying table.
- Change the text of the label for each text box to describe its
- An optional feature: check that all the fields have been
filled-in before allowing the user to move to the next tab.
These actions have to be performed each time the user moves from
one tab to another, and also when the form opens.
TIP: Now is a good time to Save the form in
case anything goes wrong whilst you are testing your code. I
normally also save a backup copy of a form, especially if I have
spent a long time working on its design!
Before starting to write the code, make a note of the colour
codes you chose for the different tabs. Look at the BackColor
property of each label. You will see that your colour choice is
represented as a long number. You will need this when setting the
colour in the code.
The various procedures are described individually below, and a
summary of the entire code module is given at the end.
The Form_Open Event Procedure
When a form opens, the Form_Open event occurs. We will use
this event to set up the form so that the first tab is visible, and
the text boxes and their labels refer to the first set of fields.
Open the Properties window for the form itself and on the
Event tab locate the On Open event. Click on its text
box then the build button (
). Choose Code Builder from the dialog then click OK
to open the code window.
Private Sub Form_Open(Cancel As Integer)
Place your cursor between the two lines of code and press TAB
to indent your code and type:
Me.boxPatch1.Visible = True
Me.boxPatch2.Visible = False
Me.boxPatch3.Visible = False
These lines set the visible property of each of the coloured
patches so that only the first can be seen. The keyword Me
refers to the form itself. This is shorthand for referring to the
current form from its own code module. Note that when you type the
dot after the word Me a list of all the acceptable
entries appears. You can save typing and also avoid making typos by
scrolling to the item you need then double-clicking it to add it to
The next line sets the background colour of the main rectangle:
Me.boxMain.BackColor = 13434828
The number corresponds to the colour chosen for the Back Color
property of the box. You can copy this number from the property
sheet or, if you know the Red/Green/Blue components of your chosen
colour you can use the VBA RGB function to generate the
number for you. Your line of code would look something like:
Me.boxMain.BackColor = RGB(204, 255, 204)
Add the following lines to assign the correct caption for each
the text box labels:
Me.lblField1.Caption = "First Name:"
Me.lblField2.Caption = "Last Name:"
Me.lblField3.Caption = "Address:"
Me.lblField4.Caption = "City:"
Me.lblField5.Caption = "Postcode:"
Me.lblField6.Caption = "Country:"
Finally, add the following lines to assign the correct control
source to each text box. This command dictates which field in the
underlying table each text box is bound to:
Me.txtField1.ControlSource = "[FirstName]"
Me.txtField2.ControlSource = "[LastName]"
Me.txtField3.ControlSource = "[Address]"
Me.txtField4.ControlSource = "[City]"
Me.txtField5.ControlSource = "[Postcode]"
Me.txtField6.ControlSource = "[Country]"
You might think that this procedure is not necessary, and
logically you'd be correct. All these properties are already set and
saved in the design of the form itself, so why set them again when
the form opens? Well, I believe in belt and braces (suspenders if
you speak American). If your braces snap, with any luck your belt
will stop your pants falling down.
The lblTab1_Click Event Procedure
This event fires when the user clicks the first coloured tab (the
one labelled Personal in my example). It does exactly the
same as Form_Open event procedure but in different
circumstances. It is necessary because the user may want to return
to this tab after visiting one of the others.
You can create the procedure as before but, since you are already
in the Visual Basic Editor window, you can do it from here using
the two combo boxes at the top of the Visual Basic Editor code
window. Open the left-hand (General) list and click on the
control's name (lblTab1).
As the default event for this
control is the Click event, that is chosen automatically from
the right-hand (Declarations) list. The Visual Basic Editor
creates the lines:
Private Sub lblTab1_Click()
You can copy all the lines you typed into the Form_Open
procedure and paste them into this one. In addition, add the
Me.lblField6.Visible = True
Me.txtField6.Visible = True
These SetFocus statement is added for the user's
convenience. The statement in this procedure occur when the user
clicks the first tab (i.e. when they are moving to this tab from one
of the others). If the SetFocus statement was omitted, the
focus would remain with the control that had it on the previous tab.
By setting the focus to the first control whenever the user switches
from tab to tab, the illusion of separate tabs is reinforced.
The statements making the sixth text box and its label visible
are necessary because, as you will see later, the second tab
requires only five fields and the sixth text box and its label are
hidden when the user moves there.
The lblTab2_Click Event Procedure
Create an event procedure for the Click event of the
second tab and enter the following lines of code:
Private Sub lblTab2_Click()
Me.boxPatch1.Visible = False
Me.boxPatch2.Visible = True
Me.boxPatch3.Visible = False
Me.boxMain.BackColor = 10092543
Me.lblField6.Visible = False
Me.txtField6.Visible = False
Me.lblField1.Caption = "Employee ID:"
Me.lblField2.Caption = "Job Title:"
Me.lblField3.Caption = "Company Name:"
Me.lblField4.Caption = "Department:"
Me.lblField5.Caption = "Line Manager:"
Me.txtField1.ControlSource = "[EmployeeID]"
Me.txtField2.ControlSource = "[JobTitle]"
Me.txtField3.ControlSource = "[CompanyName]"
Me.txtField4.ControlSource = "[Department]"
Me.txtField5.ControlSource = "[LineManager]"
This procedure is similar to the previous one but assigns a
different group of fields to the text boxes, and labels them
accordingly. Because this tab requires only five fields, the sixth
text box and its label are hidden by setting their Visible
property to False (which explains why that property was set
to True in the previous procedure - so that it reappears when
The colour of the main box is changed to match the colour of the
tab that the user clicked, and the various coloured "patches" are
hidden or unhidden as necessary to maintain the illusion of separate
The lblTab3_Click Event Procedure
Like the others, this procedure assigns the correct fields to the
text boxes and labels them accordingly. And effects the formatting
changes to give the appearance that the third tab has been brought
to the front:
Private Sub lblTab3_Click()
Me.boxPatch1.Visible = False
Me.boxPatch2.Visible = False
Me.boxPatch3.Visible = True
Me.boxMain.BackColor = 16777164
Me.lblField6.Visible = True
Me.txtField6.Visible = True
Me.lblField1.Caption = "Home Phone:"
Me.lblField2.Caption = "Work Phone:"
Me.lblField3.Caption = "Mobile:"
Me.lblField4.Caption = "Fax:"
Me.lblField5.Caption = "eMail:"
Me.lblField6.Caption = "Website:"
Me.txtField1.ControlSource = "[HomePhone]"
Me.txtField2.ControlSource = "[WorkPhone]"
Me.txtField3.ControlSource = "[Mobile]"
Me.txtField4.ControlSource = "[Fax]"
Me.txtField5.ControlSource = "[eMail]"
Me.txtField6.ControlSource = "[Website]"
That's it! You can save the form and try out the code. As you
move from tab to tab the colour of the background changes and the
text box labels change to represent different fields.
Adding Simple Validation...
Making Sure the User Fills In All the Fields
A Function to Pinpoint Missing Data
Here is an example of how you can write a piece of "generic" code
to check that the form has been filled in. You could, of course, set
the Required property of each field to Yes but this
method involves less work and gives you the opportunity to display a
friendly message and take the user straight to the appropriate
In the same code module as the form's event procedures, create
the following custom function. You can enter it after the last of
the event procedures:
Private Function CheckData() As Boolean
CheckData = True
Dim ctl As Control
Dim strName As String
For Each ctl In Me.Controls
If IsNull(ctl) Then
strName = ctl.Controls(0).Caption
CheckData = False
MsgBox "Please fill in the " & Chr(34) & strName & Chr(34) & " field."
How the Function Works
The CheckData function returns False if it finds an
empty text box or True if it does not (hence its declaration
as Boolean). The first line sets the value of the function to
True. It uses a For... Next loop to look in turn at
each control on the form. If no empty controls are found the
function returns the value True.
If it comes across an empty control an If Statement is
invoked in which it notes the control's Caption (i.e. the
Caption property of the label of a text box) and sets the value
of the function to false. It then displays a message box
incorporating the control's caption telling the user which control
is missing data. Finally it sets the focus of the form to that
control by placing the user's cursor into the control. At this point
the function exits so that the user can correct their input.
Applying the Function
To make use of the function, add the line:
If CheckData = False Then Exit Sub
as the first line of each of the Click event procedures
for the three tabs. When the user clicks one of the tabs, before
anything else happens, the function is evaluated. If it returns
True (meaning that there are no empty fields) the rest of the
Click event procedure runs. If it returns False the
Click event procedure exits (i.e. is cancelled) and the user is
shown a message and taken to the appropriate field.
Follow this link to see the code for the completed procedure
You can download a fully working demo database
illustrating the techniques covered in this tutorial. The files are
provided in Access 97 and Access 2000 format, and also as Zip files
for faster download (you will need a copy of WinZip or a similar
program to extract the zipped files).