Form Data Validation

If you ever need to make sure the user entered valid information on the form before committing the data into the table, then it is considered "best practice" to perform data validation routines using the Form's BeforeUpdate event. In addition, it is common practice to use the Tag property of form controls to indicate which data needs to be validated.

For example, let us say you have a form for a loan application and a complete section must be filled out for a co-borrower information when the customer is applying for a joint loan. Using the Tag property approach, you can enter "co-borrower" in the Tag property of each Textbox representing co-borrower information and then use the following code in the Form's BeforeUpdate event:

If Me.LoanType = "joint" Then
   For Each ctl In Me.Controls
      If ctl.Tag = "co-borrower" Then
         If IsNull(ctl.Value) Then
            Cancel = True
            MsgBox "Please complete co-borrower information.", vbInformation, "Required"
            Exit For
         End If
      End If
   Next
End If

As already mentioned, this approach is common practice and usually applies to probably 99% of situations for data validations. However, someone approached me recently and said this approach of using the Tag property and looping through all the form controls made his form ran very slowly. It turns out his form was very busy with multiple subforms and a lot of unbound controls updated using code to reflect specific information about the current record from other tables in the database.

So, in this case, we want to avoid looping through all form controls, which is a requirement when using the Tag property approach. To do so, we'll need to know the names of the controls to evaluate, so we can simply validate them. One of the main advantages of using the Tag property is it is very easy to add or remove controls to validate without needing to modify the code. If we hard code the names of the controls to validate, we'll need to modify the code each time our requirements change.

As an alternative, we need to somehow store the names of the controls to validate and then go through each of them to check the form's data. There are several options to accomplish this task. For example, we can use a hidden unbound Textbox to store a comma-delimited value of control names to validate, We can then use the Split() function to store the control names in an array for validation.

The approach I will demonstrate will use a hidden unbound Listbox to store the control names. I like using a Listbox because it is very easy to loop through each item in a Listbox when performing our data validation. For this approach, I recommend using a Value List for a Row Source, although using a table will work just fine as well. Assuming the Bound Column of the Listbox is set to the column with the names of the control, here's a modified version of the above code using a Listbox rather than the Tag property:

If Me.LoanType = "joint" Then
   For lngItem = 0 To Me.ListboxName.ListCount - 1
      If IsNull(Me.Controls(Me.ListboxName.ItemData(lngItem))) Then
         Cancel = True
         MsgBox "Please complete co-borrower information.", vbInformation, "Required"
         Exit For
      End If
   Next
End If

As you can see, this alternative approach accomplishes the same task as using the Tag property without affecting the speed of a very busy form. Adding or removing controls to validate is also as simple as updating the Listbox without needing to alter the code. This approach can even be expanded by storing all control names in a table along with the name of the form or forms to which the control validation applies. Doing so will allow the same code to validate multiple forms.

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statement, so here goes...

Delimiters

Before we start building a string in code, I think it's important to understand the concept of "delimiters" first. When you declare a String variable in VBA, the value you assign to it must be delimited with a double-quote character. For example:

strVariableName = "Some String Value"

That should be easy enough to understand, but the problem comes when building a string while concatenating field values into the string as well. Table fields have different data types and some data types need a specific delimiter when using them in code. The most common data types and their corresponding delimiters are as follows:

Data Type	Delimiter
Text	Double-quote (") or Single-quote (')
Date/Time	Octothorpe (#)

Here are some examples of their uses:

rs!CompanyName = "UtterAccess"

rs!CompanyName = 'UtterAccess'

rs!VisitDate = #4/29/2014#

rs!VisitTime = #10:30 AM#

As you can probably imagine, the problem we will try to tackle in this article is in how to build the string with Text data types within it since both String variables and Text data types use the same delimiters that usually causes some confusion during coding.

Building the string one step at a time

We are going to discuss an approach that was presented by another UtterAccess member when tackling this issue. First, let's look at the final version of the string we're trying to build. The goal will be to write code that will result in a string value that looks like this:

INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES ("O'Hara", "Scarlett", 1936, #4/28/1861#)

In our code, we will assume that each value to be inserted into the table are held in variables called strCustLastName, strCustFirstName, lngCustNo, and dteDOB. We will then insert these variables in their proper places to replace the hard values we used above.  Let's get started:

Step 1 - Naturally, the first thing to do is to delimit the whole value because it is a string, so let's add a double-quote at the beginning and at the end of our string:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES ("O'Hara", "Scarlett", 1936, #4/28/1861#)"

Step 2 - Next, we will replace the  hard values with the variables that represent each hard value:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (strCustLastName, strCustFirstName, lngCustNo, dteDOB)"

Step 3 - Now, the variables must be concatenated into the string so that VBA will then replace them with the values they represent when the code executes:

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (" & strCustLastName & "," & strCustFirstName & ","
& lngCustNo & "," & dteDOB & ")"

Notice how we enclosed each segment of the concatenation with the double-quote delimiters because we are now trying to combine multiple strings together.

Step 4 - At this point, we'll need to add the proper delimiters for each data type for our variables. Remember that strCustLastName and strCustFirstName variables are for the CustLastName and CustFirstName fields respectively, which are Text fields and therefore need a double or a single quote for its delimiters. The lngCustNo variable represents the value for the CustNo field, which is a Number data type and therefore does not need any delimiter. And, the dteDOB variable is for a Date/Time field, which requires the octothorpe (hash mark):

"INSERT INTO tblCustomers (CustLastName, CustFirstName, CustNo, DOB)
VALUES (""" & strCustLastName & """,""" & strCustFirstName & ""","
& lngCustNo & ",#" & dteDOB & "#)"

Notice how we actually used two double-quotes to delimit the Text data types (strCustLastName and strCustFirstName)? Now would be a good time to discuss some possible issues with Text data types when concatenating them into string values. If the hard values we expect will never contain a single-quote within it, then we could have just use a single-quote as delimiters and be done with it. However, remember that for our example, the strCustLastName variable actually represents the hard value "O'Hara." So, using a single-quote to delimit that variable will fail because the hard value also contains a single-quote. So to avoid that problem, we need to use a double-quote as a delimieter. But why use two of them? Well, if we used just one double-quote, then VBA will interpret it as the delimiter for our string (the whole thing that we're building) instead of just for the variable. So, we used two double-quotes to tell VBA to replace it with one double-quote when it interprets the code during execution.

Step 5 - Actually, that's it. We're done! There's no Step 5. However, one last item that I need to point out is that I didn't use any "continuation" character in the above code examples to reduce any confusion while learning the basic concept of building the string in code. So, the above code samples should be considered as one long line even if they were displayed as multiple lines above. If you wanted to break them down into multiple lines in your code using the continuation character, it might look something like this:

strSQL = "INSERT INTO tblCustomers (CustLastName, CustFirstName, " _
               & " CustNo, DOB) VALUES (""" & strCustLastName _
               & """,""" & strCustFirstName & """," & lngCustNo _
               & ",#" & dteDOB & "#)"

If you want to see the UtterAccess thread that inspired this article, click here. As usual, thanks for reading, and I would appreciate any comments or feedbacks.

Original post date: April 28, 2014

Text or Hyperlink

This is a topic of discussion that I have participated in the forums a number of times in the past and will probably continue to engage in the future. I have decided to write an article to summarize my opinions on this subject. Essentially, the dilemma is whether to use a Text or a Hyperlink field to store a "link" to a file or folder or website, etc.

First of all, let me clarify that I am not exclusively in one camp or the other. Rather, this article just attempts to bring up points and counterpoints regarding the advantages and disadvantages of using one or the other in your database.

Using a Hyperlink Field

When designing a table in Access, using a Hyperlink field may be the easiest choice when trying to store "link" data. For example, let's say you are creating a table for clients and you want to include a field that points to a network folder where you store all the documents relating to each particular client. Using a hyperlink field makes this easy because it comes with built-in functionalities that alleviates the developer from writing code to make it work. For example, by right-clicking on the hyperlink field and selecting Hyperlink > Edit Hyperlink, the user is presented with a dialog box for building the hyperlink. For our scenario, the user can navigate to the folder location and then click 'OK' to store the link to that folder in the table. Once the link is stored in the table, the user can simply click on the field to open a folder window to gain access to all the documents for that client.

Advantages

The main advantage of using a hyperlink field is its ease of use and creation. The hyperlink builder is very intuitive and Access does all the work for you. There's no need to write any code to open the folder window or email client or whatever the program the link requires. It just works!
Another huge advantage of using a hyperlink field is that it can store "any" link. That means you can have one field that can store links to a file or folder path, a website, an email address, an FTP site, etc. Access should reconize the protocol specified in the hyperlink and then execute the appropriate program to open the link. This again would be a big task for the developer to determine the appropriate action to take and program to execute based on the protocol embedded in the link.
The last advantage of using a hyperlink field is that you can "display" a short text in place of the actual link to help the user understand better what the link is pointing to. For example, instead of seeing the full path to the folder such as \\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals, you can simply display something like "Client Proposals." Clicking on the link will automatically take the user to the correct folder path.

Disadvantages

Unfortunately, that last advantage of the hyperlink field can also be considered as a disadvantage by other developers. For anyone not familiar with how hyperlink fields are stored in the table, it actually has three components separated by hash marks: the display text, the hyperlink address, and the hyperlink subaddress. So, in the above example, the hyperlink address is the full folder path, and the display text is "Client Proposals," which is all the users see when looking at the data.
The problem appears when the developer starts to use the value in the hyperlink field in code. For example, if we add an unbound textbox on a form bound to a table with the hyperlink field and use the following as its Control Source: =[HyperlinkFieldName], you will see the actual value stored in the table instead of just the hyperlink address, which is what we would normally want. For the above example, the unbound textbox will display the following value:

Client Proposals#\\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals#

Notice the hash tags separating the other components of the hyperlink field? If the user sees that, it could cause some confusion. Also, grabbing the hyperlink address using code by just referencing the field's value could cause unexpected results. For this reason, some developers prefer to use a text field instead.

Using a Text Field

As I have mentioned above, a hyperlink field may have many advantages for beginners but most experienced developers will try to avoid the pitfalls and additional maintenance that may be brought on by using it and opt to use a text field instead. A text field doesn't disguise what is stored in the table and it's easy enough to simulate a hyperlink using a text field.

Advantages

I already mentioned the main advantage of using a text field - what you see is what you get. What is being displayed in the field is exactly what is stored in the table, no surprises. Because of that, the developer is then free to manipulate the data using code. The simplest approach to simulate a hyperlink using a text field is by using the following code:

Application.FollowHyperlink HyperlinkFieldName

Disadvantages

This may not matter to seasoned developers but the first disadvantage of using a text field to simulate a hyperlink is the need to use code. As you can see above, it's not really hard to do that. However, if you can remember one of the advantages of a hyperlink field, if there's a situation where the "link" data stores various types of protocols, then the code to execute the hyperlink can become vastly complicated. For example, simply using the FollowHyperlink method when the link only contains the following data will not work because it is missing the proper protocol: thedbguy.blog.com. The developer may have to include additional logic to the code to assign and use the appropriate protocol for the link in question. These protocols might include types such as ftp, news, gopher, etc.

Conclusion and Disclaimer

Understanding that a hyperlink field really contains three components helps the developer get a better handle in manipulating the link data. But, if the purpose of the hyperlink field is just to store websites, email addresses, or file/folder paths, then using a text field will probably save you the frustration of dealing with the surprises that comes with a hyperlink field.

As I mentioned in the beginning, I am not really recommending to use one over the other but merely trying to bring up possible arguments that you may hear when you get into a discussion regarding this topic with others.

Oh, and by the way, if you were wondering how to grab just the hyperlink address from a hyperlink field as if it was a text field, you can try the following code:

Split(PlainText(HyperlinkFieldName),"#")(1)

As usual, any comments are welcome. Cheers!

Original post date: March 28, 2014

Event-Driven Programming

This topic may seem to come from out-of-nowhere, but I promised a member at UtterAccess that I will elaborate on this subject from our discussions in the forums.

What is Event-Driven Programming?

In a nutshell, event-driven programming (EDP) is a coding methodology where the flow of the program is determined by "events." It is a different approach than procedural programming where the flow of the program is dictated by the steps specified as written in the program and object-oriented programming (OOP) where the flow of the program relies on the interaction between objects and their properties and methods.
Now, it so happens that the majority of Access VBA programming is an example of EDP (it also has some aspects of OOP mixed in), and that's what this topic is all about. I just wanted to describe how Access uses EDP to help understand why we have to do certain things when writing our VBA procedures.

EDP Components

Event-driven programming requires three components:

1. Event Handler
2. Event Binding (or Wiring)
3. Main Loop

Event Handler
The first step in developing an event-driven program is to write a series of subroutines or procedures that will be executed once an event occurs. Some examples of events that can occur in a Graphical User Interface (GUI) application are clicking on a command button, opening a form, and entering data in a textbox. Access allows three types of code as event handlers as seen in figure 1.

Figure 1. Choose Builder



 Access event  handlers can be a macro (saved or embedded), an expression (usually a function call), or a procedure. As with many programming environments, Access also provides an event template when using the Code Builder so that all you have to do is write the code for the event. Here's an example of an event procedure using the template that Access provided:

Private Sub txtDateReported_BeforeUpdate(Cancel As Integer)
   If Me.txtDateReported > Date Then    
      MsgBox "Employee report date cannot be in the future.", _
         vbInformation, "Invalid!"
      Me.txtDateReported.Undo   
      Cancel = True
   End If
End Sub

Event Binding
After writing the code for handling an event in your program, the next step is to bind it to the specific event that the code is supposed to handle. I prefer to call this process as "wiring" the event. The method of wiring an event to its event handler in Access will depend on which code builder you used for creating the event handler. If you didn't use the Code Builder, you can select a name of a Macro from the dropdown in the event property to wire that event to a saved macro. If you used the Macro Builder, the event property that you are wiring (binding) will have "[Embedded Macro]" displayed. If you used the Expression Builder, the complete expression preceded by an equal sign will be displayed. And lastly, if you used the Code Builder, the event property will display "[Event Procedure]."

It is important to note that "wiring" an event is an important step in making sure the program runs as expected. Even if you have written the event handler to do all the necessary tasks when an event occurs, if it is not wired to that event, then the event handler will never be executed. An event in Access is not wired if the event property is empty. Remember, even if an event handler is present in the code module, it will not be called if the event property is blank.

Main Loop
The final piece of the puzzle is called the "main loop" because it's a function that coninually monitors for events and is responsible for calling the appropriate event handler when an event fires. Fortunately for us, Access already provides this function so we don't have to worry about it.

The main point of this article is to explain that you, as the programmer, can write as many event handlers as you like and has the option to wire any of them to the event you want to handle. And unless you "wire" the event to its event handler, the application may not run as expected. Please note that Access will automatically "wire" the event handlers you've written if you used the "build" button next to each event property. And as was mentioned in the UtterAccess thread, you can also go straight to the VBA Editor window and write a procedure based on an Access event template, and Access will automatically wire that event handler to the appropriate event based on the template you used.

See the UtterAccess discussion that started it all.

Original post date: July 5, 2013

Combobox Wizard

Almost everyone is familiar with the Combobox Wizard but the question that always comes up is why do you sometimes get three options but other times you only get two?

To activate the Combobox Wizard, make sure you have the "Use Control Wizards" option selected in the Ribbon before adding a Combobox to your form.



When you add a Combobox on your form, the Wizard might present you with the following screen:



But sometimes, you might see it like this:



Notice that in the first image, there is a third option as highlighted below:



So, why the difference? The standard answer has always been that the Combobox Wizard will present you the options based on whether the form is bound or not. In other words, if your form has a Record Source, then the Wizard adds the third option as a choice. Otherwise, it's not available if the Record Source is empty.

However, it was brought to my attention recently that the bound or unbound status of the form is only half of the reason whether you get the third option or not from the Wizard, and that is the main reason for this blog. It turns out that even when your form is definitely bound (has a Record Source), the Wizard might still not give you the third option when you try to add a Combobox to your form.

For instance, if you have either of the following Record Source for your form (see images below), then the Wizard will show the third option.



The above images show that the form is bound to a table or a query. But when you modify the record source to a SELECT statement as the one shown in the following image, then the Wizard does not present the third option although the form is clearly bound to a record source.

I'm not sure how the Combobox Wizard works, and this may be a limitation in its design; but if you use SELECT statements in your forms as a Record Source, which I often do, and you use the Combobox Wizard, then you need to be aware about this limitation.

However, just because the Combobox Wizard doesn't provide the third option in some cases, you can still implement that functionality on your form using VBA.

Here's a sample code to navigate the form to the record selected from the Combobox:

Dim rs As Object

Set rs = Me.RecordsetClone

With rs
.FindFirst "CustomerID=" & Me.cboCustomerID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

.Close

End With

Set rs = Nothing

Original post date: May 26, 2013

Database Startup Properties

A recent question in the forums asked how to set certain options for the Current Database through code. Presumably, the purpose is to restrict the user from changing these settings manually when trying to bypass any security measures the developer designed into the database application. Doing a search through the Internet didn't produce a complete list of all possible database option settings, so I decided to put all the pieces of the puzzle together in one place.

So, what exactly are we talking about? Basically, we are interested in all the settings you can find in the Current Database tab when you open the Access Options window (see image below).

Access Options

Application.SetOption

The following options can be set using the Application.SetOption command:

Option	Property	Value
Compact on Close	Auto Compact	False
Remove personal information from file properties on save	Remove Personal Information	True
Use Windows-themed Controls on Forms	Themed Form Controls	True
Enable Layout View	DesignWithData	True
Check for truncated number fields	CheckTruncatedNumFields	True
Preserve source image format	Picture Property Storage Format	0
Convert all picture data to bitmaps	Picture Property Storage Format	1
Track name AutoCorrect info	Track Name AutoCorrect Info	False
Perform name AutoCorrect	Perform Name AutoCorrect	False
Log name AutoCorrect changes	Log Name AutoCorrect Changes	True
Show list of values in: Local indexed fields	Show Values In Indexed	True
Show list of values in: Local nonindexed fields	Show Values In Non-Indexed	True
Show list of values in: ODBC fields	Show Values In Remote	False

CurrentDB.Properties

The following options can be set using the CurrentDB.Properties collection:

Option	Property	Value
Application Title	AppTitle	Text
Application Icon	AppIcon	Text
Display Form	StartupForm	Text
Display Status Bar	StartupShowStatusBar	True
Overlapping Windows	UseMDIMode	1
Tabbed Documents	UseMDIMode	0
Display Document Tabs	ShowDocumentTabs	True
Display Navigation Pane	StartupShowDBWindow	True
Ribbon Name	CustomRibbonID	Text
Shortcut Menu Bar	StartupShortcutMenuBar	Text
Allow Full Menus	AllowFullMenus	True
Allow Default Shortcut Menus	AllowShortcutMenus	True

Examples:

Application.SetOption "Auto Compact", True
CurrentDB.Properties("AllowFullMenus") = False
CurrentDB.Properties.Append CurrentDB.CreateProperty("CustomRibbonID", dbText, "MyRibbon")

Original post date: February 27, 2013