Предупреждение дублирования записей при вводе их из формы

Предупреждение дублирования записей при вводе их из формы

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

А теперь покажем, как решить ту же задачу средствами VBA.

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

Откроется окно Построитель. В нем вы можете выбрать один из трех вариантов обработки свойства (см. рис. 12.4):

• построитель выражений;

• построитель макросов;

• построитель программ.

Рис. 12.4

О построителе выражений мы поговорим несколько позже; построитель макросов – это конструктор, уже рассмотренный в предыдущей главе. Наконец, построитель программ открывает окно модуля, которое было выведено на рис. 12.2, хотя и содержало другую информацию. Чтобы начать создание процедуры, выберите именно эту третью опцию. Тогда в окне свойств поля Название_фирмы_ установится режим Процедура обработки событий, как показано на рис. 12.5, и откроется окно модуля (см. рис. 12.6). Оно примечательно следующим.

Рис. 12.6

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

Это:

• Private Sub Название_фирмы_LostFocus ();

• End Sub.

Первый оператор – имя процедуры Sub, которое автоматически присваивается ей в соответствии с именем поля и свойством (последние объединяются символом подчеркивания). Область действия процедуры (Private) – данный модуль класса (форма Фирмы). Второй оператор, End Sub, закрывает процедуру.

Дальше начинается заполнение создаваемой процедуры конкретными операторами.

Обработка ошибок выполнения

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

• во время работы приложения некоторые действия могут войти в противоречие со стандартами программы. Например, при попытке открыть таблицу, которая была удалена пользователем, возникнет ошибка;

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

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

Добавляя процедуру обработки, следует учитывать способ передачи управления процедуре при возникновении ошибки. Первое, что надо сделать для задания маршрута передачи управления, – это подключить обработчик ошибок путем включения некоторой формы инструкции On Error в процедуру. Инструкция On Error передает управление процедуре обработки события данной ошибки.

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

Обработчик ошибок определяет, какие действия будут выполняться при возникновении ошибки в процедуре. Например, может быть завершено выполнение процедуры или исправлены условия и произведен повторный запуск. Инструкции On Error и Resume определяют способ передачи управления при возникновении ошибки.

Эта инструкция служит для подключения и отключения процедуры обработки ошибок. Если такая процедура подключена, то при возникновении ошибки ей передается управление.

Есть три формы инструкции On Error:

• On Error GoTo метка;

• On Error GoTo 0;

• On Error Resume Next.

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

On Error GoTo Err_Название_фирмы_LostFocus_Click

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

Инструкция On Error GoTo 0 отключает обработку ошибок внутри процедуры. Даже если строка с номером 0 существует, она не запустит программу обработки ошибок. Если инструкции On Error GoTo 0 в процедуре нет, то обработка ошибок отключается автоматически, как только программа завершится.

Если вы выбираете форму On Error Resume Next, то строка, вызвавшая ошибку, пропускается и управление передается следующей строке. Выполнение программы не прерывается. Удобство этой инструкции состоит в том, что она позволяет проверять свойства объекта Err сразу за строкой, в которой ожидается возникновение ошибки, а также устранять ошибки внутри самой процедуры, без помощи обработчика.

Err_Название_фирмы_LostFocus_Click: MsgBox Err.Description

Последний оператор позволяет вывести сообщение об ошибке с указанием ее типа и обычно задается в конце текста основной процедуры. Инструкция Resume

Инструкция Resume возвращает управление из обработчика ошибок в процедуру. Эту инструкцию следует включать в обработчик, если необходимо передать управление в определенную строку процедуры (см. ниже). Однако инструкция Resume не является обязательной: если она отсутствует, то процедура может завершиться, когда обработчик выполнит свои функции.

Есть три формы инструкции Resume:

• Resume или Resume 0 – возвращает управление строке, при выполнении которой произошла ошибка;

• Resume Next – возвращает управление той строке, которая непосредственно следует за строкой, вызвавшей ошибку;

• Resume метка – передает управление строке, которая задана в качестве значения аргумента метка. Он содержит номер строки или метку.

Инструкции Resume и Resume 0 обычно применяются, когда пользователю необходимо внести исправления. Например, система запросила имя таблицы для открытия, а вы ввели название несуществующего объекта. В таком случае запрос можно повторить и продолжить выполнение программы с инструкции, вызвавшей ошибку, внеся исправления.

Инструкция Resume Next используется для обработки ошибок внутри самого обработчика. При этом команда, которая вызвала ошибку, больше не будет выполняться, когда управление вернется в процедуру.

Resume Exit_Название_фирмы_LostFocus_Click Выход из процедуры Включая в создаваемую программу процедуру обработки ошибок, необходимо предусмотреть такой путь выхода из нее, чтобы впоследствии обработчик активизировался только при возникновении ошибки. Процедура выхода отмечается меткой строки точно так же, как и процедура обработки ошибок. Если ошибки не произойдет, то после выполнения программы запустится процедура выхода. Если же ошибка случится, то сначала выполнится обработчик ошибок, а затем управление будет передано в процедуру выхода. Она содержит инструкцию Exit:

Exit Название_фирмы_LostFocus_Click

Итоговый синтаксис программы обработки ошибок выведен в окне модуля (см. рис. 12.7).

Теперь перейдем к созданию самой программы с помощью VBA – как вы помните, такова была наша изначальная цель.

Функция создаваемой программы

Прежде всего зададимся вопросом, зачем нужна создаваемая процедура.

Когда мы включаем новую запись в форму Фирмы, то должны проверить, не дублирует ли содержимое поля Название_фирмы_ те одноименные поля, которые уже введены в форму в предшествующих записях. Тестирование заключается в следующем. Для каждой новой вводимой записи подсчитывается количество совпадений поля Название_фирмы_ с таким же полем для уже введенных записей. Если это число равно 1, то программа извещает пользователя о наличии дубликата, обнуляет поля текущей записи и ждет ввода очередной. Если количество совпадений равно 0, то процедура:

1. Вводит значение в базу данных.

2. Сообщает об этом пользователю.

3. Устанавливает следующее значение кода фирмы (в поле Код фирмы предусмотрена последовательная нумерация).

4. Переходит к новой записи.

5. Активизирует поле Название фирмы.

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

Dim Cod As Integer Dim answer As Integer

Теперь определите выражение, которое формирует количественное значение переменной answer. Выражение будет иметь такую структуру:

answer = DCount(«[Haзвание фирмы]», «Фирмы», «[Название фирмы] = Forms![Фирмы]![Название фирмы]»)

Здесь необходимы пояснения. Функция DCount Функция DCount возвращает число записей в заданном наборе (подмножестве) записей. Ее синтаксис таков:

DCount(вырaжeниe, набор[, условие])

Здесь:

• выражение – определяет поле, для которого производится подсчет значений. Данный аргумент может задаваться строковым выражением, определяющим поле в таблице или запросе, или представлять выражение, задающее выполнение вычислений над данными, содержащимися в поле. Допускается использовать в аргументе выражение имя поля в таблице или элемента управления в форме, константы, а также имя встроенной или определяемой пользователем функции. Не допускается в аргументе выражение других статистических функций по подмножеству или статистических функций SQL;

• набор – строковое выражение, которое определяет набор записей, образующих подмножество. Может представлять имя таблицы или запроса;

• условие – необязательное строковое выражение, ограничивающее диапазон данных, для которых подсчитывается число значений. Например, аргумент условие часто является эквивалентом предложения WHERE инструкции SQL, но без ключевого слова WHERE. Если аргумент условие опущен, DCount выполняет расчеты над полем, заданным в аргументе выражение, для всего набора записей. Любое поле, указанное в аргументе условие, должно принадлежать подмножеству, заданному аргументом набор; в противном случае функция DCount возвращает значение Null.

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

В нашем случае выражение – это имя поля "[Название фирмы]". (Вспомним: когда в тексте строки есть пробелы, она заключается в квадратные скобки. Кавычками полагается обрамлять текстовую строку.) Значением аргумента набор является форма Фирмы, а аргумента условие – "[Название фирмы] = Forms![Фирмы]![Haзвaниe фирмы]". Последнее означает, что здесь сравнивается содержимое поля текущей записи с содержимым поля открытой формы (в правой части выражения Forms – класс форм, [Фирмы] – имя какой-либо фирмы из этого класса, [Название фирмы] – конкретное поле формы). Восклицательные знаки указывают на то, что следующие за ними элементы определяются пользователем. В данном случае речь идет о ссылке на открытую форму и на поле в ней.

Логическая конструкция If…Then…Else

Так как функция Dcount возвращает значение переменной answer (0 или 1), то и логическая проверка выполняется относительно этой переменной. Утверждение, подлежащее тестированию, – answer = 1. Если оно верно (дубликат действительно обнаружен), то логическое выражение принимает значение True (Истина). Затем сразу после проверяемого утверждения выполняется ряд операторов:

1. Исполняется команда MsgBox Prompt, и в результате на экране появляется сообщение «Значение уже содержится в файле и повторно не вводится».

2. Запускается макрос, который стирает значения всех полей текущей записи, кроме поля Код фирмы: в нем сохраняется текущее значение кода. Макрос имеет рабочий номер 32. (В выборе этой цифры нет какого-либо специального умысла, и макрос можно назвать как угодно.) Чтобы запустить его из программы VBA, задайте команду DoCmd.RunMacro «Макрос32».

Если проверяемое утверждение неверно (answer не равно 1), значит, дубликат не обнаружен. Логическое выражение принимает значение False (Ложь), и после инструкции Else выполняется ряд операторов:

1. Запускается макрос, запоминающий запись (макрос2);

2. Запускается макрос, реализующий переход к новой записи (макрос1). В обоих случаях аналогично предыдущему используется команда макрос1 DoCmd.RunMacro" Макрос№";

3. С помощью команды MsgBox Prompt выдается сообщение «Запись введена».

DMax(вырaжeниe, набор[, условие])

Здесь:

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

– набор – строковое выражение, определяющее набор записей, образующих подмножество. Может содержать имя таблицы или запроса;

– условие – необязательное строковое выражение, ограничивающее диапазон данных, для которых определяется минимальное или максимальное значение поля. Если аргумент условие опущен, DMax выполнит действия над полем, заданным в аргументе выражение, для всего набора записей. Любое поле, указанное в аргументе условие, должно принадлежать подмножеству, заданному аргументом набор; в противном случае функция DMax возвращает значение Null.

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

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

6. Запускается макрос14, который передает фокус следующему полю – Название фирмы.

7. Заканчивается логическая конструкция If.

Проверка завершенной процедуры

Private Sub Название_фирмы_LostFocus()

On Error GoTo Err Название_фирмы_LostFocus_Click

Dim Cod As Integer

Dim answer As Integer

answer = DCount(«[Haзвaниe фирмы]», «Фирмы», «[Название фирмы] = Forms![Фирмы]![Haзвaниe фирмы]»)

If answer = 1 Then

MsgBox Prompt:="Значение уже содержится в файле и повторно не вводится"

DoCmd.RunMacro «Макрос32»

Else

DoCmd.RunMacro «Макрос2»

DoCmd.RunMacro «Макрос1»

MsgBox Prompt:="Запись введена"

Cod = DMax(«[Kод фирмы]», «Фирмы») + 1

Forms![Фирмы]![Kод фирмы] = [Cod]

DoCmd.RunMacro «Макрос14»

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acMenuVer70

Exit Название_фирмы_LostFocus_Click:

Exit Sub

Err Название_фирмы_LostFocus_Click:

MsgBox Err.Description

Resume Exit Название_фирмы_LostFocus_Click

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

Теперь проверим, как работает написанная процедура. Откройте форму Фирмы и введите в поле Название фирмы имя организации, которое заведомо содержится в базе данных, например ORION (см. рис. 12.9).

Не заполняйте остальные поля: в данном случае важно просто проверить, дееспособна ли созданная программа. Чтобы ввести информацию в базу данных, щелкните по клавише Enter. Появится сообщение, показанное на рис. 12.10. Форма остается в том же состоянии, код новой записи не изменился, и все готово к вводу другой новой записи.

Добавьте ее, задав в качестве имени фирмы уникальное имя ORION1. Снова воспользуйтесь клавишей Enter для ввода записи. В результате появится другое сообщение (см. рис. 12.11). Как видите, добавление этой записи база данных «восприняла» совершенно нормально (рис. 12.12).

Рис. 12.12

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

Данный текст является ознакомительным фрагментом.