Статья написана в учебных целях, хотя в ней есть и пара интересных неучебных нюансов.

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

Классический способ реализации такой связи - промежуточная таблица, хранящая внешние ключи категорий и объектов и реализующая, таким образом, две связи "один ко многим", на которые мы разложили наше отношение:

Сама по себе реализация такого редактора в Access несложна, вот весь процесс.

1. Создаем новую базу данных и сохраняем ее.

2. В окне базы данных на вкладке "Таблицы" создаем в режиме конструктора 3 таблицы:

• таблица "Категории" будет включать поле "Код категории", имеющее тип "Счетчик" и текстовое поле "Категория", служащее для описания; щелкнув правой кнопкой на поле "код", сделаем его ключевым:
  
• таблица "Объекты" будет устроена аналогично: она включает ключевое поле-счетчик с именем "Код объекта" и текстовое поле "Объект", предназначенное для данных;
• наконец, таблица "Связи" состоит из двух числовых полей, показанных ниже:
  

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

3. Идем Сервис, Схема данных..., добавляем в окно схемы все 3 таблицы и связываем их, перетаскивая поля мышкой так, чтобы получились связи, показанные на первом рисунке. В появившемся окне "Изменение связей" нужно включить все флажки для обеспечения целостности данных при добавлении или удалении записей.

4. Закрыв и сохранив схему, вносим по несколько записей в таблицы "Категории" и "Объекты".

5. Формы для работы с категориями и объектами по отдельности или в связке "главная и подчиненная таблица" сделать легко. Для последнего, например, достаточно перейти на вкладку Формы, вызвать Мастер форм, добавить для формы все поля таблиц "Категории" и "Объекты", а на следующем шаге определить главную и починенную формы. Но нас интересует сейчас не это. Главное, что мы хотим сделать - спроектировать форму "Связи" для редактирования наших данных.

6. Вызываем Конструктор форм, получаем новую пустую форму. Если окна "Раздел: область данных" (на самом деле это окно свойств) нет на экране, вызываем его, выбрав в окне формы правой кнопкой мыши пункт меню Свойства.

7. В выпадающем списке окна свойств выбираем "Форма" и назначаем на вкладке Данные нашей форме источником данных таблицу "Связи".

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

Если мастера для этого или других элементов не вызываются, причин может быть 2: не нажата кнопка "Мастера" на Панели элементов или не установлены соответствующие компоненты Access.

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

9. Аналогичным образом создаем список для отображения объектов из таблицы "Объекты", а называться он будет "СписокОбъекты".

10. В принципе, все готово. Наша форма работает, в чем можно убедиться, открыв ее кнопкой Вид

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

11. Вернувшись в режим конструктора, добавим на свободное место кнопку и с помощью мастера "Создание кнопок" назначим ей действие "добавить запись" из категории действий "Обработка записей". Все остальное можно настроить по вкусу.

12. При выбранной кнопке в окне свойств перейдем на вкладку События и обратимся к коду сгенерированного нами обработчика:

13. Мы перепишем код сгенерированной Access процедуры так, чтобы он отслеживал ситуацию, когда в списках категорий и объектов ничего не выбрано, а также не давал повторно добавить уже существующую связь. Для последнего действия нам понадобиться выполнить из кода на VBA дополнительный запрос по извлечению выбранных на форме кода категории и кода объекта из таблицы "Связи". Если этот запрос вернет пустой результат, значит, такой связи еще нет и следует добавить запись. Сгенерировать такой запрос на вкладке "Запросы" и потом просто вызвать его, к сожалению, не получится. Дело в том, что Access не видит взятых из формы параметров запроса, если запрос выполняется программно. Ошибка, как правило, возникает со следующим текстом "Too few parameters. Expected Число" ("Слишком мало параметров. Ожидалось Число"). Эта ошибка возникает, если команда или один из нижележащих запросов содержит обращения к формам или собственные параметры, - все эти обращения будут восприняты как параметры, которым не передано значение.

Почему так происходит? По этому поводу в справке Microsoft MSDN написано примерно следующее:
NOTE: В DAO Вы должны явно присвоить значение параметру. При использовании DoCmd.OpenQuery Вы этого делать не должны, т.к. DAO использует операции низкого уровня, что даёт Вам большую свободу в использовании параметров (т.е. Вы можете сами присвоить параметру значение переменной, а не использовать ссылку на форму), но Вы должны выполнить служебные действия, которые Access делает "за кулисами" при исполнении DoCmd. С другой стороны, DoCmd работает на более высоком уровне, чем DAO. Выполняя DoCmd, Microsoft Access делает некоторые предположения о том, как поступить с параметрами, и не дает Вам никакой свободы в этом отношении.

Попросту говоря, при выполнении запроса непосредственно из окна Access он выполняется с помощью движка JET, который, будучи встроен в Access, "знает" о наличии форм и пытается найти их поля и подставить значения. При выполении запроса из кода методом Execute или иным, запрос выполняется с помощью библиотеки DAO, которая, будучи внешней, ничего "не знает" о формах ACCESS, поэтому все недостающие значения считает неопределенными.

В Интернете можно встретить советы предварительно обработать все параметры процедурой вида

Dim q As DAO.QueryDef, p As DAO.Parameter 
  Set q = CurrentDb.QueryDefs("ИмяЗапроса") 'как обычного запроса Select,
  'так и INSERT/UPDATE; в запросах на удаление это не помогает
  For Each p In q.Parameters 
    p.Value = Eval(p.Name) 
  Next 
  q.Execute 
  q.close: Set q=Nothing

Однако, для работы этого кода нужно, во-первых, иметь подключенную DAO (в окне редактора Visual Basic при остановленной программе вызвать Tools, References (или Сервис, Ссылки), найти и включить в списке библиотеку Microsoft DAO 3.6 Object Library), во-вторых, работа кода все-таки не гарантируется и в этом случае.

Мы хотим обойтись стандартным кодом

Dim rst As Recordset
CurrentDb.OpenRecordset ("строка запроса")

однако, едва избавившись от ошибок с недостающимим параметрами, получим сообщение о нессответствии типов (type mismatch, ошибка с кодом 13)!

Вся проблема состоит в том, что объект RecordSet есть и в библиотеке DAO, и в используемой Access по умолчанию библиотеке ADODb! Таким образом, наличие прямой ссылки на DAO, как в показанной выше процедуре, не гарантирует работоспособность кода - может возникать куча заморочек, связанных с тем, какая библиотека подключена в данный момент и у какой выше приоритет.

Поискав (и не найдя) ответ по всему Интернету я догадался, наконец, описать RecordSet как Variant, то есть, без указания типа:

 Dim rs

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

Private Sub КнопкаДобавить_Click()
If ([Forms]![Связи]![СписокКатегории] < 1) Or ([Forms]![Связи]![СписокОбъекты] < 1) Then
 MsgBox "Выберите категорию и объект", vbOKOnly, "Сообщение"
 Exit Sub
Else
 Dim rs
 Dim l1, l2 As Integer
 l1 = (Forms![Связи]![СписокКатегории])
 l2 = (Forms![Связи]![СписокОбъекты])
 Dim s As String
 s = "SELECT Связи.[Код категории],Связи.[Код объекта] FROM Связи WHERE (([Код категории]=" & l1 & ") AND ([Код объекта]=" & l2 & "))"
 Set rs = CurrentDb.OpenRecordset(s)
 If rs.RecordCount = 0 Then
   DoCmd.GoToRecord , , acNewRec
 Else
  MsgBox "Уже есть такая связь", vbOKOnly, "Сообщение"
  Exit Sub
 End If
End If
End Sub

Нам остается отключить для формы встроенную навигацию (свойства "Область выделения" и "Кнопки перехода" со вкладки "Макет" окна свойств формы), добавить с помощью мастера свою навигацию и получить работающее приложение.

Кстати, стандартные сообщения для кнопок навигации, генерируемые Access, можно заменить на свои более осмысленные, например, код

MsgBox Err.Description

на

 MsgBox "Достигнуто начало базы данных", vbOKOnly, "Сообщение" 

Это пример можно скачать и доделать, ведь область применения отношений "многие-ко-многим" так же широка, как сами эти отношения.

Скачать редактор связи "многие-ко-многим": base_mn.zip, 32 Кб

гостевая; E-mail