Access Tip – Help Screen Function
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_KeyDownIf KeyCode = vbKeyF1 Then KeyCode = 0 Call subGetHelp() End IfExit_Form_KeyDown: On Error GoTo 0 Exit Sub Error_Form_KeyDown: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_Form_KeyDown |
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 ‘ Create the SQL query for the form and control 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”) ‘ Create the SQL query for the form and control If funRecordCount(strSQL) > 0 Then GoTo LaunchHelp ‘ Record found ‘ Display a message that there is no help available LaunchHelp: ‘ Open the form and display the correct help record Exit_subGetHelp: Error_subGetHelp: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_subGetHelp |
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 Exit_Form_Activate: Error_Form_Activate: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_Form_Activate End Sub ‘+++++++++++++++ Start of Miscellaneous Buttons Click ++++++++++++++++++ On Error GoTo Error_btnClose_Click DoCmd.Close Exit_btnClose_Click: Error_btnClose_Click: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_btnClose_Click End Sub ‘————————————————————————————— On Error GoTo Error_btnEdit_Click If Me.btnEdit.Caption = “Edit” Then ‘ Currently shown as “Edit” Me.txtHelpTitle.Enabled = True Me.btnEdit.Caption = “Lock” ‘ Change the caption Me.txtHelpText.Enabled = False Me.btnEdit.Caption = “Edit” ‘ Change the caption End If Exit_btnEdit_Click: Error_btnEdit_Click: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_btnEdit_Click End Sub |
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.