Глава 8. Использование подчиненной формы (создание формы регистрации продаж)

Перед созданием формы регистрации продаж уясним себе задачи, которые должны решаться при помощи этой формы. После оформления приема товаров они поступают в розничную торговую сеть. Информация о магазинах нашей фирмы находится в таблице Магазины, а для ее ввода и просмотра удобнее использовать форму Магазин. Будем считать, что информация о проданных товарах поступает из магазинов не чаще одного раза в день. Эта информация и будет заноситься в базу данных с использованием разрабатываемой формы. А именно, здесь должны быть предусмотрены: отметка о продаже, возврате или списании; количество единиц реализованного товара, дата продажи и номер магазина. Кроме того, чтобы ввести данную информацию по каждому товару, нужно отыскать его в базе и сделать это по возможности быстро.

Чтобы идентифицировать товар, нам потребуется следующая информация о нем: наименование, категория, розничная цена, поставщик и дата приема. Будем выводить также некоторую дополнительную информацию (например, общее количество товара и подсчитывать его остаток), а также обеспечим возможность поиска нужной информации по вводимому критерию. Полную информацию о товаре можно получить из запроса Накладная_прием, а информацию о продажах будем фиксировать в таблице Продажи. Если ранее это не сделано, дополним запрос Накладная_прием вычисляемым полем Всего:[Кол_во_в_упак]*[Кол_во_упак], дающим общее количество единиц товара. Формула весьма простая, однако, для написания этого выражения можно воспользоваться построителем выражений, если вы боитесь ошибиться в написании имен полей.

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

При разработке такой формы воспользуемся мастером создания форм. Выполним следующие действия:

Размеры элементов управления, их размещение и надписи откорректируем в режиме конструктора.

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

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

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

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

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

Если использовать формулу для подсчета остатка товара в том виде, в котором она написана выше, окажется, что для товара, который не имел продаж, остатка нет. Это кажется странным, поскольку естественно ожидать, что остаток в этом случае должен равняться исходному количеству. Однако ничего странного здесь нет, если иметь в виду тот факт, что арифметические операции со значением Null дают результат Null. Тогда, если продаж некоторого товара еще не было, то для него функция Dsum вернет значение Null и, как следствие, получим: [Всего] – Null = Null. Чтобы исправить этот недостаток, воспользуемся функцией Nz, которая имеет следующий синтаксис:

Функция Nz возвращает нуль, пустую строку ("") или другое значение, указанное параметром Представление, если переменная типа Variant имеет значение Null. В противном случае будет возвращено значение аргумента Variant. Например, эту функцию используют для преобразования значения Null в другое значение при работе с выражениями, не допускающими NULL-значений.

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

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

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

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

Здесь нарушение условия ввода мы сопровождаем соответствующим сообщением (используем процедуру msgbox) и восстанавливаем текущую запись, что достигается применением метода Undo (отменить) к форме. Для ссылки на текущую форму используется служебное слово Me, а для ссылки на основную форму из процедур подчиненной – свойство Parent.

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

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

Фильтрация данных (анализ продаж)

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

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

Среда Access предоставляет широкие возможности фильтрации данных. Это:

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

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

Поиск записи, содержащей нужный образец для фильтрации данных, может потребовать таких усилий, которые, по существу, сведут на нет данный способ фильтрации. Вместо поиска записи можно воспользоваться командой контекстного меню Фильтр для:, в поле которой нужно ввести образец сравнения и нажать клавишу ENTER. На рис. 8.4 показано использование этой команды для фильтрации товаров, поступивших на склад 21 апреля 1998 года, на форме Прием товаров.

Часто бывает удобнее применять фильтрацию «наоборот», т.е. указать, что не должно содержаться в том или ином поле. Для этих целей служит команда Записи\Фильтр\Исключить выделенное, которая имеется также в контекстном меню.

Применение простого фильтра состоит из двух этапов:

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

Применение фильтра выполняется с помощью команды Фильтр\Применить фильтр.

Расширенный фильтр (команда Записи\Фильтр\Расширенный фильтр) использует запрос в качестве условий отбора записей. Это дает возможность применять вычисляемые поля и другие возможности запроса для формирования критериев фильтрации данных. После формирования критерия, как и в случае простого фильтра, нужно выполнить команду Фильтр\Применить фильтр.

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

Вспомним команду DoCmd.OpenReport, которая применялась нами для печати отчета-накладной в событийной процедуре нажатия кнопки Печать накладных на форме Прием товаров (смотри «Печать отчетов с отбором записей» в главе 6). Если в базе данных имеется запрос, подходящий для фильтрации данных в отчете, его имя в качестве строкового значения можно использовать в качестве третьего аргумента этой команды.

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

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

Фильтрация записей средствами VBA

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

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

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

В соответствии с вышесказанным поместим на форму поля Наименование_ и Цена (некоторые имена полей содержат символы подчеркивания «_», чтобы не было путаницы с именами полей таблиц), а также поле со списком Категория_. Если этой информации окажется недостаточно для однозначного определения записи, можно будет добавить и другие поля.

Источником строк для списка Категория_ является таблица Категория товара.

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

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

Crit = "" 'Строковая переменная crit будет содержать критерий

Сформированный критерий содержится в строковой (тип данных String) переменной Crit. Если считать, что все поля критерия будут заполнены, то построение критерия относительно просто. Однако если пользователь будет вводить информацию лишь в отдельные поля, то алгоритм несколько усложняется. Разберем его несколько подробней.

Сначала проверяется первый элемент (список категорий товара; имя элемента – Категория_). Если элемент списка выбран (значение не пусто - не Null), то формируется критерий, а именно,

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

При проверке выбора значения используется встроенная функция IsNull с отрицанием Not.

Затем так же проверяется поле наименования товара (использовано имя Наименование_). Здесь, однако, имеются некоторые отличия. Во-первых, необходимо проверить, сформулирован ли ранее критерий или нет. Если да (Crit > ""), то к нему добавляется (союз AND) следующий; если нет, то критерий формируется так же, как и для предыдущего поля. Во-вторых, поскольку неразумно вводить наименование полностью (трудно запомнить длинное наименование), то в этом случае используется не точное совпадение с образцом («=»), а оператор Like (похоже на). Оператор Like позволяет искать наличие введенной последовательности символов в поле Наименование.

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

Заметим, что можно сравнивать и десятичные числа, используя функцию преобразования типов Cdbl.

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

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

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

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