Changing "Entry Key Behaviour" to Allow New Lines within a Textbox

In my previous blog post, I wrote about Adding Rich Text Formatting to MS Access Data.  However, there was one related topic which I didn't cover - ie how to change the entry key behaviour to allow new lines within a text box.  This is done by changing the ENTRY KEY BEHAVIOR property of the text box in question.

The ENTRY KEY BEHAVIOR property set to 

NEW LINE IN FIELD for the txtDescription text box.

Let's imagine we have a text box on a form in which we are going enter a large amount of text data separated by paragraphs. We have set the field in the underlying table to accept LONG TEXT data, and perhaps also enabled rich text formatting.  The first thing we do is resize the text box in FORM DESIGN view by selecting the text box and dragging the lower right corner so it is large enough to display a good area of text without having to constantly scroll down.

If we left the ENTRY KEY BEHAVIOR property at its default setting, it would not be possible for a user to create a new paragraph within the text box whenever they hit the ENTER key.  What we need to do, therefore, is change the property value from DEFAULT to NEW LINE IN FIELD. This is how:

1. Open the form in DESIGN VIEW.
2. Select the text box control to be modified by clicking it.
3. Open the PROPERTY SHEET by clicking the icon in the TOOLS group of the DESIGN ribbon (if it's not already open).
4. Click the OTHER tab on the property sheet.
5. Locate the ENTRY KEY BEHAVIOR property and change the value to NEW LINE IN FIELD.

Once the property has been changed, the user can enter text in the text box and use the ENTER key to create a new paragraph without the focus shifting to the next control in the tab order.

Above: Example of a text box with the ENTRY KEY BEHAVIOR property modified to

  NEW LINE IN FIELD.

Adding rich text formatting to Access data

When we store information in an Access for Office 365 database, text formatting (eg setting text as bold or italic, and changing font size etc) isn't normally a consideration. For example, if we have a field storing a title, it is very unlikely that we would need to save the titles' font size.  This is something we can add later to a text box property of a form or report where the date is presented to the user.

Above: notice the text box for the task field
 is in a larger font with bold text. 

Above: the property sheet where
 the db designer sets formatting
 properties of the text box. 

However, there are certain instances when we do want to save formatting information relating to the data stored in a particular field of a table. This may be the case when a field contains a large amount of textual data, and we want the user entering the data to decide which text within the field is formatted in a particular way; not the database designer who can only apply formatting to all text displayed in any given text box control.

So how do we add rich text formatting to an Access for Office 365 database field?  

Rich text formatting is achieved through the addition of html markup tags to the text they relate to.  These are the same tags used by web pages for formatting content.  However, we don't need any knowledge of HTML markup, because Access for Office 365 does it for us.  All we need to do is change the TEXT FORMAT property of the field within the database table.  NB this can only be done on a table field set to the LONG TEXT datatype.

Here are the instructions for setting up a LONG TEXT field for rich text formatting:

1. Open (or create) the table containing the field to be set to rich text in DESIGN VIEW.
2. Change the data type for the field to LONG TEXT in the TABLE DESIGN GRID.

   

   Above: the entry field being set to LONG TEXT.

3. Change the TEXT FORMAT property to RICH TEXT in the field property grid.

   

   Above: changing the TEXT FORMAT property to RICH TEXT.

   
   

We can now change from TABLE DESIGN VIEW to DATASHEET VIEW and add rich text formatting to data stored in the field we have just modified.  Do this by highlighting some text within the field and selecting the desired formatting from the context menu that opens automatically. Alternatively, you can also use the TEXT FORMATTING  group of icons from the HOME ribbon.

Above: an example of rich text formatting within an Access for Office 365 table field.

Displaying formatted data on Access for Office 365 forms and reports.

Now that we have set up the table for rich text formatting, there is one other thing we need to do if we want to display our formatted data in a text box control on a form or report. This involves changing the text box's TEXT FORMAT property to RICH TEXT.

1. Open the form containing the text box we want to change in DESIGN VIEW.
2. Select the text box control by clicking it in the design grid.
3. Open the properties sheet by clicking the PROPERTIES SHEET icon from the DESIGN ribbon.
4. Click on the DATA Tab on the properties sheet, and change the TEXT FORMAT property to Rich Text.

When we now re-open the form in FORM VIEW, rich text formatting is applied to any text the user had previously set before it was saved in the forms underlying table. 

Above: an example of rich text formatting within an Access for Office 365 text box control. 
Had we left the TEXT FORMAT property as PLAIN TEXT, any data displayed in the text box would 
not have been formatted; any text that should have been, would have appeared in plain text between html markup tags.

Using Conditional Formatting on an Exam Results Report

Conditional Formatting may be applied to Text Box and Combo Box controls on Access Forms and Reports.   It allows us to format the data contained in a particular Text or Combo Box, according to value of that same data.  For example we can create Conditional Formatting for a text box to display a currency value, which when less than 0, will display that value in a red font, instead of the normal black.

Moreover, as well as formatting the Text or Combo Box according to the value contained in that same control, we can also format the Text or Combo box according to the value contained in another field of the same record.  So, for example, we can display a Text Box containing a CompanyName in bold font if the value of its AnnualTurnover field is greater than or equal to, say,  £100,000.  The great thing is, the data contained in the AnnualTurnover field does not necessarily have to be displayed on the form or report, as long as it is stored in the form or reports underlying Record Source.  In order to do this, we would enter an expression for the condition.

So let's take a look at how we go about applying Conditional Formatting using the example of a Student Exam Results Report.  The report applies conditional formatting based on the students exam result.  Students records with a result above 74 % are displayed in green, and those below 40 %, in red. The formatting for those in between is set to blue by default. To enhance clarity, I have also applied a sort on the Results field, thereby ordering the records from highest result to lowest. If you wish, you can download the complete solution by clicking the link below:

Student Exam Results

Our example database contains a table, tblExamResults, and report, rptExamResults- the table being the Record Source for the report.  Our table contains a list of student names and their corresponding exam results:

Figure 1: tblExamResults - The Record Source for our Report.

The rptExamResults report is based on this table.  I created it by clicking the REPORT icon (located in the REPORTS group of the CREATE ribbon) whilst tblExamResults was highlighted in the NAVIGATION PANE.  I then just needed to tidy it up in DESIGN VIEW, and add the Conditional Formatting (on each text box on the report) in addition to a SORT on the Results field.  This is the finished result:

Applying Conditional Formatting.

To begin with, lets look at how we apply Conditional Formatting to the Result field of rptExamResults.

1. Open the report in DESIGN VIEW
2. Right click the Result Text Box.
3. Click CONDITIONAL FORMATTING from the drop down menu.
4. When the CONDITIONAL FORMATTING dialog form opens, set the conditions as described in the next stage of the process below.

Figure 2: The Conditional Formatting dialog form.  It is possible to have a maximum of
three different conditions plus the default formatting.  

Setting Conditions based on FIELD VALUE IS

The next stage is to set the actual conditions for the Result field of the report (see Figure 2 above). NB: when FIELD VALUE IS is selected, it refers to the value contained in the text box being formatted:

1. First set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the  FIELD VALUE IS GREATER THAN 74.  If necessary click the drop down list to select FIELD VALUE IS, and then do the same in the next combo box along to select GREATER THAN.  Then just enter the value 74 in the third box along, and select the BOLD and RED icons.  You should see a preview in the box below when done.
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when FIELD VALUE IS LESS THAN 40.
5. Click OK to close.

Setting Conditions based on EXPRESSION IS

The next stage is to set the conditions for the StudentId field.  Since we are not basing the condition on the value contained in the StudentId field itself, we need to construct an expression which refers to the value contained in the Results field. As such, we shall be selecting EXPRESSION IS from the appropriate drop down lists, rather than FIELD VALUE IS.

1. Again, set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the EXPRESSION IS [Results]>=75.  Do this by clicking the drop down list to select EXPRESSION IS, and then enter the expression in the elongated box (see Figure 3 below).  Then select the BOLD and RED icons as before.  
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when EXPRESSION IS [Results]<40
5. Click OK to close.

Figure 3: Condition based on EXPRESSION IS.  Note the elongated box where the expression is entered.  This appears when EXPRESSION IS is selected from the drop down list.

Finish the report by repeating this last stage for the FirstName and Surname fields using the EXPRESSION IS selection when creating the conditions.

Calculated Controls

As well as being easy to use, Calculated Controls can be a really useful tool for the Access Developer. They provide a flexible way to display data on form's, without being restricted to information directly derived from a  field in a table or query.  We are all familiar with the simple Text Box Control.  Ordinarily these are bound to a particular field defined in the Text Box's Control Source - ie the control's property that links the Text Box with the particular field that supplies its data.  Calculated Control's, however, are slightly different.  Rather than using a field from a table or query to supply the information displayed in the control, we instead enter an expression into the text box control source.

Figure 1 (above): This is the property sheet for a Calculated Control.
The CONTROL SOURCE is located on the top line of the DATA TAB.

Figure 2: This is how the Calculate Text Box Control appears in FORM DESIGN VIEW.
Notice how the expression is displayed in the text box itself.  All expressions
begin the the = sign, and may consist of operators, identifiers, constants and functions.

The expression I have entered in Figure 1  is used to perform a mathematical calculation.  It works by multiplying the values contained in two bound text box's on the same form in order to produce a Total Amount in the calculated control. In this example I have been able to work out the total value of an order item based on the Unit Cost and Quantity Ordered.  Here is the finished result:

Figure 3: The Calculated Text Box Control multiplies
the UnitCost by Quantity to produce a Total.

The procedure for setting up a Calculated Control such as this is really quite easy.  It is useful, however, to have some knowledge of creating and modifying forms in DESIGN VIEW.  This is how I created the Calculated Text Box Control:

1. I began with an existing form called frmOrderDetails.  The form was bound to a table called tblOrderDetails.  The form began with 4 text box's displaying the ProductId, OrderId, UnitCost, and Quantity fields.
2. The form was opened in DESIGN VIEW.  This was done by right-clicking the frmOrderDetails form, and selecting the Design View Icon from the drop down menu which opened.
3. An unbound Text box Control was added by clicking the TEXT BOX icon and positioning the control on the form design grid.  The TEXT BOX Icon is located on the CONTROLS group of the DESIGN ribbon.  I had to make sure the USE CONTROL WIZARDS icon was not highlighted before doing so.
4. I then highlighted the new unbound Text Box Control and clicked the PROPERTY SHEET icon on the TOOLS group of the DESIGN RIBBON.
5. I needed to select the DATA tab on the newly opened PROPERTY SHEET.
6. I then entered the expression =[unitcost]*[quantity] into the CONTROL SOURCE property.  Unitcost was a reference to the bound UnitCost  text box, and Quantity was a reference to the bound Quanty text box.  These were the expression's Identifiers, and the * symbol was it's multiplication operator. NB I could have typed this expression directly into the text box on the DESIGN GRID - it would have set the CONTROL SOURCE property without having to open the PROPERTY SHEET.
7. Then when I opened the form and entered values in the two bound fields of UnitCost and Quantity, the Total Amount appeared automatically in the calculated text box control.

Creating an Access 2007 Form from Scratch

This post complements yesterdays article on Customizing an Access Form. This time we are going to look at Creating an Access 2007 Form from scratch.

1/ Begin by clicking the Form Design Icon on the Create Ribbon. This opens a blank new form in Design View.

The Form Design Icon on the far right of the Create Ribbon.

Form Design View and the Property Sheet.

2/ Before we start creating text boxes and other form controls, we are going to bind the form to an Access Table that has already been created. This is so Access knows where your forms' data is going to come from. We do this by setting the Record Source property. If the property window is not already open, click the property sheet icon in the Tools group of the Design Ribbon.

3/Select the Data Tab of the property sheet. This will display a grid of properties relating to your form's data. It is the Record Source property that we are currently interested in. Click the arrow to display a drop down list of potential database tables and queries that we can use. In this example we are going to use the table called tblAccounts, which we will select by clicking.

Record Source property and the Property Sheet.

4/ We are now in a position to begin adding form Controls such as Text Boxes and Labels. These are located in the Controls group on the Design Ribbon. We shall now click on the text box icon and then move then cursor to the area of the form where we would like it to go. Then simply right click the mouse over this point. This will place the text box control on our form.

The Controls group of the Design Ribbon.
The Text Box control is on the far left of the image.

5/ We shall now set the Control Source property of the text box. This determines which field from the form's Record Source will be bound to the control. Do this by clicking the arrow to display a drop down list of field names from the table which we earlier selected as our record source. We shall choose the Description field. Like in stage 3, simply click the field name in the list to select.

The Control Source Property.
Selecting a field name from the drop down list.

6/ You can now give your control a meaningful name. This is now displayed anywhere on the form itself, but it will be the name that you use to reference it later on in the design process. Do this by clicking the Other tab of the property sheet. The Name property should be at the top of the grid. Access gives it a default name such as Text1. You can simply type the name Description over this on the grid.

The Name Property for our text box control.

7/ We shall now change the Caption property of the label on the left of the text box. Unlike the Name property which we set in the previous stage, the Caption is displayed on the form to provide information to the user. In this case, it is going to tell the user that the information to enter in the text box is going to be "Description". Do this be selecting the label on the design grid by right clicking so that it goes orange. Next click the Format Tab on the property sheet and type in "Description" on the property grid. Alternatively you can click the label on the design grid to select, and then click inside the label to overwrite the old label caption directly.

You can now repeat this stage, adding as many text box controls as you need.

The Caption Property for our Label control.

8/ We will now finish off the form by entering a title. Do this by clicking on the Label control on the Controls group of the Design Ribbon. Then choose a position at the top of the grid, and click to add the label control to our form. We can now add our title text the same way as we entered the caption in the previous stage. Once we have entered the title caption, we can now increase the font size and make the text bold. Do this by right clicking the label to select, and entering a new Font Size of 24, and clicking Bold in the Font group of the Home Ribbon. You will also need to resize the label by double clicking the the notch on the labels border.