Friday, 17 May 2019

Linking Access to an Excel Worksheet

Excel is great for number crunching, and can even be used as a basic database to store information.  However, when it comes to displaying and presenting information, the MS Access database has some big advantages over it's peer in the Office 365 suite of software. The reason for this is that Access has a number of tools and features specifically designed to make data easier to read and understand - the Reports facility being just one.

The good news is that Access has the ability to connect to, and work with, Excel data in two different ways.  Firstly, Access can import data from an Excel worksheet into an Access table.  This method lets us work with a "snapshot" of data from Excel as it was at the time the data was extracted (there is more information about importing from Excel in my post on "Importing and Exporting Data between Access and Excel").  The second method is to set up a "live" link between the Excel worksheet and the Access table. This has the advantage of giving us direct access to current data in the spreadsheet; so any updates, additions or deletions will show up in Access (after data is refreshed/reloaded) as soon as the data is saved in Excel. However, please do note that this connection is read-only; new data cannot be saved back into the Excel spreadsheet without specifically doing an additional export.

Today we are going to learn how to set up this "live" link. To do so, I have set up a sample Excel worksheet with fictitious personnel records.

Linking to Excel
  1. Create a new Access Database, or open an existing one.
  2. Select the EXTERNAL DATA ribbon.
  3. Click the NEW DATA SOURCE icon in the IMPORT AND LINK group.
    External Data Ribbon
    Above: Adding a New Data Source from the External Data ribbon.
  4. Select FROM FILE from the drop-down menu, and then EXCEL from the sub-menu.  This opens the GET EXTERNAL DATA - EXCEL SPREADSHEET dialog form.
    Get External Data - Excel Spreadsheet dialog form
    Above: The Get External Data - Excel Spreadsheet dialog form.
  5. Click the BROWSE button and select the Excel spreadsheet you want to link to.
  6. Click the LINK TO A DATA SOURCE BY CREATING A LINKED TABLE radio button, and then click OK.  
  7. The next page of the wizard opens where you should see a sample of the worksheet you are linking to.  If your worksheet had column headings, ensure the FIRST ROW CONTAINS COLUMN HEADINGS box is ticked, and click NEXT. (Please note that if your spreadsheet has more than one worksheet or named range, you will see an additional wizard page asking you to select the specific worksheet or range before the page in the screenshot below).
    MS Access - Link Spreadsheet Wizard
    Above: First page of the Link Spreadsheet Wizard.
  8. Enter the name you are going to call the linked Access table in the last page of the wizard, and click FINISH.

Once you have done this, you will see the new linked table appear in the left-hand Access navigation pane under TABLES.  Note how the linked table icon is a blue arrow pointing to the Excel logo, thereby indicating the table is linked to an Excel spreadsheet.

MS Access table linked to Excel
Above: The table linked to Excel.


Now the link has been established to the Excel worksheet, we are free to base forms, reports and queries on the linked table as if it is native to Access itself.



Friday, 5 April 2019

Using Custom Functions in Calculated Controls

Back in May 2012, I wrote a blog article on Writing Custom Functions for Access VBA.  Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers.  We do this by creating a public function with the VBA programming language and save it inside a global module within the database.

The blog I wrote in 2012 explained how these custom functions can be accessed from sub procedures associated with forms and reports elsewhere in the database.  Today, however, I am going to explain how custom functions can be utilised in calculated form controls, without needing to invoke them with VBA itself.

Let's begin by looking at the custom function we are going to use.

Custom Function to get Week Commencing
I have created a custom function called getWeekCommencing to calculate the week commencing date of a given week, in a given year.  For example, the week commencing date of the 14th week of 2019 is 1st April 2019 (if we count Monday as the first day of the week). As such the function takes two arguments - week number and year.  The full syntax for using the function is as follows:

getWeekCommencing(weekNumber, Year)

The code I used to create this function is as follows:

Public Function getWeekCommencing(intWeekNo, intYear)
On Error GoTo myError

    Dim intJan1 As Integer
    Dim varStartDate As Variant  
    intJan1 = Weekday(DateSerial(intYear, 1, 1), vbMonday)
    varStartDate = DateAdd("d", -(intJan1 - 1), DateSerial(intYear, 1, 1))
    getWeekCommencing = DateAdd("ww", intWeekNo - 1, varStartDate)  
leave:
    Exit Function
myError:
    MsgBox Error$
    Resume leave  
End Function


If you are unfamiliar with VBA you can still copy this code into your database and use it within a calculated control. There are instructions for doing this below:

Instructions for adding the function to your database.

  1.  Copy the code above onto your clipboard.
  2.  Open the database you are going to add the function to.
  3.  Select the CREATE ribbon.
    Above: The MODULE icon is located on the right-hand side of the CREATE ribbon.

  4. Click the MODULE icon from the MACROS AND CODE group.  This opens the VBA editor and creates a new module ready for the code to be added:
  5. Paste the code into the VBA editor as in the screenshot below:
Above: The VBA Editor:
You may notice that I have changed the default name of the Module (see the PROJECT EXPLORER in the 
left-hand side bar) from Module 3 to "myFunctions".  You can do the same by highlighting the module name in the 
project explorer and hitting the F4 button to bring up the PROPERTIES window. You may then change the name property
 to one of your choice.  However, this is not essential.
The custom function is now ready to use in a calculated form control. 
Setting up the Test Data for this Exercise

Before we create the form to test the function, we first need to set up a simple database table containing test data. The table needs just two fields: WeekOfYear and Year, both of which are of the NUMBER data type.

Above: tblWeeks in Design View

Above: tblWeeks in Form View with test data entered.


Once the table is set up, we can add our test data as I have done in the screenshot above. Although there are  52 week in the year, the first week may not start on a Monday, so the data entered in WeekOfYear field can be anything between 1 and 53 (so the possibility of a partial first week is offset by Week 53).

The Calculated Control

Now we have added the getWeekCommencing function along with test data to our database, we can create the form containing the calculated control.

Above: The Week Commencing Calculator form in Design View.


To do this we need to create a form bound to the tblWeeks table.  We then need to add three text boxes:  the first text box (txtWeekOfYear) is bound to the WeekOfYear field in tblWeeks, and the second (txtYear) is bound to the Year field.  The third text box (txtWeekCommencing) is unbound.  This is where we are going to add our calculated control which returns the week commencing date (based on the data displayed in the WeekOfYear and Year textboxes).

Here are the instructions for creating the calculated control:

  1. Create the third text box that we are going to use for the calculated control as in the screenshot above. 
  2. Display the PROPERTY SHEET if it is not already visibile.  The PROPERTY SHEET icon is located in the TOOLS group of the DESIGN ribbon.
  3. Select the new text box by clicking on it.
  4. Click the OTHER tab of the PROPERTY SHEET, and change the NAME property to txtWeekCommencing. (I also suggest you change the names of the first textbox to txtWeekOfYear and the second to txtYear by selecting them and changing the NAME properties).
  5. Click the DATA tab on the PROPERTY SHEET (having first selected txtWeekCommencing).  We are now going to enter an expression which uses our getWeekCommencing custom function.  
  6.  Enter the expression below directly into the CONTROL SOURCE property:
=getWeekCommencing([txtWeekNumber],[txtYear])

The txtWeekNumber parameter references the value in the txtWeekNumber textbox, and the txtYear parameter does the same for the txtYear text box.  

In this exercise, the expression was entered directly into the CONTROL SOURCE
property.  Alternatively, we could have clicked the elipse button at the end of the CONTROL SOURCE
property row to open the EXPRESSION BUILDER.  Using the Expresison Builder is, however, something 
I would need to cover another day, in another post!
And that's it! All you need to do now is save the form and open it in FORM VIEW.  Use the form's navigation buttons to view each record in the tblWeeks table.  The calculated control uses the getWeekCommencing custom function to processes the week number and year data for the current tblWeeks record, and displays the appropriate date.

NB - Just to clarify, the format for the week commencing date in this screenshot is for the UK (ie dd/mm/yyyy). 
The same date using the US format would be 01/07/2019 (mm/dd/yyyy). However, your computer should be set up
to display the date format appropriate for your location automatically.


Saturday, 2 March 2019

Converting Minutes to Hours:Minutes in MS Access

There may be occasions when we need to convert the difference between a start time and an end time, and display the result in hours and minutes.  For example, suppose we set up an Access table field to record the time a person starts work (the session_start field) and another to record when s/he finishes (the session_end) field.  Rather than set up a third field to store the total time worked, we can instead calculate this value automatically using a calculated control in an Access form, or a calculated field in an Access query.

Calculated Control
Above: the bottom text box (txtHoursWorked) is a calculated control. 
The CONTROL SOURCE is an expression based on the values contained in the top two text boxes
(bound to the session_start and session_end fields in the undelying table).

However, to do this we must use two expressions to calculate the time difference, and then display the time difference in the hrs:mm format.  So if we enter 20:40 as the session_start, and 22:06 for session_end, we need an expression to calculate the total minutes worked (which in this case is 86 minutes), and then convert the total minutes to hours/minutes format (which would be 01:26).  To achieve this we use the DateDiff and FormatDateTime functions.  Lets take a look at each function individually before combing them to produce the required result.

The syntax for the DateDiff function is as follows:

DateDiff("interval", time_1, time_2)

The function works by calculating the time difference between the time_1 and time_2 parameters, and returns the result in accordance with the interval parameter.  In our case, we want need the result returned as minutes, so enter "n" as interval.   Time_1 and Time_2 correspond to the start time and end time.  To get these values for the expression, we can we put a reference to the values contained in the text boxes for "Start time" and "End time".  In our example these text boxes are called txtStartTime and txtEndTime.  This what our DateDiff function looks once we add the parameter values:

DateDiff("n", txtStartTime, txtEndTime)

The value returned in our example is 86 minutes.

The second function that we are going to use is FormatDateTime which converts the value returned by the DateDiff function into hours and minutes.  The syntax we need to use in the FormatDateTime is as follows:

FormatDateTime( (minutes/(24*60) , named_format)

This first parameter is a division of the number of minutes (returned in the DiffFunction) by the total number of minutes in a day -  which in our example is 86/(24*60) - and returns the result in accordance with the value contained in the named_format parameter.  The latter is a numeric value indicating the required time format - which in our example is vbShortTime ie hours:minutes.  The numeric value we enter for the vbShortTime format is 4.  This is what our FormatDateTime function looks like once we add the parameter values:

FormatDateTime((86/(24*60) , 4)

The value returned from this expression is 01:26 - ie 1 hour and 26 minutes.

When we come to enter the actual expression used as the control source for the TOTAL (HRS:MINS) text box (referred to as txtHoursWorked in the screenshot below), we need to combine these two functions by nesting the DateDiff function inside the first parameter of the FormatDateTime function. This is done as follows:

FormatDateTime((DateDiff("n", txtStartTime, txtEndTime/(24*60) , 4)

Finally, we just need to enter this expression into the CONTROL SOURCE property of the
txtHoursWorked text box (see screen shot below):

The txtHoursWorked text box is highlighted on the left, and the expression used as the CONTROL SOURCE property value is highlighted on the right.



Friday, 15 February 2019

Creating a Custom Save Button for an MS Access Form.

When we use an MS Access form to create a new record, or update one that already exists, the process of saving that data is normally taken care of automatically "behind the scenes."  All we need to do is move to or create a new record, close the form, or close the database, and Access saves the original record for us.

Record Navigation Buttons: when a record is added or changed, moving to a new or different record 
via the navigation buttons, will save the origin form record automatically.

However, there are times when it may necessary to save a record manually.  Suppose, for example, we have two tables joined together in a one to many relationship. We create a new record on a form bound to a table on the one side of the relationship, and then click a button to open a new form bound to a table on the many side of the relationship.  The newly opened form is then used to create a sub-record for the parent record on the original form.

Parent and sub-forms: In this screenshot, the parent form "Work Session" is bound to the tbl_work_session table, and the "Entry Log" form is bound to the tbl_entry_log table.  There is a one to many relationship between the two tables, with tbl_work_session being on the one side of the relationship.
The problem in this scenario is that when we come to close the form for the sub-record (thereby saving the data to the underlying table on the many side of the relationship), the parent record on the original form has not, at this stage, been inserted into the underlying table on the one side of the relationship.  That is to say, Access has not got around to autosaving the parent record since the parent form has not moved to any other record, or been closed etc - all we have done is click a command button to open a new form.  As such we get an error message saying "You cannot add or change a record because a related record is required in [the parent] table"; this is because the rules of referential integrity have been broken (ie the sub-record in the many table requires a parent record in the one table).

However, it is simple to overcome this issue. We just need to save the parent record manually before trying to close the sub-record form.  A quick and easy way to do this is to click the pencil symbol on the top left of the parent-form, or the SAVE icon in the RECORDS group of the HOME ribbon. This will save the parent record, thereby allowing it's sub-record to be saved afterwards.


 The pencil symbol (above left) can be used to save the current record manually.  Alternatively, the SAVE icon in the RECORDS group of the HOME ribbon (above right) can also be used.
However, this is not particularly useful from the perspective of user-friendliness. That is to say, a user may not realise or understand that this action is necessary.  A better way of doing it would be to save the parent record automatically when the user clicks the command button to open the sub record form. As such, we need to alter the open form button (referred to as "CREATE NEW ENTRY" in our example above) so it also becomes a custom save button.  To do this, we just add the following line of VBA code to the buttons ON CLICK event:

Application.RunCommand acCmdSaveRecord

See the screenshot below for the full code:


VBA code to save the parent record before opening the sub-record form.


Creating a Custom Save Button from Scratch - A Tutorial

Here is a step by step tutorial for creating a custom save button from scratch on Access for Office 365:
  1. Open the form in DESIGN VIEW.
  2. Go to the CONTROLS group of the CREATE ribbon.
  3. Click the MORE button () to the lower right of the control icons, and de-select the USE CONTROL WIZARDS icon when the additional options appear. 
    Above: additonal  options below control icons.
  4. Click the BUTTON control icon () from the CONTROLS ribbon. The cursor will now change to a button icon when it enters the design grid.
  5. Position the cursor at the desired location on the design grid, and click the left hand mouse button.  This will create the new button at the selected location.
  6. Select the new BUTTON contol by left clicking it, and open the PROPERTIES sheet from the TOOLS group of the DESIGN ribbon (if it is not already open).
  7. Click the EVENTS tab on the properties sheet, and left click the ON CLICK row of the events tab.
  8. Click the ELIPSE button (three horizontal dots) which appears on the right hand side of the row.
    Above: the ON CLICK event row on the PROPERTIES sheet.
  9. Select CODE BUILDER from the CHOOSE BUILDER dialog form which opens.  This opens the VBA editor.
  10. Enter the following code in the CLICK sub procedure for the control button.

Above: The CLICK sub procedure for the new control button shown in the VBA editor.
Now when the user clicks the new button at runtime, any data s/he entered will be saved to the corresponding record in the forms underlying table.


Friday, 25 January 2019

Passing Multiple Values via the OpenArgs Parameter of DoCmd.OpenForm

The DoCmd.OpenForm command is a useful tool for the Access for Office 365 database developer wanting to control the manner in which a form opens during runtime.  I have written about it previously in my post on how to "Control Form Opening".  However, in today's post, we are going to focus on a specific area of this command's use - that is, how we can pass multiple values via its OpenArgs property.





The OpenArgs parameter of DoCmd.OpenForm gives us the means to pass a single string value to the OpenArgs property of new form being opened.  After the form has opened, this value can then be read and used by any code able to reference the new forms OpenArgs property. As things stand, the limitation of this is that we can only pass one string value as the command executes.  So let's see how we can get around this, and pass any number of strings values in one go.

To demonstrate how this works, I have created an Access for Office 365 form containing three text boxes and a submit button.  When the user populates the text boxes and clicks SUBMIT during runtime, the values contained in each of the three text boxes are passed over to a new form by means of the OpenArgs parameter of the DoCmd.OpenForm command.

Above: Form to pass multiple values via the OpenArgs parameter of DoCmd.OpenForm.

You can download the full OpenArgsPost.accdb database used in the this exercise from here. 

(NB I have tested the download process using the Chrome browser which works OK.  The download process may fail using other browsers such as Microsoft Edge. If you experience issues please try using a different browser). 



Part One: Passing the Values

Let's look at what happens behind the scenes when the SUBMIT button is clicked.  First of all, clicking SUBMIT (referred to as btnSubmit in DESIGN VIEW) results in the control button's ON CLICK event firing.  This, in turn, starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnSubmit.


The code begins by declaring a string variable called strOpenArgs:

Dim strOpenArgs AS String

The next two lines of code reference the value entered by the user in the first text box.  The value is stored in the strOpenArgs string:

txtValue1.SetFocus
strOpenArgs = txtValue1.Text


The next two lines of code after that reference the value of the second text box, and append it to the strOpenArgs string preceded by a semi-colon (the significance of the semi-colon will be explained in part 3 of this post):

txtValue2.SetFocus
strOpenArgs = strOpenArgs + ";" + txtValue2.Text


This is then repeated for the third text box:

txtValue3.SetFocus
strOpenArgs = strOpenArgs + ";" + txtValue3.Text


So at this point in the program flow, the value of strOpenArgs is as follows:

"one;two;three"

This is the final value of the DoCmd.OpenForm OpenArgs parameter which will be passed to the new form in the following line of code:

DoCmd.OpenForm "frmReceiveOpenArgs", , , , , , strOpenArgs



Part Two: Retrieving the Values

Let's take a look at the new form which opens after the SUBMIT button has been clicked, and the event procedure explained above has finished executing:

Above: the new form which opens in response to clicking the SUBMIT button.

As you can see in the screenshot above, the new form consists of three empty text boxes (for displaying retrieved values) and a RETRIEVE button.  When the user clicks RETRIEVE the text boxes will be populated with the values submitted from the first form.  In our case, Retrieved Value 1 will display the value "one", Retrieved Value 2 will display the value "two", and Retrieved Value 3 will display the value "three".

Let's look at what happens behind the scenes after the RETRIEVE button is clicked.  Like the first form, clicking the command button, which in this form is called btnRetrieve, results in it's ON CLICK event firing.  This starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnRetrieve.

The code begins by declaring a string variable called strOpenArgs before referencing the forms OpenArgs property and storing its value in the string.  (Just to recap, the value of the OpenArgs property (ie "one;two;three") was determined by the OpenArgs parameter of the  DoCmd.OpenForm command invoked when the user clicked the SUBMIT button on the previous form):

dim strOpenArgs As String
strOpenArgs = Me.OpenArgs

The next couple of lines of code are very important. A string array called strValues is declared and has its elements populated with data returned from a function called getOpenArgsArray.  This function is located in a separate module called Utilities which is global in scope.  This is where the core of the processing is done for this exercise:

Dim strValues() As String
strValues = getOpenArgsArray(strOpenArgs)


The purpose of the getOpenArgsArray is to split the value of its parameter, strOpenArgs (which in our case is "one;two;three"), into three separate strings, with each array element containing an individual string.  So for us, the strValues array is populated with the following values:

Array ElementValue
strValues(0)"one"
strValues(1)"two"
strValues(2)"three"

These values are assigned to each of the three text boxes in the next six lines of code:

Me!txtRetValue1.SetFocus
Me!txtRetValue1.Text = strValues(0)

Me!txtRetValue2.SetFocus
Me!txtRetValue2.Text = strValues(1)


Me!txtRetValue3.SetFocus
Me!txtRetValue3.Text = strValues(2)


The end result can be seen in the screenshot below:

Above: the end result after the user clicks RETRIEVE.

In this way, we have used the DoCmd.OpenForm OpenArgs parameter to pass multiple values to a new form, thereby overcoming the limitation of only being able to pass a single string within the statement.



Part Three: The getOpenArgsArray Function

However, this post would not be complete without an explanation of the getOpenArgsArray function, as this is where the core of the processing was done for this exercise.  Let's take a look at the code used in the getOpenArgsArray function:

Above: code for the getOpenArgsArray function.
NB: in this screenshot the strArgs array is declared with 7 elements.
This is because the function is global in scope and other event procedures using
 the function may need addtional elements.  This means that the OpenArgs
 parameter of DoCmd.OpenForm can pass up to 7 values with this current setup.
If more elements are needed, the array can be declared with an adjusted number as necessary.

As mentioned in section 2 above, the purpose of the getOpenArgsArray function is to split the value of the strOpenArgs parameter passed to the function, into individual sections, and to save each section as a separate array element. So in our case, the function receives a string containing the value "one;two;three", and returns an array with three elements ie 


Array Element    
 Value  
Element 1 
 "one"
Element 2
 "two"
Element 3
 "three"

This is acheived by iterating through the strOpenArgs string in a FOR NEXT loop and appending each individual character to a temporary string called strBuildString.  Then when the current character of strOpenArgs in the loop is one of the semi-colons, the value of strBuildString is saved to a new array element. 

Lets break this down further.

The following three lines of code are concerned with iterating through each  character contained in the strOpenArgs string.  Each character is temporarily stored in a string variable called strChr:

For i = 1 To Len(strOpenArgs)
    Dim strChr As String
    strChr = Mid(strOpenArgs, i, 1)


The value of strChr is then checked to see whether or not it is a semi-colon.  If it is NOT a semi-colon, the character is appended to the strBuildString variable :

    If Not strChr = ";" Then
          strBuildString = strBuildString + strChr

If, on the other hand, the character IS a semi-colon, the current value of strBuildString is saved as a  new array element in strArgs().  The strBuildString variable is then cleared ready to build the value of the next array element:

         Else    
             strArgs(argCounter) = strBuildString

             strBuildString = ""

The element number of the array is kept track of in an integer variable called argCounter.  This is incremented by one after the strBuildString variable is cleared:

              argCounter = argCounter + 1
     End If


Next i

After the last iteration of strOpenArgs has completed, the final array element is saved, and the return value of the getOpenArgsArray function is set to the value of the strArgs() array:

strArgs(argCounter) = strBuildString
getOpenArgsArray = strArgs

Exit Function




Friday, 4 January 2019

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.

Thursday, 27 December 2018

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.

Formatted Access 365 text box
Above: notice the text box for the task field
 is in a larger font with bold text. 
Access 365 text box properties
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. 

Access 365 Rich Text Formatting Example

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.