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.

Enable and Disable a Form Control using VBA

Earlier this week I was working on a design for an Order Management Database, and one of the tasks I dealt with involved dynamically Enabling or Disabling one of the form's Command Buttons' using VBA.  This gave me the idea for the present Access tip.

The command button was located on a Customer Details form which had an Orders Subform in the lower section.  The reason I wanted to Enable or Disable the Command Button (located on the parent form), was that it was used to Delete the selected record highlighted in the Orders Subform's datasheet. I needed to get the Delete Button to enforce the business rule whereby once an order has been confirmed, it should not be deleted. As such, if the user highlighted a confirmed order in the subform, the Delete button is dynamically disabled, and vice versa.

To do this I made use of the Order Subforms ON CURRENT event.  This event is triggered whenever the focus moves from one record to another, or when the first record receives the focus as the form opens.  As such, if a user selects a record in the subform datasheet by clicking on one of the rows, the subform's ON CURRENT event fires.  This is the code I wrote to determine whether the Delete button should be Enabled or Disabled.

If IsNull(DLookup("OrderConfirmed", "tblOrders", "OrderId = " & Me!OrderId)) = False Then

        Forms!frmcustomer!ctlDeleteOrder.Enabled = False

Else

        Forms!frmcustomer!ctlDeleteOrder.Enabled = True

End If

As you can see, I have used an If ... Then ... Else Statement to determine whether or not the Order has been confirmed.  In order to create the conditional expression, I used the IsNull and DLookUp functions together to see if  the OrderConfirmed field of tblOrders contained a date. The IsNull function returns a boolean value, True or False, to indicate whether its parameter (in this case the result of a DLookUp function) is or is not null;  and the DLookUp function, looks up the value contained in the OrderConfirmed field of tblOrders where OrderId matches that of the current record displayed on the Orders subform.  

Since the presence of a date in the OrderConfirmed field indicates that the order has been confirmed, the IsNull function returning the value of FALSE (remember this is a double negative!), tells us the order has indeed been confirmed, and vice versa.   As such, when the condition of the first line of the If Statement is False, the Delete Command Button on the main form should be disabled.  This is done by referencing the Delete Command Button's ENABLED property, and setting it to FALSE:

 Forms!frmcustomer!ctlDeleteOrder.Enabled = False

And if the result of the If ... Then ... Else Statement had returned TRUE, the Delete Command Button is Enabled by setting it's ENABLED property to TRUE:

Forms!frmcustomer!ctlDeleteOrder.Enabled = True

Learning Access VBA - A Beginners Guide

This post is all about getting started with the Access VBA programming language -  a first tutorial starting right at the beginning.  As you may already know, the Access Database comes with its own programming language called Visual Basic for Applications, or VBA.  It offers a powerful way to automate and generally control how an access database application operates.  However, whilst relatively easy to use, getting started can seem a little daunting to the newcomer.  As such, this tutorial is intended to help the beginner feel at home in the programming environment.

Figure 1: The Visual Basic Icon

The first problem faced by the beginner is finding out how to get into this thing called VBA.  This is because, technically, VBA is a separate software package which works alongside Access.  That said, we can only open the VBA Editor (see figure 2 below) from within the Access database itself.  The most direct way of doing this is by clicking the VISUAL BASIC icon (located in the MACRO's group of the DATABASE TOOLS ribbon - see figure one above).  You might like to download the Learning Visual Basic Sample Database, and give this a try.

Learning Visual Basic Sample Database

Figure 2: The VBA Editor

The sample database consists of an unbound form which acts as a simple calculator.  There are three Text Boxes and two Command Buttons.  The user enters two numbers in the top two text boxes and clicks the  equals (=) buton.  This runs a block of VBA code (called a sub procedure) which reads the numbers from the two text boxes, adds them together, and displays the result in the third text box.  The user can then click the Clear button (C). This runs a different block of code to clear all the existing numbers from the text boxes so new numbers can be entered and calculated.

Figure 4: The Simple Calculator form
described in the paragraph above.

So lets take a look at how this is created.  We shall do so in two stages: first we shall create the form, then open the VBA Editor and enter the two blocks of code.

Creating the Form

1. Click the FORM DESIGN icon (located in the FORMS group of the CREATE ribbon).
2. Drag and drop three text boxes on the the form (if you need help, you might like to check out my tutorial on how to Create an Access Form from Scratch).
3. Next drag and drop the two Command Buttons onto the design grid.  Make sure the USE COMMAND WIZARDS icon is not highlighted when you do this - otherwise just click the CANCEL button if the Command Button Wizard opens.
4. Click on the label for the top text box and change the text to read "First Number".
5. Repeat step 4 for the second and third labels, entering "Second Number" and "Result" respectively.
6. Then click on the top text button and change the text to "C". This will be the Clear Button.
7. Do the same for the lower text button, changing the text to "=".  This will be the Equals Button.
8. Next click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).
9. Set the NAME property (located at the top of the OTHER TAB of the PROPERTY SHEET) for the first text box to txtFirstNumber.  
10. Repeat step 9, calling the lower two text boxes txtSecondNumber and txtResult, and calling the Command Buttons ctlClear and ctlAdd. 

NB It is important to enter the NAME properties correctly.  Although we could have called them anything we wanted, our VBA code refers to these form controls via the NAME property.  As such we need to ensure that the names used on our form are going to match those to be used in the code.  This is an important principle to grasp as we use VBA alongside Access.

Entering the VBA Code

1. Next click on the ctlAdd (=) Command Button whilst our form is still open in DESIGN VIEW.
2. Open the PROPERTIES SHEET (if it is not already open) and click the EVENTS tab ctlAdd.
3. Click the cell for the ON CLICK property.  Then click the three dots button (...) at the right of the cell.  This opens the CHOOSE BUILDER dialog box.
4. Select CODE BUILDER from the menu items, and click OK.  This opens the VBA Editor in the exact location where we are going to enter the code which is triggered when the user clicks the button at runtime (the ON CLICK Event).  It even writes the first and last line of code which makes this a self contained block of code (a Sub Procedure).
5. Enter the code (listed below) between the Private Sub and End Sub lines:

Private Sub ctlAdd_Click()

    Dim varFirstNumber As Double

    Dim varSecondNumber As Double

    Dim varResult As Double

    

    If IsNull(Me!txtFirstNumber) = True Or IsNull(Me!txtSecondNumber) = True Then

        MsgBox "Please Enter Numbers in Both Textboxes", vbInformation, "Missing Number(s)"

        Exit Sub

    End If

    

    varFirstNumber = Me!txtFirstNumber

    varSecondNumber = Me!txtSecondNumber

    

    varResult = varFirstNumber + varSecondNumber

    

    Me!txtResult = varResult

        

End Sub

Now repeat all of the last stage from 1 to 5, but this time for the ctlClear Command Button.  Enter the following code in the same manner as above:

Private Sub ctlClear_Click()

    Me!txtFirstNumber = Null

    Me!txtSecondNumber = Null

    Me!txtResult = Null

    

End Sub

When this is complete, you may save your code by clicking the SAVE icon, and close the VBA Editor.  To test the code simply open the form in FORM VIEW, enter the numbers to calculate, and click the ctlAdd button.  The correct result should display in the third text box once this is done.

How the Code Works


Please don't worry about being able to create this code yourself at this stage in the learning process.  The main purpose of this tutorial was to get you familiar with the programming environment, rather than how to construct the code.  I intend to do this in future tutorials.  However, we will go through each line of the first sub procedure just to give you a general idea of how the programming code works.

The code is triggered by the Command Button's ON CLICK event.  The user clicks the command button, Access 'raises' the ON CLICK event, and the block of code runs one line at a time.

Private Sub ctlAdd_Click()
The first line, the SUB Statement, was created automatically by Access when we opened the VBA Editor from the property sheet.  It's purpose is to mark the beginning of this block of code or Sub Procedure.  The PRIVATE part of the statement relates to the concept of Scope.  We won't worry too much about what this means at this stage, except to say it is concerned with where the sub can be 'called' from.  Private means just that - it is private to this particular form module, and can not be called from a module elsewhere in the application.  The part which says ctlAdd_Click is the name of the sub procedure. 

Dim varFirstNumber As Double

Dim varSecondNumber As Double

Dim varResult As Double
The next three lines of code declare three separate variables.  The purpose of variables are to enable values to be temporarily stored, processed, and retrieved.  More about this soon. At this stage we are just going to note that we have used the DIM Statement to define three variables called varFirstNumber, varSecondNumber and varResult.  We have also included AS DOUBLE to tell Access that the variables are going to store values of the DOUBLE data type (so we can calculate decimal numbers). 

 If IsNull(Me!txtFirstNumber) = True Or IsNull(Me!txtSecondNumber) = True Then

     MsgBox "Please Enter Numbers in Both Textboxes", vbInformation, "Missing Number(s)"

     Exit Sub

 End If
This next section of code is an IF Statement, and  forms a self contained block of code within the sub procedure itself.  It was something I added to ensure the user enters a number in each of the two text boxes. If it detects that there is a Null value it runs the nested code to display a message box, and then exits the sub. 

varFirstNumber = Me!txtFirstNumber

varSecondNumber = Me!txtSecondNumber

In this section of code, we are assigning each of the two variables with a value.  The equals sign assigns the variable name on the left of the sign with the value on the right.  In this case the top line is setting the value of the varFirstNumber variable to the value entered by the user in the txtFirstNumber text box control.  Note that the text box is referenced by means of the code: Me!txtFirstNumber.  The Me! part of this is telling access that the text box belongs to the form to which this VBA module is attached ie frmCalculator; and txtFirstNumber is the name we gave to the top text box on the form we created earlier.

varResult = varFirstNumber + varSecondNumber
This line is assigning the variable varResult with a value.  In this case the value being assigned is the sum of the values stored in varFirstNumber and varSecondNumber.  Put another way, this line is performing the addition calculation and storing the result in the variable varResult.

Me!txtResult = varResult
This line is using the value stored in the varResult variable to set the value to be displayed in the lower text box of the form, txtResult.  It references the text box in the manner already described above.  This time, however, the text box reference is on the right hand side of the equals sign, and the variable on the left.  This is because the value of the variable is being retrieved, and the value of the text box is being set.


End Sub
This line was generated automatically just like the first in this block of code.  However, this time it is marking the end of the sub procedure.


Try this Yourself
If you are feeling adventurous, you might like to try creating more Command Buttons for the Calculator form.  Try creating a button to multiply or subtract the values stored in the top two text boxes.  You can follow the same procedure as before.  However, when you copy and paste the code, change the addition operator (+) in the appropriate line for multiply (*) and subtract (-) respectively.

Filtering Report Results Using the Filter Property

In this tip we are going to filter the results of a Report to display records with order amounts above a given value.  In so doing we are going to pay special attention to two Report properties: these are FILTER, and FILTER ON LOAD.

Our solution works when the user opens a Dialog Form containing a text box and command button.  The user enters an amount value in the text box, and clicks the command button.  The command button then runs a small amount of VBA code to open the report.  As the report loads, it applies a filter defined in the Report's Filter Property to produce the desired result.

Figure 1: Dialog Form to collect the Amount Parameter.

So how does this work?

When the Report opens, Access checks the report's FILTER ON LOAD property (which we have set to YES). When it detects the property is set to YES, this tells Access to apply the report's Filter (which we have entered into the report's FILTER property). Our filter then references the users parameter value from the Dialog Form text box, and filters all records with Order Amounts greater than or equal to that value.

You can download the completed solution from here: Filter Property Tip Database. You may also want to work your way through the instructions below.

The Table

The solution contains a Table (tblOrders), a Dialog Form (frmParameter) and a Report (rptOrders).  In the screenshot, below you will see the data we shall be working with in tblOrders:

Figure 2: tblOrders.

As you can see the table is made up of four fields: OrderId (AutoNumber), OrderDate (Date/Time), Customer (Text), and Total (Currency).  

The Dialog Form

We then create the dialog form shown in Figure 1 above (there is more information about Dialog Forms in my last post).  I have referred to the textbox as txtAmount, the control button as cmdOpenReport, and the form itself as frmParameter.

As I have already mentioned, the command button runs a small amount of VBA Code when clicked by the user.  To add this code:

1. Select the command button on the form design grid, and click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).  
2. When the PROPERTY SHEET opens, click the EVENT TAB.  
3. Then select the ON CLICK event by clicking into that particular cell of the grid.  
4. A three dot  symbol appears at the right edge of the cell. Click it and select CODE BUILDER from the CHOOSE BUILDER dialog box which has opened.  
5. Then click the OK button and the VBA editor opens.

Figure 3: The On Click Event on
the PROPERTIES SHEET.

You can can copy the code below and paste it onto the editor.  The top and bottom lines that begin Private Sub... and End Sub respectively should have appeared already, so you just need to copy the middle section in between:

---

Private Sub cmdOpenReport_Click()

On Error GoTo MyError

    DoCmd.OpenReport "rptOrdersFiltered", acViewReport

    DoCmd.Close acForm, Me.Name

Leave:

    Exit Sub

    

MyError:

    MsgBox "Error " &  Err.Number &  ": " &  Error$

    Resume Leave

End Sub

---

The two important lines in this section of code begin with DoCmd (meaning do command).  The first of these is the command to open the form in Report View.  The second closes the Dialog form once the report has opened.  NB it is very important the report opens before  the Dialog Form closes, because it needs to reference the value contained in the Dialog Form's Textbox as the it loads.  

The Report

The quickest way to create the Report is to base it on our table.

1. Select tblOrders in the Navigation Pane.  It will highlight in orange.
2. Then select  the CREATE ribbon , and click the REPORT icon (located in the REPORTS GROUP). 

This creates a basic report which we can then then modify in DESIGN VIEW as required. 

Figure 4: Report Design.

Setting the Filter Properties

Next we turn our attention to setting the Report's Filter Properties accessed via its Property sheet.

1. Click the Square Box in the top right hand corner of the Report Design Grid.  This ensures we are going to be working the properties of the actual Form (rather than one of its controls).
2. Click on the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon) whilst the Report is open in Design View.
3. Click the DATA TAB of the pane.
4. Make sure the SELECTION TYPE is set to REPORT.  This should be the case if you carried out step 1 successfully.  Otherwise just change the selection to Form in the drop down list.
5. Enter the following into the FILTER property cell: Total >= forms![frmParameter]![txtAmount] .  This has the effect of filtering out report records where the amount value in the Total field is greater or equal to that entered by the user in the Dialog Form's textbox.
6. Then set the FILTER ON LOAD PROPERTY to YES.  This ensures the filter is applied when the forms loads.
7. Save the Report as rptOrdersFiltered.
8. Close the Report

Figure 5: The Form's Filter Properties.  Note the reference to the textbox
on our Dialog Form in the FILTER property cell.

The solution should now be ready to run.  Just click the frmParameter form in the Navigation Pane to begin.  The screenshot below shows what the report results look like when £50,000 is entered by the user as the amount parameter: 

Figure 6: Report Results filtered for amounts greater than or equal to £50,000.