Справочники в MS Access

Источник: Accessoft

Создание таблиц

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

Идею статьи подсказало следующее сообщение на http://am.rusimport.ru/MSAccess/f2.aspx?type=1&id=42149

Подскажите, как правильно разработать структуру базы для реализации почтовой базы. Интересует правильная связь таблиц: Страна, Регион, Город, Улица.
Начинающий разработчик имел в виду, как правильно установить связи между справочными таблицами и основной, в которую вставляются данные из справочников.

Создаем четыре справочные таблицы: Адресат, Справочник страны, Справочник регионы, Справочник города. В каждой таблице обязательно должно быть соответствующее ей ключевое поле (Тип Счетчик) - id.

Обычно ключевое поле таблицы, которое является уникальным идентификатором записи в этой таблице, называют внутренним ключом (в нашем случае это поля типа «Счетчик»), а ключевые поля, через которые внешние таблицы привязаны к данной - соответственно внешними ключами (в нашем случае это числовые поля Длинное целое)

Имя таблицы

Имя поля

Тип поля

Связанная таблица

Поле в связанной таблице

Адресат

idАдресат

счетчик

idСтрана

Длинное целое
Справочник страны idСтрана

idРегион

Длинное целое

Справочник регионы

idРегион

idГород

Длинное целое
Справочник города idГород

Улица

Текстовое

Дом

Текстовое

Справочник страны

id

счетчик

Адресат

idСтрана

Обозначение

Текстовое

Справочник регионы

id

счетчик

Адресат

idРегион

Обозначение

Текстовое

Справочник города

id

счетчик

Адресат

idГород

Обозначение

Текстовое

В руководствах для начинающих разработчиков часто присутствуют рассуждения, какое поле таблицы сделать ключевым. Дается определение ключевых полей и приводятся примеры связей между ними. На мой взгляд, такие рассуждения только сбивают с толку начинающих. Ведь в принципе, все довольно просто: в 99% случаев лучшее ключевое поле - счетчик. Оно 100% уникально (без повторений) и при установке связи с основной таблицей (в которую подставляются данные из этого справочника) с соответствующим полем Числовое (Длинное целое) связь автоматически определяется как один ко многим (одна запись в справочнике и много аналогичных записей в основной таблице).

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

Как видно, в справочниках только два поля: ключевое и обозначение. А основная таблица состоит в основном из числовых полей, кроме поля «Улица». Вот здесь то и проявляется основная особенность построения реляционных баз данных:

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

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

Справочники «Улицы» и «Дома» создавать, как мне думается, нет смысла. Улиц будет слишком много, их проще ввести вручную.
Так же в справочных таблицах не помешает сделать поле «Обозначение» уникальным, не допускающим повторений. Для этого в конструкторе таблицы в свойстве поля «Индексированное поле» выберем «Да. Повторения не допускаются». Если теперь нажать в конструкторе таблиц на кнопку «Индексы» (на ней значок молнии), то увидим, что в таблицу кроме основного индекса ключевого поля добавился еще один - «Обозначение».

Имена полей таблиц не должны содержать пробелов, иначе могут быть проблемы с VBA, и однозначно возникнут проблемы, при переносе базы на SQL Server. Если имя поля состоит из двух слов, то можно выбрать например такай вариант: НазваниеУлицы или Название_улицы.

Хотя согласно Help, имя может включать любую комбинацию букв, цифр, и специальных знаков за исключением точки (.), восклицательного знака (!), надстрочного знака (`) и квадратных скобок ([ ]), но желательно не использовать в именах полей таблицы никаких символов, кроме букв и цифр. Дело в том, что например имя поля таблицы типа «Улица№» в проекте mdb скорей всего не вызовет никаких конфликтов, а вот при переносе базы на SQL Server, мастер переноса просто «выкинет» его из таблицы.

При создании однотипных справочных таблиц дело пойдет быстрее, если в окне проекта «Таблицы» выделить таблицу, затем Ctrl + C или в контекстном меню при правом щелчке мыши выбрать «копировать», затем выбираем вставить, в появившемся окне задаем имя новой таблицы и жмем «ОК». Вот потому то я во всех справочниках ввел однотипное поля «id», «Обозначение». В таблице «Адресат» так же создаем внешние ключевые поля (Длинное Целое) - idСтрана, idРегион, idГород. Не забудьте убрать в них значение по умолчанию = 0.

Теперь осталось только установить связи между таблицами. Жмем в окне проекта на кнопку «Схема данных» или правой кнопкой мыши, и в контекстном меню выбираем «Схема данных». Появилась диалоговое окно «Добавление таблицы». Щелкаем дважды по всем названиям таблиц и закрываем окно.

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

Флажок «Обеспечение целостности данных»

Его установили для того, чтобы исключить возможность ввода в таблицу «Адресат» в поле «idСтрана» значения, которого нет в аналогичном поле справочной таблицы «Справочник страны». Советую всегда так делать. Этим Вы во многом избавитесь от проблемы «мусора» в базе данных - наличие ни с чем не связанных записей. Правда есть и другие способы «замусоривания», но не лишним будет уже в структурной схеме базы постараться свести их к минимуму.

Флажок «Каскадное обновление связанных полей»

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

А вот флажок «Каскадное удаление связанных записей» в данном примере я бы не рекомендовал ставить. Иначе может возникнуть весьма печальная ситуация: пользователь решит удалить название города из соответствующего справочника (мол, больше не нужен), а вместе с ним удалятся и все связанные с ним записи в таблице «Адресат» - это и есть каскадное удаление. Правда, при всякой попытке удаления Access выдает соответствующее предупреждение, но надеяться на то, что пользователь адекватно среагирует на него, я бы не стал. Каскадное удаление имеет смысл ставить в случае, например при связи таблиц «Заказы» и «Заказы данные». Удаляете заказ, а вместе с ним автоматически и все данные по нему, так как зачем данные по заказу, которого больше нет.

Остановлюсь еще на одном моменте: многим наверное приходилось сталкиваться с ошибкой «Слишком большое число». Если не хотите иметь с этим проблем, возьмите себе за правило не делать поля со списками в таблице (начинающие обычно для этого используют мастер подстановок). Дело в том, что из за несоответствия форматов единиц измерения ширины столбцов в разных версиях Access, эта самая ширина может вместо стандартной 2,54см стать … 57,2 см (слишком большим). Поэтому список лучше сделать на форме, а в таблице оставьте просто поле.

Справочная система без ... справочных таблиц

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

Ссылка таблицы на саму себя (Пример1. Вариант1)

Это самый простой способ организации справочника. Создаем форму «Адресат», цепляем к ней источник - таблицу «Адресат». Создаем поле со списком, источник которого - запрос к полю «Улица» из этой же таблицы. Запрос обязательно должен быть сгруппирован и в условии отбора необходимо исключить пустые стоки (Адресат.Улица = Is Not Null). Для обновления списка можно на свойство формы «После обновления» повесить Улица.Requery. Свойство «Ограничиться списком» должно быть «Нет». Иначе нельзя будет добавлять новые данные.

Достоинства: простота реализации, не нужны справочные таблицы, весь программный код - одной строчкой.

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

Впрочем, пробелы можно победить например так:

Trim([Улица])

а за неправильность ввода бить по рукам. К тому же, если в список заводятся например номера домов в виде чисел, то этот способ в принципе заслуживает внимания. Чтобы отличить число от текста можно воспользоваться например такой процедурой:

If Val(Дом) = 0 Then
        MsgBox "Не правильный формат данных!", vbCritical, "администратор"
        Дом = Null
End If

Но в нашем примере я завел для номеров домов тип поля «Текст» - ведь номер может быть например 1/2 или кор. 3. Поэтому, с домами разберемся по другому.

Добавление отсутствующего значение в список значений (Пример1. Вариант2)

Для реализации этого способа мы используем в качестве источника данных справочного списка - «Список значений». Основа этого способа - перехват события списка «Отсутствие в списке». Вот пример процедуры:

Private Sub Дом_NotInList(NewData As String, Response As Integer)
Dim ctl As Control    ' Возвращает объект Control, указывающий на поле со списком
        Set ctl = Me!Дом ' Приглашение подтвердить ввод нового значения
        If MsgBox("Значение отсутствует в списке. Добавить?", vbOKCancel) = vbOK Then
' Значение аргумента "Response" определяет добавление в список
                Response = acDataErrAdded
' Добавляет значение аргумента "NewData" в источник строк
                ctl.RowSource = ctl.RowSource & ";" & NewData
        Else
' При нажатии кнопки "Отмена" подавляет вывод
' сообщения об ошибке и отменяет изменения
                Response = acDataErrContinue
                ctl.Undo
        End If
End Sub

Испытаем процедуру: раскроем сначала список, посмотрим какие там номера и введем такой, которого там нет. При переходе на новую запись или другой элемент формы появится сообщение с предложением завести новое значение списка. Скажем «Да» - значение заведется, скажем нет - сотрется.

Достоинства: не нужны справочные таблицы.

Недостатки: новые данные в справочном списке, увы, не сохраняются. То есть, если в список добавилось новое значение, то при закрытии формы оно исчезнет. Все потому, что список значений можно заполнять только в конструкторе форм (в примере список не заполняется, а просто контролу временно "подсовывается" новый источник строк). Однако такой способ может оказаться полезным, когда именно это и требуется: например в списке присутствуют стандартные значения, но требуется ввести не стандартное, причем не один раз, и чтобы все разы одинаково, а в справочную таблицу заносить новые данные по каким либо причинам не следует. Ситуация довольно странная, но при работе с базами данных случается и не такое.

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

Добавление отсутствующего значение в список значений (Пример1. Вариант3)

Сначало небольшое отступление: в то время, когда составлялась данная рассылка, Валера Крук опять удивил общественность, предложив очень интересную идею. Вкратце суть его предложения в следующем:

Часто бывает, что вариантов для подстановок значений в поле формы не много и поэтому заводить для этого отдельную таблицу - справочник не желательно. Вставлять же заранее все значения в поле со списком тоже не выход, ведь иногда приходится менять содержимое списка. Валерий же предлагает компромисс - объединить справочные данные для нескольких списков в одну таблицу. А заодно предложил оригинальный интерфейс для ввода/редактирования данных. Подробно рассказывать я не буду, иначе получится плагиат, желающие могут ознакомится с примером по ссылке http://am.rusimport.ru/MSAccess/topic.aspx?id=598

В предыдущем примере рассматривался вариант добавления отсутствующего значения в список подстановок. Но данные не сохранялись, потому что для этого нужна справочная таблица. Теперь я покажу аналогичный пример, но уже с использованием справочной таблицы, тем самым решится проблема сохранения нового значения в справочнике. Добавлять данные, так же как и в предыдущем примере будем программно. Для этого воспользуемся DAO - Data Access Objects - объектная модель доступа к данным. Объекты доступа к данным создавались, как объектно - ориентированный интерфейс для ядра баз данных Jet фирмы Microsoft как раз для того, чтобы можно было программно вносить, изменять, удалять данные в таблицах.

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

Для работы с таблицей программно (через DAO), сначала необходимо объявить объектную переменную и присвоить ей источник данных (Recordset):

Dim rst As DAO.Recordset     ‘ объявляем переменую
Set rst = CurrentDb.OpenRecordset(ПолеСоСписком.RowSource) ‘ ТекущаяБаза.ОткрытьИсточникСтрок(ПолеСоСписком.ИсточникСтолбцов)

а далее стандартная обработка Recordset

rst.AddNew     ‘ ДобавитьНовуюЗапись
rst(i) = NewData         ‘ НовыеДанные
rst.Update        ‘ скорректировать (обновить источник)
rst.Close          ‘ закрыть источник

при работе с таблицей/запросом через DAO после выполнения действий со строками обязательно должно быть обновление источника rst.Update. Иначе компилятор тут же выдаст ошибку. И не забываем закрывать источник после работы - освобождаем память. В принципе это не обязательно, при выходе из процедуры Access его все равно обнулит, но стоит выработать такую хорошую привычку - освобождать переменные в конце процедур.

Вызывать функцию будем аналогично предыдущему примеру:

Private Sub Город_NotInList(NewData As String, Response As Integer)
Dim ctl As Control    ' Возвращает объект Control, указывающий на поле со списком
    Set ctl = Me!Город    ' назначаем переменной ctl текущий контрол (поле со списком)
' Приглашение подтвердить ввод нового значения
    Response = AppendLookupTable(ctl, ctl.Text)
    Me.Город.RowSource = Me.Город.RowSource     ' обновление списка (ведь там появился новый элемент)
End Sub

Формы - справочники.

Теперь займемся наконец справочными формами. Начнем с простого: создадим табличную форму - справочник. На событие «Открытие» формы вешаем процедуру:

Private Sub Form_Open(Cancel As Integer)
    Form.Caption = Me.Form.Name     ' присваиваем названию формы ее имя в базе
    DoCmd.MoveSize , , 5000, 3000   ' задаем размеры формы (ширина, высота)
    DoCmd.GoToRecord , , acNewRec   ' устанавливаем фокус на последнюю запись
End Sub

Мы получили простейшую форму справочник. Осталось придумать, как же ее вызывать? Обычно применяют два способа:

1. Раз справочник - источник поля со списком, то почему бы дважды не щелкнуть по соответствующему списку для его открытия? А чтобы это как то узаконить, покрасим список в синий (можно, разумеется, и в любой другой) цвет и объявим в справке к программе, что при двойном щелчке по полю синего цвета открывается подобающая ему справочная таблица. Создаем процедуру открытия табличной формы:

Private Sub idРегион_DblClick(Cancel As Integer)
    DoCmd.OpenForm "Справочник регионы", acFormDS, , , , acWindowNormal
End Sub

    acFormDS - указание на то, что форма открывается в режиме таблицы
    acWindowNormal - указываем тип границы (вид формы) - обычная Windows («Нормальная» в конструкторе форм)

Осталось сделать еще одну важную вещь: обновить список при закрытии справочника. Ведь если мы внесли туда новые данные (или удалили) изменения желательно тут же отобразить в списке.

Private Sub Form_Close()
    If IsLoaded("Пример1") Then Forms!Пример1.idРегион.Requery          ' обновляем поле со списком idРегион на форме Пример1
End Sub

    IsLoaded - эта функция проверки, открыта ли форма "Пример1". Взята из базы «Боррей».

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

Для организации подобного интерфейса нам понадобится служебная таблица «ФормыПараметры» и функция fOpenForm. В этом примере для разнообразия применим другой вид формы.

Справочник у нас будет состоять из двух форм: простая форма будет служить контейнером для табличной. Поле серого цвета - поле фильтра. Начните вводить в него первые буквы искомого обозначения - и содержимое таблицы станет динамически фильтроваться (по первым буквам). Фильтрация происходит при помощи функции «fFilForm».

А теперь самое интересное: я сделал только одну подобную форму справочник (основная форма «Справочник», починенная табличная «SubFrm»), а в списке их у меня две: Страны, Регионы. И в базе вы не найдете форм «Справочник страны», «Справочник регионы». Откуда же они берутся? Все очень просто. Рассмотрим функцию «fOpenForm».

Function fOpenForm(КодФормы As String) As Boolean
On Error GoTo Err_
    strFormName = DLookup("ИмяФормы", "ФормыПараметры", КодФормы)
    strTextFormName = DLookup("ОбозначениеФормы", "ФормыПараметры", КодФормы)
    strTableName = DLookup("Таблица", "ФормыПараметры", КодФормы)
    DoCmd.OpenForm strFormName
    fOpenForm = True
Exit_:
    Exit Function
Err_:
    MsgBox Err.Description
    Err.Clear
    fOpenForm = False
    Resume Exit_
End Function

Здесь мы видим процедуру присвоения значений трем переменным: strFormName, strTextFormName, strTableName. А теперь посмотрим на самый верх модуля «Module1». Там мы увидим

Public strFormName As String
Public strTextFormName As String
Public strTableName As String

Здесь мы подошли к такому важному понятию, как область видимости переменных. Раз эти три переменных объявлены в разделе General общего модуля с ключевым словом Public, то они становятся видимыми для всех модулей приложения. Например, их "увидят" процедуры модуля формы «Справочник» для того чтобы присвоить форме соответсвующие параметры. Рассмотрим их.

В таблице «ФормыПараметры» есть три поля: ИмяФормы (имя формы в приложении), ОбозначениеФормы (текстовое обозначение формы, или просто заголовок формы), Таблица (источник данных формы - имя таблицы). В функции «fOpenForm» происходит присвоение этим переменным значений при помощи

DLookup("ИмяПоляТаблицы/запроса", "ИмяТаблицы/запроса", "Условие отбора")

как видно, значения берутся из полей служебной таблицы «ФормыПараметры». А затем команда на открытие формы DoCmd.OpenForm strFormName

Теперь заглянем в модуль формы «Справочник». Рассмотрим процедуру, происходящую при открытии формы:

Private Sub Form_Open(Cancel As Integer)
    Form.Caption = strTextFormName
    Set idField = Me.П1
    strSql = "SELECT [" & strTableName & "].id, [" & strTableName & "].Обозначение FROM [" & strTableName & "]"
    strSql1 = " ORDER BY [" & strTableName & "].Обозначение"
    Subfrm.Form.RecordSource = strSql & strSql1
End Sub

Открытие формы происходит после присвоения значения глобальным переменным. Свое обозначение и источник данных форма получает при открытии.

В результате получился макет формы. При запуске форме присваивается текстовое обозначение и цепляется источник строк при помощи глобальных переменных, значения для которых берутся из служебной таблицы. Таким образом, становится возможным используя всего один макет формы «создавать» множество однотипных справочников. Для этого достаточно лишь заполнить необходимыми данными служебную таблицу. Единственное условие: во всех справочных таблицах должно быть два поля, называться они должны одинаково: id, Обозначение. Это накладывает ограничение на возможные варианты справочников, но ведь речь идет о простых справочных формах.

А теперь, попробуйте сами: "создайте" справочник «Города», заполнив соответствующими данными таблицу «ФормыПараметры».

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

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

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

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

Имя таблицы

Имя поля

Тип поля

СПРАВОЧНИК

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. Ведь общая справочная таблица не привязана ни к какому полю, а значит отслеживать нечего. А это значит, что пользователь спокойно может удалять данные из справочника, и в результате в форме, где эти данные раньше отображались теперь булут пустые поля.

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

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

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

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

Прозрачный

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

Кнут и пряник

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

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

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

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

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

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

Ссылки по теме