Справочники.

Многоуровневые справочники.

Для создания подобного рода справочников потребуется немного другая структура базы данных. Ведь как уже говорилось, подобные справочники создают для облегчения выбора значения из списка с большим количеством записей, которые фильтруются по выбранному значению (параметру) в другом списке. А это значит, что потребуется связать две справочные таблицы, организовав из них составной справочник.

В предыдущей главе предлагалось вместо создания множества однотипных справочников сделать один макет справочной формы и цеплять к нему источник данных при запуске. Но можно углубиться еще дальше. Воспользуемся идеей, которую предложил Валерий Крук – сделаем одну справочную таблицу и будем хранить в ней данные всех справочников. Для этого потребуется еще одно поле – идентификатор «справочной таблицы», чтобы определить, к какому справочнику относится данная запись. А для реализации двухуровнего справочника создадим вторую таблицу, в которой будут храниться связанные с первой таблицей записи.

Имя таблицы	Имя поля	Тип поля
СПРАВОЧНИК	id	счетчик
	Name	Текстовое
	Type	Длинное целое
СПРАВОЧНИК Sub	id	счетчик
	id1	Длинное целое
	Name	Текстовое

При установке связей между таблицами СПРАВОЧНИК и СПРАВОЧНИК Sub установим флажок «Каскадное удаление связанных записей» - это как раз тот случай, когда такая процедура будет полезной. Ведь при удалении записи из таблицы «СПРАВОЧНИК» связанные с ней данные становятся не нужными.

Если Вы откроете схему данных, то увидите только две связанные между собой справочные таблицы. В предыдущем примере к основной таблице «Адресат» были привязаны все справочники, и я еще подробно рассказывал о связях, флажках… Дело в том, что при данной схеме организации справочной системы мы не сможем прицепить таблицу «СПРАВОЧНИК» к какому либо полю, ведь в ней теперь хранятся все справочные данные. Да в этом и нет надобности. Если учесть, что пользователь будет работать с базой исключительно через формы, то вероятность ввода в основную таблицу записей, которых нет в справочной, сводится практически к нулю.

Параметры справочников хранятся в служебной таблице tSystemFormPar. В поле Tabl указан идентификатор соответствующего справочника. Рассмотрим отличия от предыдущего варианта.

Все глобальные переменные и константы я  перенес в специальный модуль, который так и называется Constants. Хотя объявлять их можно в любом модуле, главное, чтобы они были в разделе General и начинались с ключевого слова Public, но лучше хранить их для наглядности в одном месте.

В модуль SprawForm добавилась новая функция – fFilListBox. Она во многом похожа на fFilForm, но служит для фильтрации списка, поэтому в параметрах вместо frm As Form поставлено lst As ListBox. Кроме этого, в  обеих функциях изменена строка формирования фильтра:

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

Ведь теперь нужно фильтровать не только по значению в поле фильтра, но так же и по параметру (идентификатору справочника) в таблице tSystemFormPar. Так же если для формы источник данных задается через

RecordSource = strSql1 & strFiltr & " " & strSql2

то для списка нужно использовать свойство RowSource

RowSource = strSql & strFiltr & " " & strSql1

Добавилась функция удаления записей – sDeleteRecord. Пользовательская функция нужна для перехвата события удаления. Теперь посмотрим, что изменилось в модулях справочных форм.

В модуле формы «Справочник» добавилось условие отбора по полю Type таблицы СПРАВОЧНИК

Me.Subfrm.Form.RecordSource = strSql2 & " WHERE СПРАВОЧНИК.Type = " & strTableName & strSql3

и появилось новое – присвоение полю Type значения по умолчанию, равное текущему значению переменной strTableName

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

Ведь если этого не сделать, то запись в таблице СПРАВОЧНИК окажется не привязанной к текущему типу справочника.

В модуле формы двухуровневого справочника «СправочникМ» видим, что формируются две пары строк – источников данных: strSql, strSql1 – для списка и strSql2, strSql3 – для табличной формы. Строки разбиты на пары потому, что между ними нужно будет вставлять условие отбора (WHERE…) Чтобы новое введенное значение в поле справочной формы тут же отображалось в списке, служит процедура

Private Sub Fld_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    Me.ListB.RowSource = strSql & " WHERE СПРАВОЧНИК.Type = " & strTableName & strSql1
End Sub

А для навигации по справочной форме (поиску нужной записи) используем

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

Чтобы исключить возможность ввода в табличную форму (источник - СПРАВОЧНИК Sub) записей, не связанных с основной формой (источник - СПРАВОЧНИК) служит процедура, которая выводит соответствующее сообщение при подобной попытке и блокирует ее:

Private Sub Subfrm_Enter()
    If flgDeleteRecord = False Then
        If IsNull([id]) Then
            MsgBox "Сначала нужно завести основные данные!", vbCritical, NomWers
            Fld.SetFocus
        End If
    End If
End Sub

Раз на форме появилось второе поле фильтра, появилась соответствующая процедура фильтрации по этому полю

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

Подобный вариант организации справочной системы – с использованием общих таблиц для разных справочников, позволяет не только значительно упрощать создание справочников (для этого достаточно заполнить соответствующим образом таблицу tSystemFormPar), но так же уменьшает размер базы за счет меньшего количества форм и таблиц.

Однако при использовании справочной системы, где справочные таблицы "собраны" в одну, следует помнить о ее недостатках:

• При такой схеме невозможна установка ссылочной целостности стандартными стредствами Access. Ведь общая справочная таблица не привязана ни к какому полю, а значит отслеживать нечего. А это значит, что пользователь спокойно может удалять данные из справочника, и в результате в форме, где эти данные раньше отображались теперь булут пустые поля.
• К сожалению, далеко не все справочники можно стандартизировать - "привести к общму знаменателю". Как к примеру всести в одну таблицу простейший справочник типа "Справочник товары" (наименование, цена, ед. изм) и сложный, наподобие "Фирмы список" с множеством полей самых различных форматов.

Как варианты решения этих проблем возможно следующее:

Перевод стрелок

Объяснить пользователю, что "вот ты снес запись из справочника и поэтому в более ранних записях теперь остались пустые поля и ничего уже не сделаешь" или "ты удалил запись из справочника и поэтому у тебя стерлась половина базы данных"

Прозрачный

Сохранять удаляемые значения как-то и где-то, для того, чтобы удаление записи из справочника оставляло старые записи, использующие удаляемую запись, в прежнем виде.

Кнут и пряник

Запретить удаление из справочников, оставив возможность добавления в них

Дисциплинирующий

Вообще запретить удаление и добавление из/в справочников

Альтернатива

С точки зрения удобства для пользователя (заказчика), мне кажется, лучший вариант это - предоставить пользователю возможность выбора того, что может программа сделать со связанными данными при удалении первичного ключа из справочника (с доходчивым объяснением происходящего).

И тем не менее, думаю сама идея будет полезна для ознакомления. А теперь можете поэкспериментировать, создавая разного вида справочники  - простые и двухуровневые.

Пример для данной статьи Вы можете скачать ниже.

Обсудить на форуме...

Автор: Admin Spraw 56 кБ SprawSub 59 кБ Добавлен: 21.03.2007

---

Комментарии к статье

Посылаю Вам Ваш же пример, только сделанный еще проще. Вообще без использования какого либо кода. Причем уровень вложенности справочника произвольный. Единственно, каскадное удаление необходимо делать вручную. Павел Воронков SprawSubWar 20 кБ