To look behind the scenes at the various Microsoft Access objects, open the database while holding down the Shift key. To see the application as a user sees it, open it without holding down the Shift key.
The terms table contains the fields termid, term, and note: termid is an Autonumber field and is the primary key; term is a text field with an input mask that forces it to be all upper case; and note is a memo field.
The termlinks table contains the fields term1id, mnemonic, and term2id: term1id and term2id are Number fields set to look up term by termid in terms; mnemonic is a text field which identifies the type of link from the first term to the second.
The relationships for these tables appear as follows in the Microsoft Access "Relationships" window:
Note that "terms_1" is simply a second reference to terms and not another table.
Clicking on the "Edit thesaurus" button displays the editor form; clicking on the "Preview alphabetic report", "Preview preferred terms", and "Preview hierarchical report" buttons display the alpha, preferred terms, and hierarchy reports respectively. The code for these buttons was created using the wizards.
Clicking on the "Update hierarchy" button updates the hierarchy table, as explained further below.
The record source for the form is the terms table. In addition, it makes use of the termlinks table in a subform and in the Visual Basic code.
Clicking the "Find term" button brings up a standard dialog allowing the user to search the term field.
Clicking the "Delete term" button deletes the term. If the term that the user asks to be deleted has any links to other terms in the termlinks table, the cascading option set for joins between the tables requires that the links also be deleted. In this case, Microsoft Access first presents a warning message:
Relationships that specify cascading deletes are about to cause ... record(s) in this table and in related tables to be deleted. Are you sure you want to delete these records?
The Visual Basic code for these three buttons was created using the wizard. For the "Find term" button, one statement was added to ensure that the dialog would default to the term field:
Control over the appearance of subforms is somewhat limited in Microsoft Access, and subforms do not carry over many of the properties of their source forms. For example, the settings of termlinksform that hide the column headings and gray the cell backgrounds are not reflected in the subform.
The Visual Basic procedure begins by opening termlinks as a Recordset, selecting its primary key as the index to be used:
Private Sub LinkButton_Click() On Error GoTo Err_LinkButton_Click Dim dbs As Database, rst As Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("termlinks") rst.Index = "PrimaryKey"It then looks for and deletes links from the first term to the second and from the second to the first:
rst.Seek "=", termid, term2combobox.Value If Not rst.NoMatch Then rst.Delete End If rst.Seek "=", term2combobox.Value, termid If Not rst.NoMatch Then rst.Delete End IfIf the user just wanted to delete the links, the procedure is finished:
If linktype.Value = "<no link>" Then Exit Sub End IfOtherwise, the procedure first adds a record to termlinks for the link from the first term to the second:
rst.AddNew With rst !term1id = termid !term2id = term2combobox.Value !mnemonic = linktype.Value .Update End WithNext, it determines the appropriate reciprocal link type:
Dim recip As String Select Case linktype.Value Case "BT" recip = "NT" Case "NT" recip = "BT" Case "RT" recip = "RT" Case "USE" recip = "UF" Case "UF" recip = "USE" End Selectand then it adds the reciprocal link, from the second term to the first:
With rst .AddNew !term2id = termid !term1id = term2combobox.Value !mnemonic = recip .Update End WithFinally, the Recordset is closed, and the form and subform are refreshed to reflect any changes:
rst.Close Refresh Forms!termlinksform.Refresh Exit_LinkButton_Click: Exit Sub Err_LinkButton_Click: MsgBox Err.Description Resume Exit_LinkButton_Click End Sub
The "Link type" list box uses as its source a value list:
"<no link>";"USE";" UF";"BT&quo t;;"NT";"RT"The space before "UF" is a device to facilitate sorting of a list of links.
The row source for term2combobox was built with the Query Builder from the terms table. It includes both termid and term from the table; termid is invisible because the first column width is zero, but it is this value that is the value of the combo box.
In QBE, alphaquery could be expressed as a query involving tables only, without the need for termlinksquery; but, in trying to translate such a query to SQL in order to run it, Microsoft Access becomes confused and complains of an "ambiguous join".
To do this, the properties of the join between term in terms and term in nontopterms are set to option 2, which includes all records from terms. The condition "Is Null" on term from nontopterms then excludes those terms found in nontopterms.
To do this, the properties of the join between term in terms and term in nonpreferredterms are set to option 2, which includes all records from terms. The condition "Is Null" on term from nonpreferredterms then excludes those terms found in nonpreferredterms.
Unfortunately, scope notes are also repeated along with their terms; the reason is that note cannot have a group header, because Microsoft Access will not display a report grouped on a memo field.
Because of the bulk of the hierarchy table, it is not maintained dynamically, but is generated only on request; otherwise, it is empty.
Private Sub UpdateHierarchyButton_Click()This procedure begins by opening various record sets: the topterms query, which lists terms that have no narrower terms; the terms table; the termlinks table; and the hierarchy table, which it also clears.
Dim topterms As Recordset Set dbs = CurrentDb dbs.Execute "DELETE * FROM hierarchy WHERE true;" Set topterms = dbs.OpenRecordset("topterms") Set terms = dbs.OpenRecordset("terms") Set termlinks = dbs.OpenRecordset("termlinks") Set hierarchy = dbs.OpenRecordset("hierarchy") terms.Index = "PrimaryKey" termlinks.Index = "PrimaryKey"Because the terms, termlinks, and hierarchy tables will be used by other procedures and functions called by this procedure, they have been declared in the Declarations section. The same is true of the termids array, in which the term ids of the current chain will be stored temporarily.
Dim dbs As Database Dim terms As Recordset Dim termlinks As Recordset Dim hierarchy As Recordset Dim termids(5) As Integer
The procedure goes to the beginning of topterms and continues until it reaches the end. It puts the id for each top term in the first position in the chain and calls upon another procedure UpdateHierarchy with an argument of 1:
topterms.MoveFirst While Not topterms.EOF termids(1) = topterms!termid UpdateHierarchy (1) topterms.MoveNext WendWhen finished, the procedure closes the recordsets:
hierarchy.Close termlinks.Close terms.Close topterms.Close End Sub
The procedure UpdateHierarchy can be called at different levels, as specified in its argument Level:
Private Sub UpdateHierarchy(Level As Integer)It declares two local variables: i will be used as a pointer to elements in the chain; NTCount will keep track of how many narrower terms a term has.
Dim i As Integer Dim NTCount As IntegerIf Level is greater than 5, the rest of the procedure will not work; so, a little safeguard is included to prevent difficulties:
If Level > 5 Then Exit Sub End IfThe procedure now initializes NTCount to zero and looks for the first record in termlinks with the first term id the same as the term id at the specified level in the chain:
NTCount = 0 termlinks.Seek ">=", termids(Level), 0If the maximum chain length of 5 has not been reached, the procedure then begins scanning termlinks, continuing as long as the end of the table has not been reached and the term ids match:
If Level < 5 Then Do While (Not termlinks.EOF) If termlinks!term1id <> termids(Level) Then Exit Do End IfIf the link is to a narrower term, the narrower term's id is placed in the next position in the chain, and UpdateHierarchy calls itself to deal with the next level:
If termlinks!mnemonic = "NT" Then termids(Level + 1) = termlinks!term2id UpdateHierarchy (Level + 1)The narrower term count is then incremented, and the current position in termlinks is restored, since termlinks is a global variable and its current record will likely have been reset by the intervening call to UpdateHierarchy:
NTCount = NTCount + 1 termlinks.Seek "=", termids(Level), termids(Level + 1) End IfThe procedure then moves on to the next record:
termlinks.MoveNext Loop End If
If no narrower terms were found or visited, a hierarchy record must be created at this level, with any remaining positions in the chain left empty:
If NTCount = 0 Then For i = Level + 1 To 5 termids(i) = 0 Next hierarchy.AddNew hierarchy!term1 = TermOf(termids(1)) hierarchy!term2 = TermOf(termids(2)) hierarchy!term3 = TermOf(termids(3)) hierarchy!term4 = TermOf(termids(4)) hierarchy!term5 = TermOf(termids(5)) hierarchy.Update End If End Sub
The TermOf function simply gives the term for a given term id; if there is no such term, the function returns an empty string:
Private Function TermOf(Id As Integer) As String If Id <= 0 Then TermOf = "" Else terms.Seek "=", Id If terms.NoMatch Then TermOf = "" Else TermOf = terms!term End If End If End Function