Access зависимость одного поля от другого

Довольно распространена ситуация, когда выбор значения из одного списка должен приводить к обновлению второго, подчинённого списка, недавно я описывал такую реализацию на PHP, а теперь вот понадобилась в Access.

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

«Примечания» здесь добавлены просто для иллюстрации отношения «один к одному» как связи между двумя ключами.

Теоретически сотрудник может переводиться как на другую должность в рамках отдела, так и в другой отдел, что приведёт к необходимости выбрать должность из списка должностей нового отдела. То есть, по выбору для сотрудника нового отдела из верхнего списка, должен обновляться и нижний список «Наименование должности»:

Как решить эту типовую задачу, мы и поговорим.

1. Источником записей формы «Сотрудники» делаем соответствующую таблицу, форму можно сгенерировать мастером, а затем удалить из неё поля «Код отдела» и «Код должности».

2. Вместо первого поля, включив кнопку «Мастера» на панели элементов Access, сгенерируем список, выводящий вместо кодов названия отделов. Сделать это можно так:

форма выведена в режиме конструктора, нажимаем кнопку «Поле со списком», показываем мышкой границы будущего списка. По отпускании кнопки мыши появится окно «Мастер создания полей со списком»;
выбираем «Объект будет использовать значения из таблицы или запроса», жмём «Далее»;
выбираем таблицу «Отделы», жмём «Далее»;
переносим оба поля таблицы отделов в №выбранные поля» (кнопкой >>), жмём «Далее»;
убеждаемся, что список виден, а галочка «Скрыть ключевой столбец» включена, жмём «Далее»;
выбираем «Сохранить в поле:» и поле «КодОтдела», жмём «Далее»; задаём подпись к списку и жмём «Готово».

3. С помощью конструктора запросов составляем следующий запрос:

Здесь «ПолеСоСписком16» — наименование компоненты верхнего списка.

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

Увидеть это окно можно, щёлкнув правой кнопкой мыши на втором списке и выбрав «Свойства».

5. Теперь самое главное. В списке компонент выбираем «Форма» вместо имени поля со списком, выбираем вкладку события, событие Текущая запись, щёлкаем по кнопке с многоточием, подтверждаем, что нам нужен построитель программы, а не макроса или выражения:

Открылся редактор Visual Basic и появилась пустая функция с именем Form_Current . Пишем туда единственную строчку кода, которая обновит нижний список:

Здесь предполагается, что имя нижнего списка ПолеСоСписком20 .

6. Ту же самую строчку кода добавляем на событие «До обновления» верхнего списка, у меня получилось вот что:

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

Скачать этот пример в архиве ZIP (41 Кб)

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

Ну и общие соображения по зависимым спискам в Access:

Если первый список не связан с полем источника записей формы, то обновление надо ставить в обработчик события BeforeUpdate («До обновления» в окне свойств конструктора формы в русских версиях Access-а), либо в AfterUpdate.

Если же первый список связан с полем источника записей формы, то обновление надо ставить в обработчик события Current («Текущая запись») формы и BeforeUpdate (AfterUpdate) первого списка.

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

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

Access зависимость одного поля от другого

Читать еще: Темы для базы данных access

Как решить эту типовую задачу, мы и поговорим.

форма выведена в режиме конструктора, нажимаем кнопку «Поле со списком», показываем мышкой границы будущего списка. По отпускании кнопки мыши появится окно «Мастер создания полей со списком»;
выбираем «Объект будет использовать значения из таблицы или запроса», жмём «Далее»;
выбираем таблицу «Отделы», жмём «Далее»;
переносим оба поля таблицы отделов в №выбранные поля» (кнопкой >>), жмём «Далее»;
убеждаемся, что список виден, а галочка «Скрыть ключевой столбец» включена, жмём «Далее»;
выбираем «Сохранить в поле:» и поле «КодОтдела», жмём «Далее»; задаём подпись к списку и жмём «Готово».

3. С помощью конструктора запросов составляем следующий запрос:

Здесь «ПолеСоСписком16» — наименование компоненты верхнего списка.

Увидеть это окно можно, щёлкнув правой кнопкой мыши на втором списке и выбрав «Свойства».

Здесь предполагается, что имя нижнего списка ПолеСоСписком20 .

6. Ту же самую строчку кода добавляем на событие «До обновления» верхнего списка, у меня получилось вот что:

Скачать этот пример в архиве ZIP (41 Кб)

Ну и общие соображения по зависимым спискам в Access:

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

Автоматическая подстановка значения в поле ввода

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

Читать еще: Курсы access специалист

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

Для решения поставленной задачи требуется выполнить следующие действия:[ Реализация этого примера — небольшая база данных Заказы.mdb — есть на компакт-диске, который является дополнительным приложением к комплекту книг о Microsoft Office 2002 издательства «БХВ-Петербург» и распространяется отдельно. ]

Создайте запрос, содержащий поле «Цена» из таблицы «Товары» и все поля из таблицы «Заказы». Сохраните его с именем «Заказано».
Создайте форму «Заказы» на основе запроса «Заказано».
Поместите в форму элемент управления Список, связанный с полем «НаименованиеТовара» источника данных формы.

Замечание

Это можно выполнить с помощью окна Список полей (Filed List) (см. гл. 5), предварительно указав в режиме Конструктора таблицы «Заказы» для поля «НаименованиеТовара» на вкладке Подстановка (Lookup) значение Поле со списком (Combo Box) для свойства Тип элемента управления (Display Control) и имя таблицы «Товары» в качестве значения свойства Источник,строк (Row Source).

Поместите в форму текстовые поля, связанные с полями «Количество» и «Сумма» источника данных формы.
Для поля «Количество» в окне свойств раскройте вкладку События (Events). Для события После обновления (After Update) создайте процедуру обработки события (см. разд. «Динамическая фильтрация данных» этой главы) со следующим содержанием:

Private Sub Количество_А£terUpdate()

Сумма = Цена * Количество End Sub

Таким образом, после ввода значения в поле «Количество» в поле «Сумма» будет автоматически подставлена вычисленная стоимость заказа. А поскольку поле «Сумма» является присоединенным к одноименному полю в таблице «Заказы», вычисленное значение автоматически попадет в таблицу.

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

Поле со списком access

Поле со списком access – составной элемент управления, объединяющий поле и раскрывающийся список, позволяющий не только выбирать (как в простом списке), но и вводить значения.

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

Открывается список нажатием на кнопке
(в правом углу элемента управления «Поле со списком access») или клавишами Alt+«стрелка вниз». При помощи простого макроса можно сделать так, чтобы поле со списком раскрывалось само при входе в него.

Чтобы ввести значение в поле со списком access, можно начать вводить значение в поле с клавиатуры или выбрать нужное значение в выпадающем списке. При вводе значений с клавиатуры помогает свойство «Автоподстановка». Если установить его как «да», то при вводе информации Access автоматически будет предлагать значения из списка, выделяя буквы предлагаемого значения после последнего введённого символа.

Обилие всяких кнопочек и настроек может постоянно сбивать вас с толку. Также может появиться страх при первом открытии программы.

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

Например, слово «детектив» в поле «литературный жанр» можно написать «детиктив» или «дитиктив», или ещё как-нибудь (неважно, опечатка это или следствие неграмотности оператора. Любой человек может ошибаться и даже имеет на это право).

Читать еще: Что такое switch access в телефоне

Поле, содержащее такие значения, уже нельзя правильно ни отсортировать, ни использовать в запросе. Мы зададим запрос с фильтром Like «детектив» и запрос найдёт нам, предположим, 120 книг этого жанра. Но вследствие неправильного ввода информации запрос «не увидел», к примеру, 8 книг.

Такая ситуация просто недопустима. Гораздо проще один раз и в одном месте (в базовой таблице подстановки) правильно ввести название жанра и связать эту запись с полем подстановки в таблице поставок книг отношением «один-ко-многим» (можно и не связывать. Но этот вопрос не представляется возможным обсудить в пределах одной статьи, так как слишком много завязано вопросов с типами полей, связями и др.).

Другой вопрос состоит в том, стоит ли разрешать оператору добавлять новые значения в базовую таблицу подстановки (со стороны «один»). Всё зависит от конкретных задач и типа информации. Не всегда можно предугадать все возможные варианты для значений поля подстановки. Например, поле подстановки для указания пола сотрудника может принимать два значения: «мужской» и «женский». А вот попробуйте создать поле подстановки мужских или женских имён (например, мужские имена: Андрей, Андриан, Андрон, Анджей и т. д.; женские имена: Наталья, Наталия, Наталея, Натали и т. д.).

Аксесс довольно сложная программа. Это подтверждает тот факт, что в интернете можно найти массу информации по ней. Однако, вы знаете что на самом деле достаточно знать лишь 20% инструментов, чтобы создавать и настраивать 80% баз данных в программе?

Во-первых, некоторые родители дают такие имена, которых не было раньше в природе (а это имя ещё надо ввести в базовую таблицу подстановки).

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

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

К тому же оно чаще всего используется в инициалах. Например, я ввёл значение «Иванов Андриан Иванович», хотя имя человека «Андрей». В поле «ФИО» получим «Иванов А. И.». Вроде как и ошибки нет.)

Создаётся поле со списком access, как правило, в режиме конструктора объекта базы данных:

— в таблицах – в области «Свойства поля» на вкладке «Подстановка» есть такой аргумент «Тип элемента управления» с выпадающим списком. Выбираем значение «Поле со списком» и выбираем следующие аргументы: тип источника строк, сам базовый источник строк, номер присоединённого столбца и другие необходимые элементы;

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

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

В приведённом ниже примере при выборе города в поле со списком автоматически загружаются в подчинённую форму записи, относящиеся к этому городу (т. е. мы видим, кто стоит на учёте в г. Щёлково).

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

Можно создать выражение, которое ссылается на отдельный столбец списка поля со списком или списка. Для этого используем свойство Столбец этого элемента управления:

forms![ИмяФормы]'[Имя поля со списком].[Column](x). Например: forms![Типы]![НазваниеТипа].[Column](1)

х – номер столбца. Столбцы начинают нумерацию с нуля.

При вычислении номера видимого столбца надо принимать в расчет и невидимые столбцы.