This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.
Years ago, a printed manual and a comprehensive on-line help - a help file - were mandatory parts of any decent application for commercial usage.
Both are rare these days, as applications are expected to be self-explanatory, or the user is directed to obtain help from an online forum or a downloaded PDF.
Still, a good and context-sensitive help file can be the optimum help for the user, and - if present - the fastest method, ready at hand as it is; no time wasted on browsing and sorting good info from all the irrelevant stuff.
Writing a good help file is an art in itself, which not will be touched here. However, if this is what you need, a good place to start is one of these sites (examples only, no affiliation to these): HelpScribble or Innovasys Help Studio, which is also the origin of the sample help file used here (see Download later).
The help file itself is of a special compressed format - with the extension .chm - which requires a special viewer. That viewer is often the native viewer of Windows, Microsoft HTML Help Viewer, an OCX control.
From VBA, the viewer is opened via one of two API calls to the control, hhctrl.ocx, which normally is present on all newer Windows installs. This would be very simple, if not for these traps:
The documentation on the control is sparse and rudimentary, almost nil, and these traps are to be found by trial and error or to be extracted bit by bit at various fora and blog posts. However, once taken care of, the usage is straight-forward.
These days, API calls should be declared to work in both 32- and 64-bit VBA environments, as more and more corporations move to the 64-bit version of Microsoft Office.
This is done in the header of the code module, and also a special UDT (User Defined Type) for the search function is declared:
' API calls for the HTML Help Viewer control. ' Sample help file for download: ' http://www.innovasys.com/download/examplechmzipfile?ZipFile=%2FStatic%2FHS%2FSamples%2FHelpStudioSample_CHM.zip ' Open a compiled HTML help file (.chm) or close all opened help files. #If VBA7 Then Private Declare PtrSafe Function HTMLHelpShowContents Lib "hhctrl.ocx" Alias "HtmlHelpA" ( _ ByVal hwnd As LongPtr, _ ByVal lpHelpFile As String, _ ByVal wCommand As Long, _ ByVal dwData As Long) _ As Long #Else Private Declare Function HTMLHelpShowContents Lib "hhctrl.ocx" Alias "HtmlHelpA" ( _ ByVal hwnd As Long, _ ByVal lpHelpFile As String, _ ByVal wCommand As Long, _ ByVal dwData As Long) _ As Long #End If ' Open a compiled HTML help file (.chm) with the Search tab active. #If VBA7 Then Private Declare PtrSafe Function HTMLHelpShowSearch Lib "hhctrl.ocx" Alias "HtmlHelpA" ( _ ByVal hwnd As LongPtr, _ ByVal lpHelpFile As String, _ ByVal wCommand As Long, _ ByRef dwData As HhFtsQuery) _ As Long #Else Private Declare Function HTMLHelpShowSearch Lib "hhctrl.ocx" Alias "HtmlHelpA" ( _ ByVal hwnd As Long, _ ByVal lpHelpFile As String, _ ByVal wCommand As Long, _ ByRef dwData As HhFtsQuery) _ As Long #End If ' User Defined Types. ' ' UDT for HTMLHelpShowSearch. Private Type HhFtsQuery cbStruct As Long ' Size of structure in bytes. fUniCodeStrings As Long ' TRUE if all strings are unicode. pszSearchQuery As String ' String containing the search query. iProximity As Long ' Word proximity. fStemmedSearch As Long ' TRUE for StemmedSearch only. fTitleOnly As Long ' TRUE for Title search only. fExecute As Long ' TRUE to initiate the search. pszWindow As String ' Window to display in. End Type
The interesting parameter is wCommand which controls what the viewer control does and how it appears. It must have one of a few special values which - for ease of use and to prevent errors - has been collected in an Enum:
' Commands to control the appearance of the viewer when launched. Public Enum hhCommand ' Select the last opened tab. DisplayTopic = &H0 ' Select the Contents tab. DisplayContents = &H1 ' Select the Index tab. DisplayIndex = &H2 ' Select the Search tab. DisplaySearch = &H3 ' Select the Contents tab and open a topic by its index. OpenContext = &HF ' Close all windows opened by the viewer. CloseAll = &H12 End Enum
Now, everything is prepared.
One function does it all, controlled by the key parameter: Command.
The next parameters are HelpFile to specify to help file to open. The last parameter, Context, is used to pass the Context ID of a specific help page that should be opened, ready to be read.
Some typical examples for the usage are listed in the header of the function, HelpControl:
' Displays a compressed HTML help file using the HTML Help File Viewer. ' Alternatively, closes all windows of the viewer that may be open. ' ' Returns True if the operation was successful. ' Returns False if the operation couldn't be carried out, for example ' if one or more parameter is invalid, or - for closing windows - if ' no windows were open. ' ' Example, display the Contents tab: ' Success = HelpControl(DisplayContents, "d:\path\HelpStudioSample.chm") ' Example, display the Contents tab and open the page with context id 7: ' Success = HelpControl(OpenContext, "d:\path\HelpStudioSample.chm", 7) ' Example, display the Index tab: ' Success = HelpControl(DisplayIndex, "d:\path\HelpStudioSample.chm") ' Example, display the Search tab: ' Success = HelpControl(DisplaySearch, "d:\path\HelpStudioSample.chm") ' Example, display the last opened tab and the main page: ' Success = HelpControl(DisplayTopic, "d:\path\HelpStudioSample.chm") ' Example, close all opened Help Viewer windows: ' Success = HelpControl(CloseAll) ' ' Note: ' If the help file is stored on a networked folder and will open, but ' will not display the context pages, move the file to a local folder. ' ' 2018-04-26. Gustav Brock, Cactus Data ApS, CPH. ' Public Function HelpControl( _ ByVal Command As hhCommand, _ Optional ByVal HelpFile As String, _ Optional ByVal Context As Long) _ As Boolean ' Use default owner handle (Application). Const OwnerHandle As Long = 0 ' Neutral values. Const NoHandle As Long = 0 Const NoTopic As Long = 0 Const NoFile As String = "" ' Handle of the current Help Viewer window (if any). Static OpenHandle As Long Dim SearchQuery As HhFtsQuery Dim Handle As Long ' Manage the Help Viewer. Select Case Command ' Open the Help Viewer and display a tab. Case hhCommand.DisplayTopic, _ hhCommand.DisplayContents, _ hhCommand.DisplayIndex Handle = HTMLHelpShowContents(OwnerHandle, HelpFile, Command, NoTopic) ' Open the Help Viewer and display the topic having the ID of Context. Case hhCommand.OpenContext ' Reset displayed tab to Contents. Handle = HTMLHelpShowContents(OwnerHandle, HelpFile, hhCommand.DisplayContents, NoTopic) ' Open help context page. Handle = HTMLHelpShowContents(OwnerHandle, HelpFile, Command, Context) ' Open the Help Viewer and display the Search tab. Case hhCommand.DisplaySearch SearchQuery.cbStruct = Len(SearchQuery) Handle = HTMLHelpShowSearch(OwnerHandle, HelpFile, Command, SearchQuery) ' Close all windows opened by the Help Viewer. Case hhCommand.CloseAll If OpenHandle = NoHandle Then ' Don't waste time on closing non-existing windows. Else ' A help file has been opened. ' Set Handle to return success. Handle = OpenHandle ' Make sure, all help windows are closed, and reset OpenHandle. OpenHandle = HTMLHelpShowContents(OwnerHandle, NoFile, Command, NoTopic) End If Case Else ' Ignore. End Select If Command <> hhCommand.CloseAll Then If Handle <> NoHandle Then ' Store the handle of the window. OpenHandle = Handle End If End If ' Return True if success. HelpControl = (Handle <> NoHandle) End Function
The in-line comments explain the details of the operation but pay attention to two details.
First, the command OpenContext causes two calls of the API. First, it sets the viewer to display tab Contents, then it opens the specific context page. This is to avoid that the context page is opened while, say, the Search tab is displayed, which would confuse the user.
Second, the function maintains hold of the handle (the "ID") of the help window opened with the static variable OpenHandle.
This will be 0 (zero) if no help window has been opened. If that is the case, there is no need to call the close command which, otherwise, would take about half second. Not much but, in some cases, that is exactly the level of delay, that could make the application appear "slow" to the user.
In the download, a small Access application which demonstrates the use of the function in a form, is included.
It contains an option group to set the action, a button to carry the command out, and a button to close the Help Viewer window:
The full code is held at a minimum:
Private Sub CloseHelp_Click() HelpControl CloseAll End Sub Private Sub Form_Close() ' Make sure the Help Viewer is closed, or Access may crash when closing. HelpControl CloseAll End Sub Private Sub OpenHelp_Click() ' Name of help file. Const FileName As String = "HelpStudioSample.chm" Dim Command As hhCommand Dim Context As Long Dim HelpFile As String ' Position the help file in the folder of the application. HelpFile = CurrentProject.Path & "\" & FileName ' Open the Help Viewer. Select Case Me!HelpGroup.Value Case 1 Command = DisplayContents Case 2 Command = DisplayIndex Case 3 Command = DisplaySearch Case 4 Command = OpenContext Context = Val(Nz(Me!ID.Value)) End Select HelpControl Command, HelpFile, Context End Sub
Note, in the Close event, the call to close the Help Viewer window, should it have been left open. As mentioned earlier, an open Help Viewer window may cause the application to crash when closing.
Full code and an Access demo application as presented here will provide a good starting point for implementing an on-line help function for an application using either 32- or 64-bit VBA.
Another example of usage can be found in another of my articles:
The full and current code is available for download at GitHub: VBA.HtmlHelp
Also, code and a demo application is here: HelpDemo 1.0.2.zip
Sample help file: HelpStudioSample