Handbooks

Multilevel handbooks

For creation of a similar sort of directories little bit other structure of a database is required. In fact as it was already spoken, similar directories create for simplification of a choice of value from the list with a lot of records which are filtered on the chosen value in other list. And it means, that it is required to connect two help tables, having organized from them the compound directory.

In the previous chapter it was offered to make one breadboard model of the help form instead of creation of set of the same directories and to cling to it a source of data at start. But it is possible to go deep further away. We shall take advantage of idea which was offered by Valery Kruk – we shall make one help table and we shall store in it(her) data of all directories. For this purpose it is required one more floor – the identifier of " the help table » to define, the given record concerns to what directory. And for realization multilevel the directory we shall create the second table in which the records connected with the first table will be stored(kept).

Name of the table	Name of a field	Type of a field
DIRECTORY	id	The counter
	Name	Text
	Type	Длинное целое
DIRECTORY Sub	id	The counter
	id1	The long whole
	Name	Text

At installation of communications between tables the DIRECTORY and DIRECTORY Sub we shall establish a tag « Cascade removal of the connected records » is just that case when such procedure will be useful. In fact at removal of record from the table "DIRECTORY" the data connected by it become not necessary.

If you will open the scheme of data will see only two help tables connected among themselves. In the previous example directories have been adhered to the basic table "Addressee" all, and I still in detail told about communications, tags … The matter is that at the given scheme of the organization of help system we cannot fasten the table "DIRECTORY" to what or to a field, in fact in it all help data now are stored. Yes in it also there is no need. If to consider, that the user will work with base exclusively through forms the probability of input in the basic table of records which are not present in help, is reduced practically to zero.

Parameters of directories are stored in the service table tSystemFormPar. In floor Tabl the identifier of the corresponding directory is specified. We shall consider differences from the previous variant.

Global variables and constants I have transferred all to the special module which and refers to Constants. Though to declare them it is possible in any module, the main thing that they were in section General and began with keyword Public, but it is better to store them for presentation in one place.

In module SprawForm new function – fFilListBox was added. It in many respects is similar on fFilForm, but serves for a filtration of the list, therefore in parameters instead of frm As Form is put lst As ListBox. Besides in both functions the line of formation of the filter is changed:

strFiltr = " WHERE Left([" & strFieldName & "]," & Len(strFiltr) & ") = '" & strFiltr & "'" & " and СПРАВОЧНИК.Type = " & strTableName

In fact now it is necessary to filter not only on value in a floor of the filter, but as and on parameter (the identifier of the directory) in the table tSystemFormPar. As if for the form the source of data is set through

RecordSource = strSql1 & strFiltr & " " & strSql2

That for the list it is necessary to use property RowSource

RowSource = strSql & strFiltr & " " & strSql1

Function of removal of records – sDeleteRecord was added. The user function is necessary for interception of event of removal. Now we shall look, that has changed in modules of help forms.

In the module of the form "Directory" the condition of selection on field Type of the table the DIRECTORY was added

Me.Subfrm.Form.RecordSource = strSql2 & " WHERE DIRECTORY.Type = " & strTableName & strSql3

Also has appeared new – assignment I weed Type values by default, equal to current value of a variable strTableName

Me.Subfrm.Form!Type.DefaultValue = " & strTableName

In fact if it to not make, record in the table the DIRECTORY will appear not adhered to current type of the directory

In the module of the form of the two-level directory «DIRECTORYМ» we see, that two pairs lines – sources of data are formed: strSql, strSql1 – for the list and strSql2, strSql3 – for the tabulared form. Lines are broken into pairs because between them it will be necessary to insert a condition of selection (WHERE …) That the new entered value in a floor(field) of the help form there and then was displayed in the list, procedure serves

Private Sub Fld_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    Me.ListB.RowSource = strSql & " WHERE DIRECTORY.Type = " & strTableName & strSql1
End Sub

And for navigation under the help form (search of the necessary record) it is used

Private Sub ListB_AfterUpdate()
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[id] = " & Str(Nz(Me![ListB], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

To exclude an opportunity of input in the tabulared form (a source - DIRECTORY Sub) the records which have been not connected with the basic form (a source - the DIRECTORY) serves procedure which deduces the corresponding message at similar attempt and blocks it

Private Sub Subfrm_Enter()
    If flgDeleteRecord = False Then
        If IsNull([id]) Then
            MsgBox "all over again it is necessary to get a specification!", vbCritical, NomWers
            Fld.SetFocus
        End If
    End If
End Sub

Time on the form there was a second field of the filter, there was a corresponding procedure of a filtration on this field

Private Sub П2_Change()
    strFiltr = Me.П2.Text
    Set idField = Me.П2
    Call fFilForm(strFiltr, strSql2, strSql3, Me.Subfrm.Form, "Name")
End Sub

The similar variant of the organization of help system – with use of the general tables for different directories, allows not only to simplify considerably creation of directories (for this purpose to fill in appropriate way the table tSystemFormPar enough) but as reduces the size of base due to smaller quantity(amount) of forms and tables.

However at use of help system where help tables "are collected" in one, it is necessary to remember its lacks:

• At such scheme installation of reference integrity standard Access is impossible. In fact the general help table is not adhered to any field so to trace there is nothing. And it means, that the user can easy delete data from the directory, and as a result in the form of where these data were displayed now is empty fields earlier.
• Unfortunately, far not all can be standardized directories - " to lead to a denominator ". As for example in one table the elementary directory of type " the Directory the goods " (the name, the price, a unit) and complex, like " Firms the list " with set of fields of the most various formats.

As variants of the decision of these problems probably following:

Translation of arrows

To explain to the user, that " here you have taken down(have demolished) record from the directory and consequently in earlier records now there were empty fields and anything will be not not made any more " or " you have removed record from the directory and consequently at you half of database " was erased

Transparent

To keep deleted values somehow and somewhere that removal of record from the directory left the old records using deleted record, in a former kind.

Whip and gingerbread

To forbid removal from directories, having left an opportunity of addition in them

Disciplining

In general to forbid removal and addition from/in directories

Alternative

From the point of view of convenience to the user, it seems to me, the best variant it - to give to the user an opportunity of that choice that the program with the connected data can make at removal of a primary key of the directory (with a understandable explanation of an event)

And nevertheless, I think idea it will be useful to acquaintance. And now can experiment, creating a different kind directories - simple and two-level.

The author: Admin It is added: 21.03.2007