2. Разработка приложения пользователя с использованием vba

2.1. Введение

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

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

• язык VBAявляется объектно-ориентированным языком;

• модель программирования в Accessявляется событийно управляемой.

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

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

2.2. Модули Access

Модули являются объектамиAccess, такими же, как таблицы, запросы, формы и т.д.

Модуль — это набор подпрограмм и функций, написанных в VBA и собранных в одну программную единицу.

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

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

Чтобы создать стандартный модуль или модуль класса, нужно: 1) выбрать команды Модули  Создать; 2) создать необходимые процедуры , 3) сохранить модуль.

2.3. Классы и семейства

Объекты - это формы, отчеты, таблицы в Access.

Класс – описание совокупности однотипных объектов. Объект представляет собой экземпляр определенного класса.

Свойство – отдельная характеристика объекта или класса, например, свойство формы Вывод на экран (Visible) может иметь значение True или False.

Метод – представляет собой процедуру (или функцию) объекта или класса. Например, объект Form имеет метод Refresh. Вызов этого метода позволяет обновить данные в форме.

Семейство – упорядоченный набор экземпляров одного класса.

2.4. Объектные модели Microsoft Office

В VBA определены объектные модели для каждого семейства Microsoft Office и объектные модели, общие для всех приложений. Каждое приложение Microsoft Office представляет собой объектные модели в виде библиотеки объектов, которая может быть использована другими приложениями:

• библиотека объектов Microsoft Access ( Microsoft Access Object Library 10.0);

• библиотека объектов доступа к данным (Microsoft DAO 3.6 Object Library);

• библиотека объектов ActiveX ( Microsoft ActiveX Data Objects 2.6 – ADO);

• библиотека объектов Visual Basic (Microsoft Visual Basic for Application);

• библиотека объектов Microsoft Office (Microsoft Office 10.0 Object Library.

2.5. Модели объектов Microsoft Access

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

На верхнем уровне иерархии располагается объект Application, позволяющий дать информацию об исполняемом приложении. Application включает: семейства форм и отчетов (Forms, Reports), ссылок (References), модулей (Modules), метод Docmd и др.

Семейство форм и отчетов – это семейство открытых форм и отчетов. Он включают семейство элементов управления (Controls) и свойств (Properties).

Объект Docmd располагает методами, которые позволяют выполнить макрокоманды Access из программы VBA.

Обращение к объектам библиотек из процедур и функций Access осуществляется по установленным на них ссылкам. Список установленных ссылок можно просмотреть и изменить в окне, которое открывается командами Tools  References в окне редактора VBA.

2.6. Модели объектов доступа к данным DAO и ADO

Стандарты DAO и ADO обеспечивают программиста похожим набором инструментов управления. DAO – это предыдущий, более ранний стандарт средств управления базой данных. Стандарт ADO представляет более мощные инструменты и его применение приводит к сокращению программного кода. Ниже представлены фрагменты листингов программного кода с библиотекой объектов доступа к данным DAO и ADO для решения одной и той же задачи – открытие записей таблицы Отдел в текущей базе данных.

__________________________________________________

Sub DAO_пример ()

Dim Db As Database

Dim Rs As DAO.Recordset

Set Db As CurrentDb()

Set Rs = DB.OpenRecordset(“Отдел”)

__________________________________________________

Sub ADO_пример ()

Dim Rs As New ADODB.Recordset

Rs.Open “Отдел”, CurrentProject.Connection, adOpenKeySet, _

adLockOptimistic

__________________________________________________

Пояснение записей в листингах:

Объект класса ADODB – это OLE-сервер. Его интерфейс может использоваться любой программой, написанной на VBA.

Connection – это блок информации, указывающий Access, как общаться с базой данных. Благодаря Connection можно общаться с базами данных, созданных фирмами Microsoft, MS-SQL Server, Oracle и др.

CurrentProject.Connection – указывает на текущую базу данных.

adOpenKeySet, adLockOptimistic - параметры, указывающие на тип курсора и тип блокировки соответственно.

Константы, которые показывают, в какой библиотеке они описаны имеют следующие префиксы: ac, db, ad, vb. Например, ac – библиотека Access и т.д.

2.7. Ссылки на объекты

Для обращения к объекту существует одно общее правило: нужно проследить путь в иерархии объектов, начиная с объектов самого верхнего уровня. Главным в иерархии объектов является объект Application. Он содержит ссылки на все остальные объекты и семейства объектов Microsoft Office (предоставляет к ним доступ). Объект Application представляет активное приложение Microsoft Access и содержит все методы и свойства, которые можно применять к активному приложению. Например, если нужно обратиться к форме, входящей в семейства Forms и имеющей название “Сотрудники”, следует написать следующее выражение:

Application.CurrentProject.Forms (“Сотрудники”)

Это выражение представляет собой ссылку на объект – форму «Сотрудники». Вместо этого выражения можно написать сокращенно:

Application.Forms (“Сотрудники”) или Forms! Сотрудники

Чтобы сослаться на элемент управления Код сотрудника в форме Сотрудники нужно записать

Forms! Сотрудники![Код сотрудника]

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

Если форма Сотрудники открыта и является текущей, то ссылка на элемент управления [Код сотрудника] запишется так: Me![Код сотрудника]

2.8. Свойства и методы объектов

Каждый объект имеет свойства, которые являются его характеристиками, и методы, позволяющие управлять поведением этого объекта. Например, чтобы установить Свойство Value (Значение) элемента управления Цена формы Товары нужно записать:

Forms!Товары!Цена.Value = 345,25

Чтобы получить свойство, можно записать

S=Forms!Цена

Здесь переменной S присваивается значение свойства Value семейства Forms (Value при записи программы можно опускать).

Метод в VBA обозначается так же, как и свойство. Однако в отличие от свойств, методы могут иметь аргументы. Например, в следующем коде VBA применяется метод OpenForm объекта DoCmd :

DoCmd.OpenForm “Сотрудники”, acForm

Объект DoCmd – это специальный объект, который позволяет в программах VBA выполнять макрокоманды. Имя каждой макрокоманды является методом этого объекта.

2.9. Применение метода DoCmd в программах VBA

VBA позволяет создавать процедуры, состоящих из одних макрокоманд. Прямые эквиваленты существуют только для некоторых из них. Для выполнения макрокоманд используется метод DoCmd. Структура метода:

DoCmd.макрокоманда [аргумент],…

К макрокомандам, которые выполняются из среды VBA, относятся такие: ApplyFilter(Применит фильтр),Close(Закрыть),FindNext(Следующая запись), FindRecord (НайтиЗапись),HourGlass(Песочные часы),Maximize(Развернуть),Minimize(Свернуть),OpenForm(Открыть Форму),OpenQuery(Открыть Запрос),OpenReport(ОткрытьОтчет),ShowToolBar(ПанельИнструментов).

В данной работе будут рассмотрены процедуры, с макрокомандами открытия, закрытия формы и применить фильтр для данных в форме.

Открытие формы. Для открытия формы используется макрокомандаOpenForm. Структура метода DoCmd при этом такова:

DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs

FormName – имя формы из текущей базы данных, которую нужно открыть.

View – определяет тип открытия формы. Используется одна из следующих констант:AcNormal– открывает форму в виде Форма (по умолчанию);AcDesign -открывает форму в виде Конструктор;AcFormDs – открывает форму в виде Таблица данных.

FilterName– имя запроса из текущей базы данных.

WhereCondition – SQL-выражение (словоWHEREопускается), определяющее записи, которые следует вывести на экран (например, “Страна = ‘Россия' “).

DataMode – определяет режим ввода данных, в котором открыта данная форма, на основании следующих констант:AcAdd– открывает форму в режиме Добавление (можно вносить новые записи, но нельзя редактировать имеющиеся);AcEdit– открывает форму в режиме Редактирования (можно вносить новые записи и редактировать имеющиеся);AcReadOnly– открывает форму в режиме Только просмотр ( ввод и редактирование записей невозможно).

WindowMode– определяет режим окна, в котором выводится форма. Используются константные значения:AcNormal– открывает форму в видеОбычный. Используется по умолчанию;AcHidden– открывает форму вскрытом виде;AcIcon– открывает формув виде свернутой пиктограммы.

OpenArgs – строковое выражение, определяющее один или несколько аргументов. Эти аргументы могут использоваться в программе, написанной на VBA, в качестве модификаторов открытия формы.

Пример 1.Записать процедуру-функцию для открытия формы “Продажи” и вывести записи, удовлетворяющие следующим условиям: “Заказано” -больше 18, “Дата заказа” –2005г., Форму открыть в видеФорма. Режим ввода данных –Редактирование.

Public Function Форма1()

DoCmd.OpenForm _

FormName:=”Продажи”, _

Wherecondition:=”Заказано> 18 and year(Дата_Заказа) =2005”, _

view:=acNormal, _

datamode:=acEdit

End Function

Пример 2. Имеется форма “Выбор студента” с полем со списком, где даны города (рис.2.1). Имеется форма "Студенты”, где даны города и фамилии студентов (рис.2.2.) В форме “Выбор студента” имеется кнопка “Выбор студента по городу”. В поле со списком выбирается город, и, нажав на кнопку, выбираются только те студенты, кто живет в данном городе. Ниже приведена простейшая процедура для кнопки.

Private Sub Кнопка3_Click()

DoCmd.OpenForm "Студенты", _

wherecondition:= "[Города]=[Forms]![Выбор студента]![Города]"

End Sub

Рис.2.1. Форма “Выбор студента”

Рис. 2.2. Форма “Студенты”

Закрытие формы. Для закрытия формы используется макрокомандаClose. Cтруктура методаDoCmd при этом такова:

DoCmd.Close ObjectType, ObjectName, Save

ObjectType – тип закрываемого объекта. Для закрытия формы используется аргументacForm. К прочим аргументам относятсяacTable, acQuery, acReport, acMacro, acModule. Если этот аргумент упущен, то производится закрытие активного объекта.

ObjectName– имя объекта, который необходимо закрыть.

Save– константа, определяющая процедуру закрытия объекта, в котором содержатся не сохраненные данные: (предлагает пользователю сохранить внесенные изменения; используется по умолчанию),acSaveYes(автоматическое сохранение внесенных изменений),acSaveNo(не сохранять внесенных изменений).

Пример 3. Записать процедуру - подпрограмму для закрытия формы ”Продажи” с автоматическим сохранением внесенных изменений.

Public Sub Закрыть ()

DoCmd.Close acForm, “Продажи”, acSaveYes

End Sub

Примечание. Для закрытия активного объекта достаточно в программе написать:

DoCmd.Close

Фильтрация данных в форме

Для фильтрации данных формы используется макрокомандаApplyFilter. Cтруктура методаDoCmd при этом такова:

DoCmd.ApplyFilter “ Имя фильтра”,”Условие отбора”

Имя фильтра – имя запроса;

Условие отбора– выражениеSQLбезWhere.

Пример 4. В форме “Отдел кадров” (рис. 2.3) процедураSub для фильтрации данных по“Дата_зачисл”такова:

Sub Фильтрация_по_дате()

DoCmd.ApplyFilter "", "[Дата_зачисл]>[Введите дату]"

End Sub

При запуске программы в окне диалога выводится приглашение на ввод даты.

Рис.2.3. Форма “Отдел кадров”

2.10. Работа с объектами доступа к данным

Чтобы работать с данными в базе данных, нужно сначала выполнить соединение с этой базой данных, то есть получить на нее ссылку. В модели DAOэто будетDatabase, а в моделиADO–Connection. Когда ссылка получена, можно обращаться к семействам и объектам этой базы данных. Примеры соединения даны выше в разделе“Модели объектов доступа к данным DAO и ADO”.

Типы данных объектов определяются ссылками на объекты соответствующих моделей, хранящихся в библиотеках. Наиболее распространенными объектными типами, поддерживаемые AccessVBA, являются:

- объектные типы данных Accessиз библиотекиAccess10.0 –Form, Report, Control– соответственно присваиваются объектам, формам, отчетам и их элементам управления;

- объектные типы данных DAO – Database, TableDef, Field, DAO.Recordset, QueryDef. Тип данных Database присваивается объекту База данных, QueryDef – запросу (выражение SQL), TableDef – таблице, Dao.Recordset – набору записей в таблице;

- объектные типы данных ADO – Connection заменяет тип данных Database в DAO, Сommand - QueryDef, ADODB - DAO.Recordset.

Рассмотрим примеры описания объектных перменных:

Dim FF As Form, Fam As Control, Bd As Database

Присваивание значения объектной переменной

Оператор Dimтолько описывает переменную. Для того, чтобы переменная содержала ссылку на объект, необходимо с помощью оператораSetприсвоить этой переменной ссылку на объект соответствующего типа. Тип объектной переменной должен соответствовать объекту, который ей присвоен. Например:

1) Set FF = Forms![Список студентов] - создает переменнуюFFдля формы Список студентов;

2) Set Fam = Forms![Список студентов]!ФИО - создает переменнуюFamвэлементе управления формы Список студентов;

3) Set Bd = CurrentDB() – присваивает переменной Bd значение текущей базы данных;

4) Set Rs = db.OpenRecordset("Продажи") -– присваивает переменной Rs значение записи таблицыПродажи текущей базы данных;

5) Set Tabl = Bd.CreateTableDef(“Сотрудники”) – создает в текущейBd таблицу с именемСотрудники и сохраняет ссылку на этот объект в переменнойTabl;

6. Set Fld = Tabl.CreateField(“ФИО”, dbText)– создает поле и присваивает ссылку на него объектной переменнойFld.

7) Set имя объектной переменной = New – создается новый экземпляр объекта.

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

Наборы записей в таблицах

После соединения с базой данных и открытия таблицы создается новый набор записей и, теперь основная работа, производится с объектом Recordset.

Существует три типа наборов записей:

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

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

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

Свойства объекта Recordset

BOF - возвращает значениеTrue, если позиция текущей записи находится перед первой записью.

Bookmark- устанавливает или возвращает значениеVariant, которое идентифицирует текущую запись.

EOF - возвращает значениеTrue, если позиция текущей записи находится ниже последней записи.

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

RecordCount– возвращает количество записей в наборе.

Fields.Count – возвращает количество полей в таблице.

Fields(i).Name – возвращает имяi-го поля в таблице.

Sort- возвращает или устанавливает порядок сортировки в наборе записей динамического и простого типов.

Методы объекта Recordset

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

Close- закрывает набор данных.

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

Update- обновляет набор записей.

Edit- копирует текущую запись набора табличных или динамических записей в буфер копирования для дальнейшего редактирования (только вDAO).

Move- перемещает указатель текущей записи на определенное количество записей. Этот метод имеет следующий синтаксис:

Объект.Moveстрока

Объект- объект типаRecordset.

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

MoveFirst, MoveLast, MoveNext, MovePrevious - перемещает текущую запись в первую, последнюю, следующую или предыдущую позицию текущего набора записей.

Работа с набором записей таблицы. Перед написанием программ нужно открыть

окно текстового редактора VBAкомандами Модули  Создать. В модуле сделать ссылки на библиотеки доступа к даннымDAOиADO. Для этого выполнить командыTools  Referencesи выбрать библиотеки объектов так, как это показано на рис.2.4.

Рис. 2.4. Окно ссылок для подключения библиотек.

Пример 1. Пусть имеется форма "Товары", созданная на основе одноименной таблицы в текущей базе данных. Необходимо подсчитатьcуммарную стоимость кофе.Для этого в форме создается элемент управления с именемСтоимость_кофеи кнопкаРасчет стоимости (рис.2.5),под которую следует написать программу.

Private Sub Кнопка17_Click()

Dim db As Database