This article will offer a simple solution of how to let the person know what is needed from them on Microsoft Access data entry forms and where it is needed.

Last week I was asked for a way to give the person an on screen message while on Microsoft Access data entry forms without it requiring them to dismiss it by clicking the OK button on a message box. My reply was to have a label change it’s caption property from nothing to “The desired message…” then back to nothing using the form’s built in timer and some OnTimer event code. This article using some code similar to the last posted article.  It addresses two areas of Microsoft Access data entry forms assistance. The first is a visual clue as to where that skinny little cursor is blinking and second, what the user needs to know about the current form control that has cursor focus. The first area was addressed by changing the background color to a light shade of blue as the visual clue. The second area was addressed by having a custom message appear in a label at the top of the form. The messages are stored in the Tag property of each control that requires a message to be displayed by Microsoft Access. Data entry forms might have a message that simply lets the data entry person know that this is a required field or it might supply more about the type of information that should be entered in this field. This message is turned off when the user move to the next control on the form.

Below is a short video demonstrating how this works.

This green colored code below is all you need at the form level. It is placed in the form’s On Open Event.

Private Sub Form_Open(Cancel As Integer)
Me.SetFocus
Call SetEventHandlers(Me)
End Sub

Each form control has a tag property where you can type in the message you want to appear in the ‘lblControlMsg’ label that you will need to place on your form. If you need help finding the tag property see this video.

Place code below in a code module:

Public Sub SetEventHandlers(frm As Form)

‘Runing this routine from the OnOpen event of any form using
‘Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus and OnLostFocus event code
‘with a call to the Highlighter function.
On Error GoTo errline
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
If ctl.Visible = True Then
ctl.OnGotFocus = “=Highlighter(true)”
ctl.OnLostFocus = “=Highlighter(false)”
End If
Case Else
End Select
Next
exitline:
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “Error ” & Err.Number & Chr(10) & Err.Description
GoTo exitline
End Select

End Sub

Public Function Highlighter(TurnOn As Boolean)

On Error GoTo errline
Select Case Screen.ActiveControl.ControlType
Case acTextBox, acComboBox, acListBox
If TurnOn Then
Screen.ActiveControl.BackColor = 16773857
‘requires that the name of the message control be labeled ‘lblControlMsg’
Screen.ActiveForm![lblControlMsg].Caption = Screen.ActiveControl.Tag
Else
Screen.ActiveControl.BackColor = -2147483643
Screen.ActiveForm![lblControlMsg].Caption = “”
End If
Case Else
End Select
exitline:
Exit Function
errline:
Select Case Err.Number
‘Form window not visible yet to see Screen.ActiveControl
‘(Only needed when setting event handlers by code)
Case 2475
Resume exitline
Case Else
MsgBox “Error ” & Err.Number & Chr(10) & Err.Description
GoTo exitline
Resume Next
End Select
End Function

Let me know how this worked for you by entering your comments and suggestions.

How to create Microsoft Access email is a common request. I received the following question a while back and it presented a great opportunity to show how useful Microsoft Access recordsets can be. Microsoft Access recordsets allow you to move through a set of records and use the data contained in them to perform such tasks as emailing, running reports or queries, analyzing data, etc.

The request was: “At the moment I am building a database for … I have a table holding information of the product, user, email address and balance field. I would like to create an automatic email alert to any user who’s balance is under £10 at anytime. I am guessing the solution is probably fairly simple but am really struggling. I have no idea how to do it so any help would be greatly appreciated.”

Here is the solution that I offered:

You can send the email by various methods but to use the built in Access email command that does not require Microsoft Outlook or access to an SMTP mail server just use the DoCmd.SendObject acSendNoObject (default). The acSendNoObject argument allows you to just send email message without sending an Access object such as a Report or Query object.

Just so there can be a date stamp of when the email was sent, first add a new date field called Email_Sent_Date to your table. Next create a query called ‘Email_Reminder_RecordSet’, that has the name and email address of users having a balance under 10 pounds and optionally who have not received an email in the past month or what ever time seems prudent. You will use the new field ‘Email_Sent_Date’ for this purpose and this will allow the following code to enter the date that the email was sent. The code on the on click event of your button will open a recordset on this query and loop through it’s records to send the emails. Here is the code:

‘This code is using DAO (Data Access Objects) for recordset control so be sure DAO is showing under Tools menu Under References in your code module as a ‘referenced code library or select DAO 3.6 to add it if it is not showing already.

If you copy and paste code from this article, Please replace all double quotes that look curly with standard straight looking double quotes.  These “Web Page Smart Quotes”, that have a curved look to them, will not compile in your code editor unless they are replaced with your standard keyboard double quotes.

Here is the code to paste into the OnClick event of the button you will use to trigger this emailing code:

Dim rs as dao.recordset
set rs = currentdb.openrecordset(“Email_Reminder_RecordSet”)
with rs
if .eof and .bof then ‘IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)
Msgbox “ No emails will be sent because there are no records from the query ‘Email_Reminder_RecordSet’ “
else
do until .eof ‘DO UNTIL END OF RECORDSET FILE

‘THE NEXT LINE SENDS THE EMAIL MESSAGE
DoCmd.SendObject acSendNoObject, , , ![Email Address Field], , , “Low Balance Alert”, “Hello ” & ![First Name Field] & _
“, ” & Chr(10) & “You ballance is under 10 pounds. Please…”, False

.edit
![Email_Sent_Date] = now()
.update
.movenext ‘THIS MOVES TO THE NEXT RECORD IN THE RECORDSET
loop

End If
end with

‘CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Please let me know how this works for you by leaving a reply / comment below.

Data validation checks that data are valid, sensible, and reasonable before they are processed. Microsoft Access validation can involve a time consuming choir if one had to do it for each control or ‘access field’, on each of your Access forms. Several have authored various approaches to automate Access validation for you. No single approach will work for all situations. Your form might allow a user to move off the record before Microsoft Access validation code is triggered. If you try to prevent the user from doing so, you will need to create additional design changes and add more complexity to your form.

I chose to take a conservative approach to Access validation, which works for most situations, but with a few more lines of code, will even prevent the user from leaving an unvalidated record no mater where he mouses to.

The user is notified if a form control failed validation, told why it failed, the control is highlighted briefly, and cursor focus is moved back to the failing control. Oh yes, it doesn’t try to validate form controls that come later, in the tab order, than where the user currently is. A single line of programming code is used on the OnOpen event of your form for this Microsoft Access validation of form data. The user can be prevented from navigating off the form record, adding a new record, or closing the form if validation fails, this requires an additional line of code on the appropriate form close, add new record and navigation buttons.

An Access developer just needs to enter a few characters into the ‘TAG’ property of a form’s control. The ‘TAG’ control property stores extra information about a control. The videos below will show you how to find this property in the ‘Property Sheet’ of the control you want to validate and will demonstrate the Access validation of a form using the code found from a link at the end of this article which moves you into the article containing the full programming code. There is no programming involved unless you wish to expand on the supplied code’s functionality.

Access Form Data Validation Demonstration Video:

from youtube

http://www.youtube.com/watch?v=otCMjARPiJY

You may get your example demostration file containing all code for Access 97, 2000, 2002, 2003, 2007 – Get File #51

This is where you will find all the Microsoft Access validation code in next article:

http://dailyaccesstips.com/code-for-microsoft-access-validation-of-data/

How to locate the Access Form Control ‘Tag’ Property for Access 97, 2000, 2002XP, 2003 video:

from youtube
http://www.youtube.com/watch?v=16HVabt2gM4

How to locate the Access Form Control ‘Tag’ Property for 2007 video:

from youtube
http://www.youtube.com/watch?v=hViGDmDU3P4

The following code subroutine called ‘SetValidatorEventHandlers’ is place in the OnOpen Event of every form you want Microsoft Access validation of form data. It will add the call to the Validator function from OnGotFocus Events of all visible text box, combo boxes, and listboxes on your form. If you already are planning to place other code in the OnGotFocus Events of these controls on your form, then you will want to reference the custom validation function call ‘Validator’ in the OnGotFocus subroutine for each control that you will want to trigger this code.

Place in the ‘Tag’ property of each control you want to validate the following symbols and characters:

• *n – The form control may not be left empty.
• *d – The control must contain a valid date.
• *+ – The control must have an amount greater than zero.
• *@ -The control must be a valid email address.

Other types of validation may be added by you as needed. You may may combine these pairs of characters if needed.

If you do not wish to refer to the form’s controls actual name in your validation message to the user, you may specify a preferred alternative name by adding this to the end of the tag property.
~My Preferred Control Name
Replace ‘My Preferred Control Name’ with the control name you want the user to see.

Here’s the code:

Public Sub SetValidatorEventHandlers(frm As Form)

‘Runing this routine from the OnOpen event of any form

‘using Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus event code with a call
‘to the Validator function.
Public Sub SetValidatorEventHandlers(frm As Form)
‘Runing this routine from the OnOpen event of any form
‘using Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus event code with a call
‘to the Validator function.
On Error GoTo errline
Dim ctl As Control, tagstr As String
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
If ctl.Visible = True Then
ctl.OnGotFocus = “=Validator()”
End If
Case acCommandButton
If ctl.Visible = True And Len(ctl.Tag) > 0 Then
Select Case ctl.Tag
Case “*Close”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Close” & Chr(34) & “)”
Case “*First”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*First” & Chr(34) & “)”
Case “*Previous”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Previous” & Chr(34) & “)”
Case “*Next”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Next” & Chr(34) & “)”
Case “*Last”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*Last” & Chr(34) & “)”
Case “*New”
ctl.OnClick = “=CommandButtonCodeActiveForm(” & Chr(34) & “*New” & Chr(34) & “)”
Case Else
End Select
End If

Case Else
End Select
Next
exitline:
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: ” & Chr(10) & “Error Number: ” & Err.Number & Chr(10) & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
Resume Next
End Select
End Sub

Public Function CommandButtonCodeActiveForm(CommandTagAction as String)

On Error GoTo errline
If Validator() = True Then
Select Case CommandTagAction
Case “*Close”
DoCmd.Close acForm, Screen.ActiveForm.Name
Case “*First”
DoCmd.GoToRecord , , acFirst
Case “*Previous”
DoCmd.GoToRecord , , acPrevious
Case “*Next”
DoCmd.GoToRecord , , acNext
Case “*Last”
DoCmd.GoToRecord , , acLast
Case “*New”
DoCmd.GoToRecord , , acNewRec
Case Else
End Select
End If
exitline:
Exit Function
errline:
Select Case Err.Number
Case 2105 ‘Impossible navigation attempt
Resume Next
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: ” _
& Chr(10) & “Error Number: ” & Err.Number & Chr(10) & Err.Description, vbCritical, _
“Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
End Function

Public Function Validator() As Boolean

On Error GoTo errline
Dim TabOrderedControls As New Collection
Dim ControlNames() As String
Dim i As Long
Dim ctl As Control
Dim CurrentControlTabIndex As Long
Dim strFailedCtlName As String
Dim bolFailedValidation As Boolean
Dim strErrorMsg As String
‘Is Form ready to be validated by this microsoft access validation code
If Screen.ActiveForm.NewRecord And Screen.ActiveForm.Dirty = False Then
Exit Function
End If
ReDim ControlNames(Screen.ActiveForm.Controls.Count)
‘Save any pending edits
If Screen.ActiveForm.Dirty Then
Screen.ActiveForm.Dirty = False
End If
‘Where are we in the tab order

‘If pressing a command button, consider we are to validate all tab order controls (Not just ones earlier than tab index of the command button.)
If Screen.ActiveControl.ControlType = acCommandButton Then
CurrentControlTabIndex = 999
Else
CurrentControlTabIndex = Screen.ActiveControl.TabIndex
End If

For Each ctl In Screen.ActiveForm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
‘Set yellow backround to white, if previously set to yellow
ctl.BackColor = -2147483643
‘Populate the ‘ControlNames’ array by control tab order
If ctl.TabIndex < CurrentControlTabIndex Then
ControlNames(ctl.TabIndex) = ctl.Name
End If
Case Else
’skip
End Select
Next
‘Populate collection ‘TabOrderedControls’ using ControlNames array.
For i = 0 To UBound(ControlNames)
If ControlNames(i) <> “” Then
TabOrderedControls.Add Screen.ActiveForm.Controls(ControlNames(i))
End If
Next i
‘Start validation
For Each ctl In TabOrderedControls
”Test for null (empty) on form control
If InStr(1, ctl.Tag, “*n”, 1) > 0 Then
If IsNull(ctl.Value) Then
strErrorMsg = ” needs to be filled in.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘Test for a valid date
If InStr(1, ctl.Tag, “*d”, 1) > 0 Then
If Not IsDate(ctl.Value) Then
strErrorMsg = ” must contain a valid date.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘Test for a value greater than zero
If InStr(1, ctl.Tag, “*+”, 1) > 0 Then
If Not (ctl.Value) > 0 Then
strErrorMsg = ” amount must be greater than zero.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘Test for a valid email address symbol
If InStr(1, ctl.Tag, “*@”, 1) > 0 Then
If Not InStr(1, ctl.Value, “@”, 1) > 0 Then
strErrorMsg = ” amount must be greater than zero.”
bolFailedValidation = True
GoTo ValidatorFalure
End If
End If
‘You may add additional validation here:
ValidatorFalure:
If bolFailedValidation = True Then
ctl.BackColor = 65535 ‘yellow
‘~ The tilde means there is an alternative preferred control name
‘After any tilde ~ there should be no other validation * asterisk symbols
If Not InStr(1, ctl.Tag, “~”, 0) = 0 Then
strFailedCtlName = Trim(Mid(ctl.Tag, InStr(1, ctl.Tag, “~”, vbBinaryCompare) + 1)) ‘Retreive prefered control name if different than real control name.
End If
If Len(strFailedCtlName) = 0 Then
MsgBox “The ” & ctl.Name & strErrorMsg, vbInformation, ctl.Name & ” missing value…”
Else
MsgBox “The ” & strFailedCtlName & strErrorMsg, vbInformation, strFailedCtlName & ” missing value…”
End If
ctl.SetFocus
If ctl.ControlType = acComboBox Then ‘If combo box, drop down the pick list.
ctl.Dropdown
End If
Validator = False
Exit Function
End If
Next ctl
Validator = True
exitline:
If Not TabOrderedControls Is Nothing Then
Set TabOrderedControls = Nothing
End If
Exit Function
errline:
Select Case Err.Number
‘Form window not visible yet to see Screen.ActiveControl
‘(Only needed when setting event handlers by code
‘using the ‘SetValidatorEventHandlers’ function.)
Case 2475
Resume exitline
Case Else
MsgBox “There was an error in the program. Please notify database administrator of the following error: ” _
& Chr(10) & “Error Number: ” & Err.Number & Chr(10) & Err.Description, vbCritical, _
“Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
End Function

Here is the demonstration video tutorial:

from youtube

Here are three questions that have been asked of me regarding how to pass a value from one place to another in MS Access:

1. I have multiple queries and need a form for a date to be entered once. Rather than have to enter the same dates for 16 queries. I would like to set them up with a temporary variable somehow so that each query could read the date and the user would only have to enter the date range once.
2. I have a query and want to establish criteria based on a combo box on a Microsoft Access form. When I leave the form’s combo box blank I want to include everything in the query output otherwise just the item in selected in the combo box.
3. What is the best way for me to get the value of a variable from a subroutine into another subroutine or a query?

I commonly get asked how to pass a value from one location in Microsoft Access to another. In VBA, Visual Basic for Applications, code a value that is passed or referenced later in code is called a variable. If it is referenced later out side of the form or code module were it was first given a value, then we need the variable value to be visible broadly throughout the application. This public variable (called “strFilter ” below), then needs to be placed in a code module that you create in Access. A simple function is then needed to retrieve the value of your public variable that you have declared in that code module you created. At the end of this article you will find a video tutorial demonstrating how to do this. Note that versions of Access 2007 or later have something called “tempvars” which serve this purpose with some additional benefits. You can find an article on this at http://www.vb123.com.au/toolbox/09_access/needforspeed.htm.

Create a new code module where you will declare your public variables and write a function to get them when they are needed.

Declare the public variable in a code module using this single line of code in the declaration area at the top of the code module you just created:

Public qrytesting as Variant

Note that the word “Public” allows this value to be visible from anywhere in the Microsoft Access programming.

Now create the public function in this code module to get the value of the public variable:

‘This function requires the name of the public variable it is to retrieve, in this case “strFilter”

Public Function GetPublicVariable(VariableName As String) As Variant
Select Case VariableName
Case “strFilter”
GetPublicVariable = strFilter
Case Else
MsgBox “You need Variable Name on GetPublicVariable function”
End Select
End Function

You set the public variable like this:
strFilter = [NameOfFormControl]
or
strFilter = some value

or

strFilter = another variable

This code is usually placed in the after update event of a form control.

You will retrieve the value of the set public variable with this line of code:

GetPublicVariable(“strFilter”)

GetPublicVariable(“strFilter”) can be used as the query criteria of the query above called “qrytesting”.

Regarding: “When I leave the form’s combo box blank” you could use this combination in the
Access query grid’s criteria cell:

Like GetPublicVariable(“strFilter”) & “*”
The Like operator will give you anything starting with the value that is currently being held
in the public variable call “strFilter”, however if nothing is stored there, a null value, then Microsoft Access wild card value, the *
asterisk, would be used for retrieving all values.

A simpler function to retrieve the public variable could have been written as seen below, but
it would not have been reusable for other public variables.

Public Function GetStrFilter() as Variant
GetStrFilter = strFilter
End Function

from youtube

I hope this has been useful.  Let me know.

Bob Heifler