Project Perfect
PROJECT  PERFECT
                 Project Management Software
                          Specialists in Project Infrastructure		 Microsoft Access Development Tips

Home - Microsoft Access Development - Microsoft Access Tips

Sample Microsoft Access VBA

Help Screen Function

Download Sample Database

Microsoft Access help usually requires a separate help file be created using one of a number of tools available to create the file. Each text box or combo has to have information in the properties detailing the help file. This help function uses a different approach. It stores the help information in a table within the application. Users can update the help with tips and information themselves. If you do not want them to update the help, change the edit button visible property to not display.

A sample database is available to download. I will outline the key parts of the application below.

Help Table

The table is simple in that there are only three fields.

Table Name: tblHelp
Fields: HelpRef - Text, 255, Indexed Yes(no duplicates). Set as Primary Key
HelpTitle - Text 50
HelpText - Memo

The field HelpRef is a combination of the form and field name separated by a dash. For example if a form frmCustomer had a field called txtSurname, the HelpRef would be frmCustomer-txtSurname.

Forms

In order for the forms to display help, you need to intercept the F1 key before it fires up Access Help. To do this, you change the Form.KeyPreview to "Yes" and use the Form.KeyDown function to change KeyPress to 0. You then call subGetHelp.

Paste the following code into your form KeyDown. Remember to change the form KeyPreview to "Yes".

'---------------------------------------------------------------------------------------
' Procedure : Form_KeyDown
' Author : Neville Turbit
' Date : 30/04/2010
' Purpose : ' All input on the form checks for F1 key. If it is pressed, the code is set to
' zero to avoid Microsoft Help being displayed. The subGetHelp subroutine is called
' which displays the appropriate help information.
'---------------------------------------------------------------------------------------
'
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Error_Form_KeyDown

If KeyCode = vbKeyF1 Then
KeyCode = 0
Call subGetHelp()
End If

Exit_Form_KeyDown:
On Error GoTo 0
Exit Sub

Error_Form_KeyDown:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: Form_frmCustomers" & funCrLf & _
"Type: VBA Document" & funCrLf & _
"Calling Procedure: Form_KeyDown" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_Form_KeyDown
Resume
End Sub

Module modHelp

Create a new module called modHelp and paste the code below into the module.

The code creates a string called strHelp which is a combination of form and field name. This is what is stored in the table as HelpRef. The code uses a few functions such as funIC which is a short function to insert inverted commas. Just lazy I guess playing. Avoids around with lots of inverted commas in a cancatenated string. It also uses funRecordCount which returns the number of records in the recordset. SubOpenForm is just a generic version of the DoCmd.OpenForm command which has some error trapping. All are in a module called modGeneric.

There are three stages. First it searches within tblHelp.HelpRef for a combination of form-field. If it is not found then it then searches for just form. If neither is found, it displays a message that no help exists.

NOTE: You cannot step throught the code as it uses
Screen.ActiveForm.Form.Name & "-" & Screen.ActiveControl.Name.
If the form is not the active screen it will fail. If you do want to step through the code, pause after the line above has processed.

Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : subGetHelp
' Author : Neville Turbit
' Date : 28/04/2010
' Purpose : Looks for F1 key and displays help
'---------------------------------------------------------------------------------------
'
Public Sub subGetHelp()

Dim dbs As Database
Dim rst As Recordset
Dim strHelp As String
Dim strSQL As String
Dim strCriteria As String

On Error GoTo Error_subGetHelp

' Create the combination of form and control name. If the form was frmCustomer and
' the field txtSurname the help table primary key is "frmCustomer-txtSurname".
' If there is no help for that textbox, the program looks for generic help for the
' screen under a primary key of "frmCustomer"
strHelp = Screen.ActiveForm.Form.Name & "-" & Screen.ActiveControl.Name

' Create the SQL query for the form and control
strSQL = "SELECT tblHelp.HelpRef FROM tblHelp" & _
" WHERE tblHelp.HelpRef = " & funIC & strHelp & funIC & ";"

If funRecordCount(strSQL) > 0 Then GoTo LaunchHelp ' Record found so skip the next stage

' If there is no help for the control try the form help (e.g. "frmCustomer")
strHelp = Screen.ActiveForm.Form.Name

' Create the SQL query for the form and control
strSQL = "SELECT tblHelp.HelpRef FROM tblHelp" & _
" WHERE tblHelp.HelpRef = " & funIC & strHelp & funIC & ";"

If funRecordCount(strSQL) > 0 Then GoTo LaunchHelp ' Record found

' Display a message that there is no help available
Call MsgBox("There is no help available for this field or for the form.", _
vbInformation, "No Help Available")
GoTo Exit_subGetHelp

LaunchHelp:
' Create the where clause for the record to display on the form
strCriteria = "[HelpRef] = " & funIC & strHelp & funIC

' Open the form and display the correct help record
subOpenForms "frmHelp", , , strCriteria

Exit_subGetHelp:
On Error GoTo 0
Exit Sub

Error_subGetHelp:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: Form_frmImageMgmt" & funCrLf & _
"Type: VBA Document" & funCrLf & _
"Calling Procedure: subGetHelp" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_subGetHelp
Resume
End Sub

Help Form

The Help form is very straight forward. It displays the help, and has two buttons. One button closes the form. The other button allows you to edit the form. When you select "Edit", the button caption changes to "Lock". Select "Lock" and it changes to "Edit".

When you edit, there is a third button visible. That button allows you to insert bullets in Microsoft Access. Normally if you want bullets you have to use something like a dash. How this works is described in the Project Perfect Blog. We will not cover it again in this article.

The form has the three fields from tblHelp - the reference number (HelpRef) which is hidden, the name of the help item (HelpTitle) and the text of the help (HelpText). The text boxes are called txtHelpRef, txtHelpTitle and txtHelpText.

SubFormDisplay is a generic sub to set the size of the form when it opens. It is in the module modGeneric in the sample.

The two buttons are called btnEdit and btnClose. Create the form and past in the code below. If you do not want users editing the form, change the btnEdit.Visible to "No".

Option Compare Database
Option Explicit

'+++++++++++++++++++ Start of Opening Actions ++++++++++++++++++++++
'
'---------------------------------------------------------------------------------------
' Procedure : Form_Activate
' Author : Neville Turbit
' Date : 02/07/09
' Purpose : Set the form size if the focus had moved away and back to the form.
'---------------------------------------------------------------------------------------
'
Private Sub Form_Activate()

On Error GoTo Error_Form_Activate

' Set the height and width
subFormDisplay Me.Name, 7.5, 13 ' Height , Width

Exit_Form_Activate:
On Error GoTo 0
Exit Sub

Error_Form_Activate:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: Form_frmHelp" & funCrLf & _
"Type: VBA Document" & funCrLf & _
"Calling Procedure: Form_Activate" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_Form_Activate
Resume

End Sub
'
'================ End of Opening Actions =========================

'+++++++++++++++ Start of Miscellaneous Buttons Click ++++++++++++++++++
'
'---------------------------------------------------------------------------------------
' Procedure : btnClose_Click
' Author : Neville Turbit
' Date : 29/04/2010
' Purpose : Close the form
'---------------------------------------------------------------------------------------
'
Private Sub btnClose_Click()

On Error GoTo Error_btnClose_Click

DoCmd.Close

Exit_btnClose_Click:
On Error GoTo 0
Exit Sub

Error_btnClose_Click:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: Form_frmHelp" & funCrLf & _
"Type: VBA Document" & funCrLf & _
"Calling Procedure: btnClose_Click" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_btnClose_Click
Resume

End Sub

'---------------------------------------------------------------------------------------
' Procedure : btnEdit_Click
' Author : Neville Turbit
' Date : 29/04/2010
' Purpose : Change the text box properties to allow editing or not.
' Button caption changes from "Edit" to "Lock" and back again.
'---------------------------------------------------------------------------------------
'
Private Sub btnEdit_Click()

On Error GoTo Error_btnEdit_Click

If Me.btnEdit.Caption = "Edit" Then ' Currently shown as "Edit"
Me.txtHelpTitle.Enabled = True
Me.txtHelpTitle.Locked = False
Me.txtHelpTitle.BorderStyle = 1 ' Display the border

Me.txtHelpText.Enabled = True
Me.txtHelpText.Locked = False
Me.txtHelpText.BorderStyle = 1 ' Display the border

Me.btnEdit.Caption = "Lock" ' Change the caption
Else ' Currently shown as "Lock"
Me.txtHelpTitle.Enabled = False
Me.txtHelpTitle.Locked = True
Me.txtHelpTitle.BorderStyle = 0 ' Hide the border

Me.txtHelpText.Enabled = False
Me.txtHelpText.Locked = True
Me.txtHelpText.BorderStyle = 0 ' Hide the border

Me.btnEdit.Caption = "Edit" ' Change the caption
End If

Exit_btnEdit_Click:
On Error GoTo 0
Exit Sub

Error_btnEdit_Click:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
"Please write down the following details:" & funCrLf & funCrLf & _
"Module Name: Form_frmHelp" & funCrLf & _
"Type: VBA Document" & funCrLf & _
"Calling Procedure: btnEdit_Click" & funCrLf & _
"Error Number: " & Err.Number & funCrLf & _
"Error Descritption: " & Err.Description

Resume Exit_btnEdit_Click
Resume

End Sub
'
'================= End of Miscellaneous buttons===================

 

Help Maintenance Form

Within the sample is a simple program to update the help information. It allows you to either:

  • Search for, and edit existing help records
  • Create new help records. This includes looking up the form and field name from drop down lists, and combining them to create the primary key (e.g. if the form were frmForm1, and it had a textbox txtInfo, the name would be frmForm1-txtInfo.

We have not provided a description of the code used in the help maintenance form. You can just use it without having to modify any code. For those interested, it creates a value list of forms, and based on the selected form creates a value list of appropriate controls.

Summary and Download

The download also illustrates another tweak. On the Customer form is a button to show help for the window. It uses the same module. Since there is no help item for the combination of screen name and control name, it just displays screen help.

This is a simple way to plug in help without putting together a Microsoft Access help file. There is no need to buy the tools to create a help file, and it allows users to update the information.

I would also like to recognise the following people who provided input to the application. Alex Dybenko and Stuart McCall. Thanks guys.

Please provide feedback on the application to Project Perfect.

To download the sample database, click here.

Return to the top