• Printer Friendly Version
• Access Index
• VBA Index
• Download the File

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

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:

If your browser does not have JavaScript enabled my click effects 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.

1. Create a new table containing the following fields:
   ID, FirstName, LastName, Address, City, PostCode, Country,
   EmployeeID, JobTitle, CompanyName, Department, LineManager,
   HomePhone, WorkPhone, Mobile, Fax, eMail, WebSite
   They can all be Text fields except ID which is an AutoNumber and also the Primary Key.
2. 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 tutorial).

3.	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.
4.	Drag the edges of the detail section (the form background) to width of 9cm and height of 6cm.
5.	Use 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 rectangle selected.
6.	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: Left: 1cm, Top: 1cm, Width: 7cm, Height: 4cm, Special Effect: Flat, Border Width: Hairline Now 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.
7.	Use 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: Hairline 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 own requirements. Use the Name property to give the label a sensible name. In this example I am using the name lblTab1.
8.	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 values: 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 boxPatch1.
9.	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, Name: lblTab3
10.	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, Name: boxPatch3 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).
11.	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, Name: txtField1 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).
12.	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 things:

• 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 connected.
• 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 purpose correctly.
• 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)

End Sub

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 your code...

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

End Sub

You can copy all the lines you typed into the Form_Open procedure and paste them into this one. In addition, add the following lines:

   Me.txtField1.SetFocus
   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.txtField1.SetFocus
   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]"
End Sub

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

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 coloured tabs.

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.txtField1.SetFocus
   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]"
End Sub

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

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."
       ctl.SetFocus
       Exit Function
     End If
   Next ctl
End Function

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 [new window].

Download the File

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

ColoredTabsDemo97.mdb	ColoredTabsDemo97.zip	Access 97 format.
ColoredTabsDemo2000.mdb	ColoredTabsDemo2000.zip	Access 2000 format.