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:
term.SetFocus
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 If
If the user just wanted to delete the links,
the procedure is finished:
If linktype.Value = "<no link>" Then
Exit Sub
End If
Otherwise,
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 With
Next,
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 Select
and 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 With
Finally,
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
Wend
When 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 Integer
If 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 If
The 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), 0
If 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 If
If 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 If
The 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
Home