ГлавнаяРазноеAccess в vba

Различие между использованием .text и .value в VBA Access. Access в vba


Программирование на VBA в СУБД Access 2003

Основные сведения о системе программирования VBA

2011-02-22

Автор: Владимир Ткаченко

Источник: Обучение в интернет

Небольшую и несложную базу данных или приложение Access можно создать в СУБД Access без использования языков программирования SQL и Visual Basic. В СУБД Access имеется достаточно средств (различных мастеров и конструкторов) для визуального проектирования таблиц, запросов, форм и отчетов. При этом в качестве средства для отыскания необходимой информации в базе данных Access можно использовать QBE - запрос по образцу, а не язык запросов SQL.

Для решения некоторых задач автоматизации приложений Access можно использовать макросы вместо языка программирования Visual Basic (например, при создании главной и подчиненной кнопочной формы БД). Но создание коммерческих баз данных в СУБД Access невозможно без применения визуального языка программирования Visual Basic и языка запросов SQL.

Для автоматизации  действий над объектами в Microsoft Access и в других приложениях Microsoft Office применяются макросы и модули. Макросы - это небольшие программы на языке макрокоманд (языке сценариев). Модули - это наборы описаний и процедур на языке программирования VB для приложений, т.е. модули - это объекты, содержащие программы на языке Visual Basic.

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

Программирование на VBA в приложениях Word, Excel, PowerPoint обычно применяют пользователи для автоматизации своей деятельности, связанной с обработкой документов. Использование языка программирования VBA для создания функций пользователя в Excel рассмотрено в разделе Основы офисного программирования. Программирование на VBA в СУБД Access используют в основном разработчики (программисты) в процессе создания приложений (различных баз данных, БД), с которыми работают пользователи.

Для программирования в Access используется не приложение Visual Basic, а встроенная в Microsoft Office система программирования Visual Basic for Applications (VBA - Visual Basic для приложений). Система программирования VBA является неотъемлемой частью приложений Microsoft Office и предназначена для визуального программирования в таких приложениях как Word, Excel, PowerPoint, Access и т.д. В VBA языком программирования является VB, а инструментальная среда программирования реализована в виде редактора VB, который может активизироваться из любого приложения MS Office. Редактор Visual Basic является отдельным приложением, поэтому можно переключаться между приложениями Microsoft Office и редактором Visual Basic клавишами "Alt+F11".

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

Стандартные модули содержат общие процедуры, которые не связаны с конкретным объектом (формой, отчетом). Стандартный модуль - это модуль, в который помещают процедуры Sub и Function, которые должны быть доступны для всех процедур в данном приложении. Стандартные модули могут использоваться другими приложениями Access, так как в общих процедурах нет ссылок на конкретные объекты данного приложения (формы, отчеты). Кроме общих процедур, в стандартных модулях могут содержаться глобальные переменные и функции, а также объекты, которые доступные из других объектов базы данных.

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

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

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

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

Третий способ осуществляется путем обработки некоторых событий связанных с формой или каким-либо элементом управления формы, находясь в режиме Конструктора форм или отчетов. Для этого необходимо открыть окно редактора Visual Basic, щелкнув на команду "Программы" в Построителе. Построитель вызывается командой "Обработка событий" из контекстного меню объекта, например кнопки.

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

Чтобы создать отдельный модуль класса или стандартный модуль, надо выбрать пункт Модуль класса или Модуль в меню Вставка. Стандартный модуль можно создать, например, путем преобразования макроса. Модули отображаются в окне базы данных на вкладке Модули. На рисунке представлено окно базы данных Access (на вкладке Модули) в нем находятся модули объектов, три стандартных модуля и один модуль класса.

Для просмотра процедуры в стандартном модуле или в модуле класса надо выделить требуемый модуль в окне базы данных на вкладке Модули и щелкнуть на пиктограмме Программа на панели инструментов в главном окне Microsoft Access или щелкнуть на кнопке Конструктор в окне базы данных. Откроется редактор Visual Basic, на панели редактора кода которого отображаются процедуры.

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

Существует две группы действий на события. Действия первой группы определены свойствами объекта, и их изменить в процессе программирования на VBA нельзя. Эти свойства устанавливаются ОС Windows и системой программирования VBA. Примером может быть свертывание окна при щелчке на кнопке Свернуть. Вторая группа действий (отклика) на события определяется программистом. Для этой группы действий программист может создать процедуры VBA для каждого возможного события, но на практике программист заполняет кодом процедуры только для тех событий, которые требуются для данного приложения Access.

www.lessons-tva.info

Программирование на VBA в Access

VBA является интерпретируемым языком, то есть его инструкции интерпретируются каждый раз при выполнении программы. Этот язык является общим для всех приложений MS Office; на нем можно с равным успехом писать VBA является интерпретируемым языком, то есть его инструкции интерпретируются каждый раз при выполнении программы. Этот язык является общим для всех и не могут содержать приложений MS Office; на нем можно с равным успехом писать программы как для Access, так и для Word либо Excel. Вначале давайте кратко рассмотрим синтаксис языка VBA.

Объявление переменных.Переменные в Access можно объявлять явно и неявно. Имена переменных должны начинаться с буквы пробелов или других знаков пунктуации, кроме знака подчеркивания. Также нельзя использовать зарезервированные слова. Число символов в имени не должно превышать 225. При неявном задании переменной ей автоматически присваивается универсальный тип Variant. Переменная такого типа может принимать любые значения (этот тип по умолчанию имеют все данные, непосредственно полученные из таблиц и запросов) и дополнительно пустое значение Null. Проверить, содержит ли переменная какое-либо значение, можно с помощью функции IsNull().

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

Byte Currency @
Integer % String $
Long & Date
Single ! Boolean
Double # Variant

Для явного описания переменной используется оператор Dim.

Dim <имя переменной> As <тип переменной>.

Например, Dim i As Integer, j As Integer Dim x As Double

Строки могут быть постоянной и переменной длины. Оператор Dim <имя переменной> As String объявляет строку переменной длины, а оператор Dim <имя переменной> As String*<количество символов> - фиксированной длины. Например:

Dim str1 As String Dim str2 As String*20.

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

Переменные имеют определенную область видимости. Существует четыре уровня видимости:

1. Локальный уровень или уровень процедуры. Переменные существуют только внутри процедуры, где они описаны и используются;

2. Уровень формы (отчета). Переменные описываются в разделе описаний модуля формы (отчета) и являются доступными только для процедур этой формы (отчета) в то время, когда форма (отчет) открыта;

3. Уровень модуля. Переменные описываются в разделе описаний стандартного модуля и доступны для всех процедур модуля при открытой БД;

4. Глобальный уровень. Переменная записывается в разделе описаний модуля при помощи инструкций Public или Global.

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

Все переменные имеют определенное время жизни. Для локальных переменных время жизни определяется временем использования кода. Каждый раз при новом вызове процедуры переменной приписывается либо Null, либо 0, либо пустая строка в зависимости от типа. Для создания переменных со временем жизни, равным времени жизни приложения, используется инструкция Static. Тем не менее, статическая переменная не может быть использована в других процедурах. Изменяется лишь время ее жизни, а не область видимости. Если произойдет повторный вызов той же самой процедуры, в которой была описана статическая переменная, то эта переменная сохранит свое прежнее значение, которое она имела в момент завершения работы этой процедуры при предыдущем вызове. Все переменные в определенной процедуре будут статическими, если перед самой процедурой поставить инструкцию Static.

C помощью ключевого слова Dim также можно определять многомерные массивы. Размерность и число элементов массива определяется внутри скобок после имени переменной. Например:

Dim myArray(20) As String*10

Dim myArray(5, 5) As Double или Dim myArray(3, 3, 3) As Double

Нумерация элементов массива начинается с 0, поэтому для первого примера будет зарезервирована память для массива, состоящего из 21 элемента, для второго – 6 на 6. При объявлении массива можно определять не только верхнюю границу массива, но и нижнюю. Например, Dim my Array(1 то 20) As String.

В VBA допускается использование динамических массивов. Для этого при описании переменной число элементов массива опускается, а затем применяется инструкция ReDim для объявления реального размера массива. При этом все элементы массива обнуляются. Для сохранения значений элементов массива применяется инструкция Preserve.

Dim dynArray( ) As Double

ReDim Preserve dynArray (5, 2, 4)

Во время выполнения программы нижнюю и верхнюю границы массива можно определить с помощью функций LBound() и UBound() соответственно. Удалить массив из памяти можно с помощью оператора Erase.

Константы в VBA описываются с помощью ключевого слова Const. Вместе с объявлением константы также необходимо произвести ее инициализацию.

Const pi As Double = 3.1415

Кроме переменных вышеприведенных типов в VBA можно задавать объектные переменные, применяющиеся для хранения ссылок на объекты Access. Для каждой коллекции основных объектов в Access имеется соответствующий ей объектный тип, а также общий тип Object, принимающих ссылки на любые объекты. Например:

Dim myObject As Object – переменная любого объектного типа,

Dim myControl As Control – переменная типа элемента управления,

Dim myForm As Form – переменная типа формы.

Присваивать конкретные значения объектным переменным можно с помощью инструкции Set.

Set myForm = Forms![Моя Форма].

Пользователь может также создавать свой тип данных на основе существующих. Пользовательский тип данных вводится между ключевыми словами Type…End Type.

Type tPhone

number As String*15

type As String*10

End Type

Обращение к полю пользовательского типа производится через операцию “.”.

Dim tel As tPhone

tel.number = 3334455

tel.type = "Мобильный".

Заполнить объектные переменные, содержащие много свойств, а также переменные пользовательского типа данных можно с помощью инструкции With … End With.

Dim myForm As Form

Set myForm = Forms![Имя формы]

With myForm

.Top=1000

.Left=1000

.Width=5000

. Height=4000

End With.

Создание комментариев.Любой текст, следующий за символом ‘, воспринимается как комментарий.

Процедуры и функции.Основными компонентами программ на VBA являются процедуры и функции. Они представляют собой фрагменты программного кода, заключенные между операторами Sub и End Sub или между Function и End Function. В общем случае процедуры и функции записываются следующим образом:

Sub <имя процедуры< [(<аргументы>)]

<операторы>

End Sub

Function <имя функции> [(<аргументы>)] As <тип возвращаемого значения>

<операторы>

<имя функции> = <возвращаемое значение>

End Function

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

Call mySub("Ландера", 4, i+1)

mySub "Ландера", 4, i+1

total_price = myFunc([Цена]*[Количество], [Доставка])

Допускается два различных способа передачи переменных процедуре или функции: по ссылке или по значению. По умолчанию переменные передаются по ссылке (объявление ByRef). При изменении значения переменной внутри процедуры это изменение остается и при выходе из этой процедуры. Переменные можно передавать и по значению. Тогда изменение значения такой переменной не будет воздействовать на значение переменной, переданной в процедуру или функцию. Для этого используют объявление ByVal. В общем виде объявление аргументов производится следующим образом BуVal/ByRef <имя аргумента> [( )] [As <тun>]. Например:

Sub mySyb(srteet As String, ByVal building As Integer, ByRef apt As Byte)

Forms![Клиенты]![Адресс] = srteet & " " & building & ", " & apt

End Sub

Function myFunc(full_price As Currency, shipment As Single) As Currency

myFunc = full_price * 1.2 + shipment

End Function

Для принудительного выхода из процедуры и функции используются соответственно Exit Sub и Exit Function.

Управление выполнением программы.Управление выполнением программы достигается за счет оператора безусловного перехода, операторов ветвления (условных операторов) и циклов. Условные операторы выполняют группу команд в зависимости от условия. К ним относится оператор If и Select Case. В простейшем виде оператор If можно записать в одну строку.

If <условие> Then <оператор>

Более сложный вариант использования оператора If приведен ниже:

If <условие> Then

<операторы>

Else

<операторы>

End If

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

If <условие> Then

<операторы>

Else If <условие 2> Then

<операторы>

Else

<операторы>

End If

Если выбор действий зависит от различных значений одного и того же выражения, то вместо вложенного оператора If предпочтительнее использовать оператор Select Case … End Select. Этот оператор определяет, является ли выражение истинным, а также оценивает, заключены ли в определенных пределах значения этого выражения.

Select Case <имя переменной>

Case <выражение 1> [ , <выражение 2>, …]

(операторы, выполняемые, если значения переменной удовлетворяют или выражение 1, или выражение 2, или …)

[Case <выражение 3> To <выражение 4>]

(операторы, выполняемые, если значение переменной находится в диапазоне, определяемом выражениями 3 и 4)

[Case Is <выражение отношения>]

(операторы, выполняемые, если значение переменной удовлетворяет выражению отношения.)

[Case Else]

(операторы, выполняемые, если не было выполнено ни одно из вышеперечисленных условий)

End Select

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

Операторы цикла в VBA различаются на две основные группы: циклы с перечислением (For … Next) и циклы с условием (Do … Loop).

Оператор For…Next повторяет тело цикла заданное число раз.

For <счетчик> = <начальное значение> To <конечное значение> [Step <приращение>]

<операторы>

Next <счетчик>

Для выхода из цикла используется оператор Exit For.

Операторы циклов Do While … Loop, While … Wend являются синонимами. While … Wend оставлен для совместимости со старыми версиями. Эти операторы повторяют тело цикла, пока условие принимает значение True.

Do While <условие [=True]>

<операторы>

Loop

Оператор Do Until … Loop выполняет тело цикла до тех пор, пока не будет выполнено условие

Do Until <условие [< > True]>

<операторы>

Loop

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

Do

<операторы>

Loop While <условие [=True]>

или

Do

<операторы>

Loop Until <условие [=False]>

Для выхода из этих циклов используется оператор Exit Do.

Для построения цикла по всем элементам массива или коллекции используется инструкция For Each

For Each <элемент> In <коллекция>

<операторы>

Next <элемент>

где <элемент> – это переменная, используемая для ссылки на элементы семейства объектов. Следующий пример заполнит выпадающий список элемента управления ПолеСоСписком1 названиями всех элементов управления, расположенных в форме

Dim ctrl As Control

For Each ctrl In Form

ПолеСоСписком1.AddItem ctrl.Name

Next ctrl

Похожие статьи:

poznayka.org

Microsoft Access. Работа с таблицами из VBA

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

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

Наверное, Вы уже научились создавать таблицу с помощью мастера, этот способ указан слева. Теперь давайте попробуем создать ее программным способом. Иногда этот способ становится более предпочтительным, чем стандартный. В качестве переменной создаваемой таблицы будем использовать имя StrTable = «Калькулятор»

    On Error GoTo 999 'Назначаем переход по ошибке

    funCreateTable = False 'Возвращаем результат при ошибке

    If funVerifyTable(strTable) = False Then 'Проверяем таблицу

        dbs = appAccess.CurrentDb 'Находим базу данных

        tdf = dbs.CreateTableDef(strTable)  'Создаем таблицу

        tdf.Fields.Append tdf.CreateField("Пункт", dbLong) 'Создаем 1 поле

        dbs.TableDefs.Append tdf 'Добавляем таблицу

        funCreateFields strTable '<3> Создаем поля

        funCreateTable = True 'Возвращаем результат

    End If

    Exit Function

999:

    MsgBox(Err.Description, vbCritical, "Создание таблицы") 'Сообщаем красиво об ошибке

    Err.Clear() 'Очищаем поток от ошибок

End Function

Проверка таблицы

Очень часто при работе с базой данных Вам надо проверить наличие в базе данных объекта. На данном примере указан способ проверки таблицы в базе данных.

Если объекта нет, то случается ошибка и программа очищает поток от ошибок, возвращая значение False.

На этом снимке экрана в Access 2016 отображается таблица Калькулятор. Помните, что системные и другие таблицы в разных базах данных могут быть скрыты от просмотра.

Public Function funVerifyTable(strTable As String) As Boolean

    Dim tdf As TableDef

    On Error GoTo 999  'Назначаем переход по ошибке

    funVerifyTable = False 'Возвращаем результат при ошибке

    tdf = appAccess.CurrentDb.TableDefs(strTable) 'Находим объект

    If (tdf Is Nothing) = False Then funVerifyTable = True 'Проверяем объект

    tdf = Nothing 'Уничтожаем переменную

    Exit Function 'Выходим из программы

999:

    Err.Clear() 'Очищаем поток от ошибок

End Function

Создание полей таблицы

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

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

На этом снимке экрана таблица Калькулятор в Access 2016 находится в режиме проектирования полей. Мы видим три поля, тип данных и описание.

Public Function funCreateFields(strTable As String) As Boolean

    Dim dbs As Database, tdf As TableDef, fld As Field

    On Error GoTo 999 'Назначаем переход по ошибке

    funCreateFields = False 'Возвращаем результат при ошибке

    dbs = appAccess.CurrentDb 'Определяем базу данных

    tdf = dbs.TableDefs(strTable) 'Находим таблицу

    With tdf 'Выбираем таблицу для изменения

        .Fields.Append.CreateField("Выражение", dbText, 75) 'Создаем поле Выражение

        .Fields.Append.CreateField("Итог", dbDouble) 'Создаем поле Итог

    End With

    fld = tdf.Fields("Пункт") 'Изменяем свойства поля "Пункт"

    funChangeProperty(fld, "Description", dbText, "Номер выражения в калькуляторе") 'Изменяем описание

    funChangeProperty(fld, "Format", dbText, "Fixed") 'Назначаем фиксированный формат

    funChangeProperty(fld, "DecimalPlaced", dbByte, 0) 'Отключаем десятичные знаки

    fld = Nothing 'Уничтожаем переменную поля

    tdf = Nothing 'Уничтожаем переменную таблицы

    funCreateFields = True 'Возвращаем результат

    Exit Function 'Выходим из программы

999:

    MsgBox(Err.Description, vbCritical, "Создание таблицы") 'Сообщаем об ошибке

    Err.Clear() 'Очищаем поток от ошибок

End Function

Изменение свойств таблицы

Когда Вы создаете поле программным методом, некоторые его свойства, например, Описание (Description) не создаются автоматически.

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

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

У каждого поля кроме его имени и типа данных есть дополнительные свойства. На этой форме в Access 2016 показано, что их можно увидеть в конструкторе таблицы

'Параметры:

'   fld - поле в таблице (Объект Field)

'   strName - имя свойства (Description, Format ...)

'   varType - тип свойства (dbText, dbLong ...)

'   varValue - значение свойства

'

Function funChangeProperty(fld As Field, strName As String, varType

                As Variant, varValue As Variant) As Boolean

    Dim prp As Object

    On Error GoTo 999 'Назначаем переход по ошибке

    funChangeProperty = False 'Возвращаем результат при ошибке

    fld.Properties(strName) = varValue 'Присваиваем значение полю

    funChangeProperty = True 'Возвращаем результат

    Exit Function 'Выходим из программы

999:

    If Err = 3270 Then  'Свойство не найдено

        prp = fld.CreateProperty(strName, varType, varValue) 'Создаем свойство

        fld.Properties.Append prp 'Добавляем свойство

        Err.Clear() 'Очищаем поток от ошибки

        Resume Next 'Возвращаемся к следующему оператору

    End If

    Err.Clear() 'Очищаем от незнакомой ошибки

End Function

Проверка знаний

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

Ваш результат обучения Вы можете увидеть, нажав кнопку [Проверка N3] внизу экрана. Перед Вами отобразится база данных, в которой будет пустая таблица «Калькулятор». На следующем этапе программирования мы будем создавать запросы SQL, которые нужны для управления формой калькулятора. Итак для изучения следующей лекции нажмите кнопку Вперед>

blog.leadersoft.ru

access-vba - Как объединиться в MS Access, VBA

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

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

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

Округление значений вверх, вниз, на 4/5 или на значительные цифры (EE)

или здесь:

Округление значений вверх, вниз, на 4/5 или на значительные цифры (CodePlex)

Код только в GitHub:

VBA.Round

Они покрывают обычные методы округления:

  • Раунд вниз, с возможностью округления отрицательных значений к нулю

  • Раунд вверх, с возможностью округления отрицательных значений от нуля

  • Round by 4/5, либо от нуля, либо до четного (округление Banker)

  • Раунд к количеству значимых цифр

Первые три функции принимают все числовые типы данных, а последние существуют в трех вариантах - для Currency, Decimal и Double соответственно.

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

Также включен тестовый модуль для тестирования и проверки.

Пример здесь - для общего округления 4/5. Пожалуйста, изучите встроенные комментарии для тонких деталей и способ использования CDec для предотвращения ошибок в битах.

' Common constants. ' Public Const Base10 As Double = 10 ' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimals. ' ' Rounds to integer if NumDigitsAfterDecimals is zero. ' ' Rounds correctly Value until max/min value limited by a Scaling of 10 ' raised to the power of (the number of decimals). ' ' Uses CDec() for correcting bit errors of reals. ' ' Execution time is about 1µs. ' Public Function RoundMid( _ ByVal Value As Variant, _ Optional ByVal NumDigitsAfterDecimals As Long, _ Optional ByVal MidwayRoundingToEven As Boolean) _ As Variant Dim Scaling As Variant Dim Half As Variant Dim ScaledValue As Variant Dim ReturnValue As Variant ' Only round if Value is numeric and ReturnValue can be different from zero. If Not IsNumeric(Value) Then ' Nothing to do. ReturnValue = Null ElseIf Value = 0 Then ' Nothing to round. ' Return Value as is. ReturnValue = Value Else Scaling = CDec(Base10 ^ NumDigitsAfterDecimals) If Scaling = 0 Then ' A very large value for Digits has minimized scaling. ' Return Value as is. ReturnValue = Value ElseIf MidwayRoundingToEven Then ' Banker rounding. If Scaling = 1 Then ReturnValue = Round(Value) Else ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675. ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error ' when dividing. On Error Resume Next ScaledValue = Round(CDec(Value) * Scaling) ReturnValue = ScaledValue / Scaling If Err.Number <> 0 Then ' Decimal overflow. ' Round Value without conversion to Decimal. ReturnValue = Round(Value * Scaling) / Scaling End If End If Else ' Standard 4/5 rounding. ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error ' when dividing. On Error Resume Next Half = CDec(0.5) If Value > 0 Then ScaledValue = Int(CDec(Value) * Scaling + Half) Else ScaledValue = -Int(-CDec(Value) * Scaling + Half) End If ReturnValue = ScaledValue / Scaling If Err.Number <> 0 Then ' Decimal overflow. ' Round Value without conversion to Decimal. Half = CDbl(0.5) If Value > 0 Then ScaledValue = Int(Value * Scaling + Half) Else ScaledValue = -Int(-Value * Scaling + Half) End If ReturnValue = ScaledValue / Scaling End If End If If Err.Number <> 0 Then ' Rounding failed because values are near one of the boundaries of type Double. ' Return value as is. ReturnValue = Value End If End If RoundMid = ReturnValue End Function

qaru.site

sql - Как использовать параметры в VBA в разных контекстах Microsoft Access?

Существует много способов использования параметров в запросах. Я попытаюсь предоставить примеры для большинства из них и где они применимы.

Во-первых, мы обсудим решения, уникальные для Access, такие как формы, отчеты и агрегации доменов. Затем мы поговорим о DAO и ADO.

В Access вы можете напрямую использовать текущее значение элементов управления для форм и отчетов в вашем коде SQL. Это ограничивает необходимость в параметрах.

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

Forms!MyForm!MyTextbox для простого управления на форме

Forms!MyForm!MySubform.Form!MyTextbox для управления надстройкой

Reports!MyReport!MyTextbox для управления в отчете

Пример реализации:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Forms!MyForm!MyTextbox" 'Inserts a single value DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = Forms!MyForm!MyTextbox" 'Inserts from a different table

Это доступно для следующих целей:

При использовании DoCmd.RunSQL, обычных запросов (в графическом интерфейсе), форм и источников отчетов, форм и отчетов, агрегатов доменов, DoCmd.OpenForm и DoCmd.OpenReport

Это недоступно для следующих целей:

При выполнении запросов с использованием DAO или ADODB (например, открытие наборов записей CurrentDb.Execute)

Использование TempVars в качестве параметров

TempVars in Access - глобально доступные переменные, которые могут быть установлены в VBA или с использованием макросов. Они могут использоваться повторно для нескольких запросов.

Пример реализации:

TempVars!MyTempVar = Me.MyTextbox.Value 'Note: .Value is required DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = TempVars!MyTempVar" TempVars.Remove "MyTempVar" 'Unset TempVar when you're done using it

Доступность для TempVars идентична доступности значений из форм и отчетов: недоступна для ADO и DAO, доступных для других целей.

Я рекомендую TempVars для использования параметров при открытии форм или отчетов по ссылке на управляющие имена, так как если закрытие объекта закрывается, TempVars остаются доступными. Я рекомендую использовать уникальные имена TempVar для каждой формы или отчета, чтобы избежать странности при обновлении форм или отчетов.

Использование пользовательских функций (UDF) в качестве параметров

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

Пример реализации:

Option Compare Database Option Explicit Private ThisDate As Date Public Function GetThisDate() As Date If ThisDate = #12:00:00 AM# Then ' Set default value. ThisDate = Date End If GetThisDate = ThisDate End Function Public Function SetThisDate(ByVal NewDate As Date) As Date ThisDate = NewDate SetThisDate = ThisDate End Function

а затем:

SetThisDate SomeDateValue ' Will store SomeDateValue in ThisDate. DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"

Кроме того, для обеих настроек и получения значения частной статической переменной может быть создана одна функция с необязательным параметром:

Public Function ThisValue(Optional ByVal Value As Variant) As Variant Static CurrentValue As Variant ' Define default return value. Const DefaultValue As Variant = Null If Not IsMissing(Value) Then ' Set value. CurrentValue = Value ElseIf IsEmpty(CurrentValue) Then ' Set default value CurrentValue = DefaultValue End If ' Return value. ThisValue = CurrentValue End Function

Чтобы установить значение:

ThisValue "Some text value"

Чтобы получить значение:

CurrentValue = ThisValue

В запросе:

ThisValue "SomeText" ' Set value to filter on. DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"

Использование DoCmd.SetParameter

Использование DoCmd.SetParameter довольно ограничено, поэтому я буду краток. Он позволяет установить параметр для использования в DoCmd.OpenForm, DoCmd.OpenReport и некоторых других операторах DoCmd, но он не работает с DoCmd.RunSQL, фильтрами, DAO и ADO.

Пример реализации

DoCmd.SetParameter "MyParameter", Me.MyTextbox DoCmd.OpenForm "MyForm",,, "ID = MyParameter"

Использование DAO

В DAO мы можем использовать объект DAO.QueryDef для создания запроса, установки параметров, а затем либо открыть набор записей, либо выполнить запрос. Сначала вы устанавливаете SQL запросов, затем с помощью коллекции QueryDef.Parameters устанавливаете параметры.

В моем примере я собираюсь использовать неявные типы параметров. Если вы хотите сделать их явными, добавьте PARAMETERS объявление в ваш запрос.

Пример реализации

'Execute query, unnamed parameters With CurrentDb.CreateQueryDef("", "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?") .Parameters(0) = Me.Field1 .Parameters(1) = Me.Field2 .Execute End With 'Open recordset, named parameters Dim rs As DAO.Recordset With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2 WHERE Field1 = FirstParameter And Field2 = SecondParameter") .Parameters!FirstParameter = Me.Field1 'Bang notation .Parameters("SecondParameter").Value = Me.Field2 'More explicit notation Set rs = .OpenRecordset End With

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

Использование ADO

Вы можете использовать параметры в ADO с помощью объекта ADODB.Command. Используйте Command.CreateParameter для создания параметров, а затем добавьте их в коллекцию Command.Parameters. ADO требует, чтобы вы были более явными, чем любой из предыдущих способов использования параметров. Хотя некоторые аргументы (такие как длина) иногда могут быть опущены, я не рекомендую его.

Пример реализации:

'Execute query, unnamed parameters Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database .CommandText = "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?" .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) 'adVarWChar for text boxes that may contain unicode .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) 'adInteger for whole numbers (long or integer) .Execute End With 'Open recordset, named parameters Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database .CommandText = "SELECT Field1 FROM Table2 WHERE Field1 = @FirstParameter And Field2 = @SecondParameter" .Parameters.Append .CreateParameter("@FirstParameter", adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) .Parameters.Append .CreateParameter("@SecondParameter", adInteger, adParamInput, 8, Me.Field2) Set rs = .Execute End With

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

qaru.site

vba - Создание глобального кода VBA в Access

Я хочу встроить простую часть кода VBA в Access 2007. Мне нужно выполнить этот код на сотнях различных БД доступа, поэтому я не хочу вручную вставлять код в каждый БД. Можно ли сделать это? Может быть, с помощью надстройки?

Спасибо Карл

ИЗМЕНИТЬ

Я хочу выполнить следующий код VBA:

DoCmd.DeleteObject acTable, "LastNum" DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=myDB;UID=User1;PWD=123;LANGUAGE=u s_english;" & "DATABASE=LastNumber", acTable, "LastNum", "LastNum"

Как перевести это в дополнение к VB?

Шаблон надстройки Visual Studio VB выглядит следующим образом:

imports Extensibility imports System.Runtime.InteropServices <GuidAttribute("B61E2444-F46E-4591-A8BA-3D06A4E5D84C"), ProgIdAttribute("MyAddin1.Connect")> _ Public Class Connect Implements Extensibility.IDTExtensibility2 Private applicationObject As Object Private addInInstance As Object Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown End Sub Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate End Sub Public Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete End Sub Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection End Sub Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection applicationObject = application addInInstance = addInInst End Sub End Class

ИЗМЕНИТЬ ЧАСТЬ 2:

Хорошо, поэтому я понял, что должен сделать следующее:

imports Extensibility Imports System.Runtime.InteropServices Imports Microsoft.Office.Core Imports Access = Microsoft.Office.Interop.Access <GuidAttribute("B61E2444-F46E-4591-A8BA-3D06A4E5D84C"), ProgIdAttribute("MyAddin1.Connect")> _ Public Class Connect Implements Extensibility.IDTExtensibility2 Private applicationObject As Access.Application Private addInInstance As Microsoft.Office.Core.COMAddIn Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown End Sub Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate End Sub Public Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete End Sub Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection End Sub Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection applicationObject = CType(application, Access.Application) addInInstance = CType(addInInst, Microsoft.Office.Core.COMAddIn) ' This line enables VBA to call back into this object. addInInstance.Object = Me End Sub Public Sub ChangeLink() applicationObject.DoCmd.DeleteObject(Access.AcObjectType.acTable, "LastPolNum") applicationObject.DoCmd.TransferDatabase(Access.AcDataTransferType.acLink, "ODBC Database", "ODBC;DSN=ZACANTDB02;UID=EDIPolicyNumber;PWD=museum123;LANGUAGE=u s_english;" & "DATABASE=EDIPolicyNumber", Access.AcObjectType.acTable, "LastPolnum", "LastPolNum") End Sub End Class

Теперь я хочу, чтобы иметь возможность выполнить ChangeLink() из Access. Как это сделать?

qaru.site

vba - Различие между использованием .text и .value в VBA Access

Эта тема и ответы здесь хорошо объясняют проблему. Есть несколько дополнительных моментов, которые я хотел бы добавить, которые я нашел в ходе экспериментов:

Порядок приоритета свойств:

  • .ControlSource
  • .Value
  • .Text

Из того, что я видел в Access 2007, если .ControlSource есть undefined, когда форма открывается, .Value будет Null.

Если вы установите для свойства .ControlSource значение ="" (пустая строка), это приведет к тому, что для свойства .Value будет присвоено значение по умолчанию вместо Null.

Вы можете установить для свойства .Value значение "" в событии Form_Load. Но... Я видел там какую-то неустойчивую операцию; кажется, что .Value иногда изменяется с "" на Null, и я еще не разработал обстоятельства.

Так что лучше всего определить .ControlSource до ="", либо в Design View, либо в событии Form_Load. Но следует предупредить, что niblet является сложным из-за встроенных двойных кавычек, и это может быть сложно прочитать.

Некоторые способы сделать это:

  • myTextbox.ControlSource = "=" и " "" "(пять двойных кавычек подряд)
  • myTextbox.ControlSource = "=" и Chr (34) и Chr (34)
  • Etc и т.д., есть много способов сделать это...

Кроме того, здесь расширенный лакомый кусочек. Если вы установите для свойства .TextFormat значение Rich Text, вы можете отформатировать текст в нем жирным шрифтом, курсивом, цветами и т.д. Но следует предупредить (снова), начиная с Office 2007, оригинальный формат Microsoft RTF был выведен из эксплуатации в пользу "мини" версии HTML, которая поддерживает только несколько тегов, связанных с форматированием шрифтов и абзацев.

В качестве примера скажем, что вы хотите, чтобы текстовое поле отображало маленький символ значка ASCII с надписью "valid" курсивом рядом с ним и делало его зеленым. Вы можете это сделать, но все это должно быть в HTML, и это непросто читать:

myTextbox.TextFormat = acTextFormatHTMLRichText myTextbox.ControlSource = "=" & Chr(34) & "<font color=#80CA45><font face=Wingdings>" & _ Chr(254) & "</font>&nbsp;<font face=Calibri><i>Valid.</i></font></font>" & Chr(34)

qaru.site