Каскадное обновление связанных списков

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

Для рассмотрения примера создадим три справочных таблицы:

Индексируем поля таблиц для получения возможности автоматического отслеживания уникальности значений. В противном случае может случится, что одно и то же значение в справочнике повторяется. Для этого открываем таблицу в режиме конструктора, жмем на значок в меню "Индексы" (значок молнии). В появившемся диалоговом окне в поле "Индекс" пишем название индекса (любое), например "Страна". Затем в поле "Имя поля" выбираем из списка "Страна" и в нижней части формы выбираем из списка "Уникальный индекс" - Да. Теперь при попытке ввести уже существующее значение появится соответствующее сообщение и ввод будет заблокирован.

В таблицах "Производители" и "Товары" индекс должен быть составной, так как нужно отслеживать уникальность пары Страна - Производитель. Ведь разные страны могут выпускать одинаковые товары. Пример создания составного индекса:

Связываем отношением один ко многим справочные таблицы: Страны - Производители - Товары. Таким образом получили систему из трех связанных справочников, в которой кроме данных устанавливаются так же зависимости между таблицами. Создадим так же для демонстрации примера автозаполнения две рабочих таблицы: Заказы и Список товаров.

Связываем таблицы "Заказы" и "Список товаров" соотношением один ко многим по полям "id_Заказ" (на один заказ может быть много товаров).
Теперь рассмотрим вариант создания формы - справочника. Можно двумя вариантами:

1. Сделать справочник "Страны". Потом два справочника: "Страны - производители" и "Производители - товары". Заполняться они должны поочередно: Сначала "Страны", потом "Страны - производители", потом "Производители - товары".
2. Сделать один двухуровневый справочник: "Страны - производители - товары".

Во втором случае нужно предусмотреть процедуру, препятствующую вводу данных в форму "Производители", если не заведены данные в форме "Страны". Иначе получим не связанную запись. Для этого служит процедура события формы Enter (Вход).

Private Sub subСправочникПроизводители_Enter()
      If IsNull(Me.id_Страны) Then
            MsgBox "Сначала укажите страну производителя", vbCritical, "admin"
            Страна.SetFocus
      End If
End Sub

Как видим, при попытке ввести данные в форму, когда не введен ключ от главной формы

If IsNull(Me.id_Страны) Then

появляется сообщение об ошибке и фокус ввода переносится на поле главной формы. Аналогичная процедура блокировки сделана и для подчиненной табличной формы на форме "Заказы".

На форме "Заказы" присутствуют два списка: Страны и Производители. Причем второй связан с первым через ссылку на него в запросе (источнике своих данных). Откройте форму "Заказы" в конструкторе и посмотрите на источник строк списка Производитель(id_Компании). На поле id_Страны установлено условие отбора

Eval("Forms!Заказы!id_Страны")

Аналогично и на поле со списком табличной подчиненной формы "subЗаказы"

Eval("Forms!Заказы!id_Компании")

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

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

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

Private Sub id_Страны_NotInList(NewData As String, Response As Integer)
      Set ctl = Me!id_Страны 'устанавливаем ссылку на список "Страны"
      ' запускаем функцию каскадного добавления, и в качестве второго аргумента
      ' указываем первый список. То есть функция работает как обычная процедура
      ' добавления в один список
      Response = AppendLookupTable(ctl, ctl, ctl.Text, 0)
      Me.id_Страны.RowSource = Me.id_Страны.RowSource 'обновляем список стран
      Set ctl = Nothing 'очищаем переменную
End Sub

В этом случае после проверки условия (каскадное или обычное добавление) отсутствующее значение добавляется так же как в старой функции

If cbo.Name = cbo1.Name Then
      Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
      With rst
            .AddNew
            rst(1) = NewData
            .Update
            .Close
      End With

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

Private Sub id_Компании_NotInList(NewData As String, Response As Integer)
      Set ctl = Me.id_Компании 'устанавливаем ссылку на список "Компании"
      Set ctl1 = Me.id_Страны 'устанавливаем ссылку на список "Страны"
      ' запускаем функцию каскадного добавления в два списка
      Response = AppendLookupTable(ctl, ctl1, ctl.Text, Nz(Me.id_Страны, 0))
      Set ctl = Nothing 'очищаем переменную
      Set ctl1 = Nothing 'очищаем переменную
      Me.id_Компании.RowSource = Me.id_Компании.RowSource 'обновляем список компаний
      ' в случае отказа от заполнения
      If Response = 0 Then
            Me.id_Компании = Null 'обнуляем список компаний
            id_Страны.SetFocus  'устанавливаем фокус на список страны
      End If
End Sub

А теперь по поводу Eval(). Дело в том, что процедура добавления отсутствующего значения в таблицу - источник списка происходит при помощи объектной модели DAO. Если в строке запроса встретится выражение типа Forms!Заказы!id_Страны - будет сообщение об ошибке типа: "Требуется параметр". Потому, что DAO, знать ничего не знает, об открытых формах, именно по этому и ругается. Ему дали строку SQL, он пытается открыть соответствующий рекордсет. Пытается найти поле таблицы с именм Forms! и разумеется не находит.

Поэтому, когда используется работа с запросом при помощи DAO, то ссылки на элементы форм нужно оформлять через Eval(). Например так:

Eval("Forms!Заказы!id_Страны")

Или вместо имени приводить сам текст запроса, и в нем указывать ссылку:

Set rst = CurrentDb.OpenRecordset("SELECT Производители.id_Компании, " & _
"Производители.Компания, Производители.id_страны " & _
"FROM Производители " & _
"WHERE Производители.id_Страны = " & Forms!Заказы!id_Страны & _
"ORDER BY Производители.Компания")

Эти рекомендации справедливы и при работе с объектной моделью ADO. Часто первое, на чем спотыкаются те, кто впервые решил перевести свой проект с mdb на adp - это ошибки при выполнении запросов, где есть ссылки на элементы формы. Ведь теперь обработка данных происходит на сервере, на котором нет никаких форм. Впрочем, это уже совсем другая тема, к данной статье не имеющая отношения - переход от mdb к adp.

В заключение остановлюсь еще на одном вопросе, который так же часто задают начинающие разработчики: как отключить стандартные сообщение Access? Например, при использовании процедуры на удаление записи

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

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

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True

Но в этом случае отключаются все сообщения, а не только "Прервано выполнение макрокоманды DoMenuItem". И если при выполнении "огражденной" процедуры возникнет какая либо другая ошибка - никто об этом "не узнает". А ведь ошибки бывают и фатальными, с "вылетом" из программы. Еще хуже, если увлекшись включением/отключением стандартных сообщений разработчик забудет потом в коде программы включить их.

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

Function sDeleteRecord() As Boolean
On Error GoTo Err_
      DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
      DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
      sDeleteRecord = True
Exit_:
      Exit Function
Err_:
      ErrNum = Err.Number
      sDeleteRecord = False
      Err.Clear
      Resume Exit_
End Function

Для ее работы  в глобальном модуле Constants введена переменная Public ErrNum As Long. Через нее передается номер ошибки. Сама же процедура удаления записи выглядит так (для формы "СПРАВОЧНИК страны производители товары"):

Private Sub butDelete_Click()
      sDeleteRecord
      Select Case ErrNum
            Case 2501
                  Err.Clear
            Case 3396
                  MsgBox "Данные нельзя удалять, иначе в таблице [Список товаров] появятся не связанные записи!", vbCritical, "admin"
      End Select
End Sub

Здесь возможны две ошибки:

• 2501 - прерывание команды удаления (передумали удалять)
• 3396 - нарушение целостности данных

Первую ошибку можно просто игнорировать - Err.Clear, а вот в случае возникновения второй - не помешает вывести соответствующее сообщение, чтобы было понятно, почему нельзя удалять.      Таким образом, можно создавать свои процедуры обработки ошибок выполнения. Чтобы узнать номера ошибок, можно воспользоваться Err.Number. Например, при отладке новой процедуры включить в нее обработчик ошибок:

On Error GoTo Err_

      …..
      …..
      …..

Exit_:
      Exit Sub
Err_:
      MsgBox "Ошибка " & Err.Number & ": " & Err.Description, vbInformation
      Err.Clear ' сброс ошибки
Resume Exit_