Использование параметров в запросах, формах и отчетах
Если вам необходимо, чтобы запрос в Access при запуске каждый раз предлагал ввести параметры, создайте запрос с параметрами.
Вы также можете создать форму для ввода значений, позволяющих ограничить количество возвращаемых записей в запросах, формах и отчетах. В этой статье рассказывается, как с помощью форм сделать проще и удобнее работу с параметрами в запросах, формах и отчетах.
В этой статье
Обзор
С помощью условий в запросе с параметрами в Access можно ограничить набор записей, возвращаемых запросом. Функциональности диалоговых окон в запросе с параметрами может оказаться недостаточно. В этом случае можно создать форму, которая лучше подойдет для ввода параметров в вашей ситуации. В этой статье рассказывается, как настроить форму для ввода параметров запросов и отчетов. Здесь предполагается, что вы уже знакомы с принципами создания запросов и определения параметров в них. Прежде чем продолжать, ознакомьтесь как минимум с процедурой создания запроса на выборку.
В этой статье приведены примеры использования параметров в запросах. Она не содержит полной справочной информации о настройке условий.
Дополнительные сведения о создании запроса на выборку см. в статье Создание простого запроса на выборку.
Дополнительные сведения о запросах в целом см. в статье Знакомство с запросами.
Дополнительные сведения о создании параметров в запросах см. в статье Использование параметров для ввода данных при выполнении запроса.
Дополнительные сведения о настройке условий в запросах см. в статье Примеры условий запроса.
Использование параметров в запросах
Использовать в запросе параметр не сложнее, чем создать запрос на основе условий. Запрос можно настроить таким образом, чтобы он предлагал ввести определенное значение, такое как артикул товара, или несколько значений, например две даты. Для каждого параметра запрос выводит отдельное диалоговое окно, в котором предлагается ввести значение.
Добавление параметра в запрос
Создайте запрос на выборку и откройте его в конструкторе.
В строке Условия поля, для которого вы хотите применить параметр, введите в квадратных скобках текст, который должен отображаться в соответствующем диалоговом окне. Пример:
При выполнении запроса с параметрами этот текст отобразится в диалоговом окне без квадратных скобок.
При запросе параметров также можно использовать выражения. Пример:
Between [Дата начала] And [Дата окончания]
Примечание: Для каждого параметра выводится отдельное диалоговое окно. Во втором примере появляется два окна: одно для даты начала, второе — для даты окончания.
Повторите шаг 2 для каждого поля, для которого хотите добавить параметры.
С помощью описанных выше действий можно добавить параметр в запрос любого из следующих типов: выборка, добавление, создание таблицы и обновление, а также перекрестный запрос.
Кроме того, с помощью перечисленных ниже действий можно добавить параметр в запрос на объединение.
Откройте запрос на объединение в режиме SQL.
Добавьте предложение WHERE, содержащее каждое поле, для которого требуется ввести параметр.
Если предложение WHERE уже существует, убедитесь в том, что поля, для которых нужно ввести параметры, уже есть в этом предложении; в противном случае добавьте их.
Вместо условия в предложении WHERE используйте запрос параметра.
Указание типов данных для параметра
Вы также можете указать, данные какого типа разрешается вводить в качестве значения параметра. Тип данных можно настроить для любого параметра, но особенно важно сделать это для числовых и денежных данных, а также значений даты и времени. Когда для параметра указан тип данных, пользователи получают более осмысленные сообщения об ошибках в случае ввода значений неправильного типа (например, текста вместо денежных величин).
Примечание: Если параметр настроен для ввода текстовых данных, любое введенное значение будет распознаваться как текст, а сообщение об ошибке не будет выводиться.
Чтобы задать тип данных для параметра в запросе, выполните указанные ниже действия.
Когда запрос открыт в конструкторе, на вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры.
В диалоговом окне Параметры запроса в столбце Параметр введите текст запроса на ввод значения для каждого параметра, для которого требуется указать тип данных. Убедитесь, что каждый из параметров соответствует запросу, который используется в строке Условие отбора в бланке запроса.
В столбце Тип данных выберите тип данных для каждого параметра.
Создание формы для ввода параметров
Хотя в запросах с параметрами есть встроенные диалоговые окна для ввода значений, они предлагают лишь простейшие функции. В то же время форма для ввода параметров поддерживает перечисленные ниже дополнительные возможности.
Использование элементов управления для ввода данных определенных типов, например календарей для дат.
Сохранение введенных параметров для использования в других запросах.
Списки и поля со списками для ввода параметров, позволяющие выбирать значения из заранее заданных наборов.
Элементы управления для выполнения других функций, таких как открытие запроса или обновление данных в нем.
В приведенном ниже видео демонстрируется создание простой формы для ввода параметров запроса вместо стандартных диалоговых окон.
Создание формы для ввода параметров для отчета
Реализовать этот сценарий можно несколькими способами, но мы рассмотрим вариант, в котором активно используются макросы. Создать форму для сбора параметров отчета можно с помощью описанной ниже последовательности действий.
Шаг 1. Создайте форму для ввода значений
На вкладке Создание в группе Формы нажмите кнопку Конструктор форм.
В режиме конструктора нажмите F4, чтобы открыть страницу свойств, и задайте свойства формы, как показано в таблице ниже.
Введите имя, которое будет отображаться в строке заголовка формы.
Режим по умолчанию
Режим сводной таблицы
Режим сводной диаграммы
Для каждого параметра, который будет вводиться с помощью формы, на вкладке Конструктор в группе Элементы управления нажмите кнопку Поле.
Задайте для полей свойства, как показано в таблице ниже.
Введите имя, описывающее параметр, например ДатаНачала.
Выберите формат, который соответствует типу данных поля параметра. Например, для поля даты выберите вариант Полный формат даты.
Сохраните форму и задайте для нее название, например frmCriteria.
Шаг 2. Создайте блок кода, проверяющий, загружена ли форма параметров
На вкладке Создание в группе Макросы и код нажмите кнопку Модуль.
В редакторе Visual Basic откроется новый модуль.
Вставьте в редакторе следующий код:
Сохраните модуль под уникальным именем и закройте редактор Visual Basic.
Шаг 3. Создайте макрос, управляющий работой формы и отчета
С помощью функций вложенных макросов в макросах Access можно определить все необходимые действия, которые необходимо выполнить в одном макросе. Мы создадим четыре вложенных макроса: открыть диалоговое окно, закрыть диалоговое окно, ОК и отменить, чтобы управлять различными задачами, необходимыми для этой процедуры. С помощью снимка экрана ниже в качестве руководства создайте новый макрос со следующими вложенными макросами и действиями. Обратите внимание, что в этом примере наша форма параметра называется frmCriteria. Измените макрос таким образом, чтобы он соответствовал имени созданной ранее формы. Кроме того, вы должны убедиться, что на вкладке конструктор выбран пункт Показать все действия , чтобы просмотреть все макрокоманды.
Сохраните и закройте макрос. Задайте для макроса имя, например Макрос диапазона дат.
Шаг 4. Добавьте на форму кнопки «ОК» и «Отмена»
Снова откройте форму параметров, которую создали в режиме конструктора.
На вкладке Конструктор в группе Элементы управления отмените выбор параметра Использовать мастера.
На вкладке Конструктор в группе Элементы управления щелкните Кнопка.
Расположите курсор под полями на форме и мышью создайте кнопку ОК.
Если окно свойств не отображается, откройте его, нажав клавишу F4.
Задайте для кнопки ОК свойства согласно приведенной ниже таблице.
Введите имя макроса, например Макрос диапазона дат.ОК.
Создайте кнопку Отмена и задайте для нее свойства, как показано в таблице ниже.
Введите имя макроса, например Макрос диапазона данных.Отмена.
Сохраните и закройте форму.
Шаг 5. Используйте значения из формы в качестве условий запроса
Откройте запрос, который создали в режиме конструктора.
Введите условия для данных. Воспользуйтесь объектом Forms , именем формы и именем элемент управления.
Например, в базе данных Access (ACCDB или MDB) для формы frmCriteria можно задать следующее выражение, ссылающееся на элементы управления «Дата начала» и «Дата окончания» в запросе:
Between [Формы]![frmCriteria]![Дата начала] And [Формы]![frmCriteria]![Дата окончания]
Шаг 6. Добавьте макрокоманды в события отчета
Откройте нужный отчет в режиме конструктора.
Если окно свойств не отображается, откройте его, нажав клавишу F4.
Убедитесь, что для свойства Источник записей в отчете используется запрос с параметрами, который мы создали.
Задайте два дополнительных свойства отчета, как показано в таблице ниже.
Введите имя макроса, например Макрос диапазона дат.Открыть диалоговое окно.
Введите имя макроса, например Макрос диапазона дат.Закрыть диалоговое окно.
При открытии отчета Access будет выполнять команды, содержащиеся во вложенном макросе «Открыть диалоговое окно» объекта «Макрос диапазона дат». И аналогично: при закрытии отчета Access будет выполнять команды, содержащиеся во вложенном макросе «Закрыть диалоговое окно» объекта «Макрос диапазона дат».
Сохраните и закройте отчет.
Шаг 7. Проверьте форму в действии
Теперь, когда все объекты Access созданы, можно проверить, как они работают. Откройте отчет в режиме отчета или представлении для печати и обратите внимание, что перед показом отчета Access отображает форму ввода параметров в диалоговом режиме. Введите необходимые условия в созданные вами текстовые поля и нажмите кнопку ОК на форме. После этого Access скроет форму (вывод на экран — «Нет») и отобразит отчет, содержащий только те данные, которые соответствуют вашим условиям. Это произойдет благодаря тому, что запрос с параметрами, на котором основан отчет, использует значения в элементах управления на скрытой форме. Когда вы закроете отчет, Access также закроет форму параметров.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Запросы с параметрами
Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы демонстрировали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение и затем получить нужный результат.
Покажем, как создавать запросы с параметрами на примере запроса «Отсортированный список товаров», который мы создавали ранее. Теперь мы с помощью этого запроса попробуем отобрать товары, поставляемые определенным поставщиком. Для этого:
- Откройте данный запрос в режиме Конструктора.
- Чтобы определить параметр запроса, введите в строку Условие отбора (Criteria) для столбца «Название» (CompanyName) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например [Поставщик:]. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
- Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите из контекстного меню команду Параметры (Parameters) или выполните команду меню Запрос, Параметры (Query, Parameters). Появляется диалоговое окно Параметры запроса (Query Parameters), представленное на рис. 4.31.
Рис. 4.31. Диалоговое окно Параметры запроса
- В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования через буфер обмена), только можно не вводить квадратные скобки. В столбце Тип данных (Data Type) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.
- Нажмите кнопку Запуск (Run) на панели инструментов, чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Input Parameter Value) (рис. 4.32), в которое нужно ввести значение, например Tokyo Traders. Результат выполнения запроса представлен на рис. 4.33. В него попадают только те товары, которые поставляются данным поставщиком.
В одном запросе можно ввести несколько параметров. При выполнении такого запроса для каждого из параметров будут поочередно выводиться диалоговые окна Введите значение параметра в том порядке, в котором параметры перечислены в бланке запроса.
Рис. 4.32. Диалоговое окно Введите значение параметра
Рис. 4.33. Результат выполнения запроса с параметром
При использовании запроса с параметрами делайте понятными их имена, т . к. именно они будут выводиться в диалоговом окне для пользователя.
Passing a parameter in a Report’s Open Event to a parameter query (Access 2007)
I would like to know if there is a way to set the parameters in an Access 2007 query using VBA. I am new to using VBA in Access, and I have been tasked with adding a little piece of functionality to an existing app.
The issue I am having is that the same report can be called in two different places in the application. The first being on a command button on a data entry form, the other from a switchboard button. The report itself is based on a parameter query that has requires the user to enter a Supplier ID.
The user would like to not have to enter the Supplier ID on the data entry form (since the form displays the Supplier ID already), but from the switchboard, they would like to be prompted to enter a Supplier ID.
Where I am stuck is how to call the report’s query (in the report’s open event) and pass the SupplierID from the form as the parameter. I have been trying for a while, and I can’t get anything to work correctly. Here is my code so far, but I am obviously stumped.
Private Sub Report_Open(Cancel As Integer)
I’ve tried Me.Supplier >
PARAMETERS [Enter Supplier] Long; SELECT Suppliers.Supplier >
I know there are ways around this problem (and probably an easy way as well) but like I said, my lack of experience using Access and VBA makes things difficult. If any of you could help, that would be great!
Создан 15 май. 10 2010-05-15 04:39:19 JPM
2 ответа
The suggestion being made here is to 100% REMOVE the parameter from the query. This not only solves your problem, but then means you can use the query for code, other forms and not have your whole design fall apart because one stupid form is not open (hence the VERY reason for your question).
So, remove the parameters from the query. This also means that your report will now not need some form that already opened. And again, if some silly form is not opened, why should your report fail to work?
So, remove the parameter. Now, in your form that opens the report, it can pass the filter, and more in point use what is a called a «where» clause. This «where» clause is designed in MS-access to solve the problem of having to know ahead of time what kind of parameters and filters you need. It occurs at runtime, and thus MANY DIFFERENT forms can call and open that report.
Now in the form that calls and opens the form, you go:
So, in the above, the parameter is created on the fly. The great advantage is tomorrow you can have another form open the same report and perhaps filter by region.
In the case of NO where clause being passed and a user simply opening the form, then no filters will be used and no prompts will occur and all records will show. This is probably your best approach.
However if for some strange reason you still deem it REALLY necessary to have some report prompt when one silly form just happens to not be opened, then place the following code in the forms on-open event.
However, I would really make efforts to avoid hard coding some silly form name in the reports open event. Not only does this mean your hard coding dependencies of some silly form that is now attached to a report, but if you later on copy that report, or even copy the original form (or even rename any of these objects), then you have to go into the application and hunt about and now find the places you as a developer introduced dependences. This approach can substantially increase the maintenance costs of an application and thus should be advoied.
So, the suggestion here is to dump the parameter query. Simply provide a form or some prompt system to launch the reports. Those forms should prompt the user for the information you wish to filter. Or as in your case the bound form and it current record provides that information. The beauty of this system is now there is no depdancy from the report.
Any form, or even any code down the road is free to pass a pramaeter, and it will not be limited to SupplierID, but can be any type of filter or parameter you wish.
Keep in mind that perhaps the user might not want that form to be open and perhaps they don’t want the prompt. With your design and question the user will be forced to enter a parameter value even when launching the report without any forms open and not desiring to be prompted to allow them to view all reocrds in that report.
Создан 16 май. 10 2010-05-16 18:19:09 Albert D. Kallal
I think you’re recommendation to never hardwire a reference to a criteria form in the OnOpen is overstated. I do it all the time. In the few cases where I also want to provide some method to bypass the criteria form, I use OpenArgs in A2003 and later, and provide a WhereCondition and check the report’s .Filter property in the OnOpen to skip the opening of the form. – David-W-Fenton 16 май. 10 2010-05-16 19:45:04
Thanks Albert. Your advice was great, but in the end, I was told to go with the solution I mentioned in my comment to David. Anyways, after working on this ticket for a while, it got to the point where the time to make it work as it should be would be too much for the ticket. Then again, this app should just be scrapped and redone since it’s a horrible mess. Anyways, thanks again for the advice. As I mentioned in my response to David, I am new here, so I can’t vote you up just yet either. – JPM 18 май. 10 2010-05-18 01:18:35
I’m honestly not a big fan of the «refactor your entire application so that it’s done the way I think it should be» answers. This may be good commentary on best practices, but it doesn’t really answer the question at all. – Hill 27 сен. 16 2016-09-27 21:30:40
As I outlined in a recent post, I tend never to hardwire any parameters or form control references into the recordsources of reports or forms. Instead, I set them at runtime. The simplest way is by passing the WhereCondition property in the DoCmd.OpenForm/DoCmd.OpenReport:
That assumes you’re running it from a form that has the relevant SupplierID already present in its recordsource (i.e., you’re on a record with that SupplierID).
More complicated is to use the OnOpen event of the report to set the reports’s recordsource. That’s what I outlined in the cited post above. But that example hardwires the choice to a selection form, whereas you might want to instead offer different sets of choices depending on context. There are two ways to handle that:
if A2003 and later, pass an OpenArg (the last parameter of the DoCmd.OpenReport) to tell the OnOpen event what to do to collect the information on what to filter to.
use an outside structure like a standalone class module to store criteria that the OnOpen event will read and act upon accordingly.
I suspect that the WhereCondition in the DoCmd.OpenReport is your easiest solution, but if you want details on the other two, just ask.
Создан 15 май. 10 2010-05-15 19:12:07 David-W-Fenton
Thanks for the response David. But I think my lack of experience is really showing, so I must apologize. What I would like to have happen is in the Open event of the Report, issue the parameter prompt of the report’s record source query only if the data entry form is not open. If the data entry form is open, just grab the Supplier ID from the form itself. I am still stuck, because no matter what I do, the parameter prompt still shows, or it bombs entirely. I’ve tried the line of code you’ve suggested, but I still get prompted for the SupplierID. I am, however, not familiar with the OpenArgs. – JPM 15 май. 10 2010-05-15 20:19:24
I’m suggesting you remove the parameters entirely from the report’s recordsource and instead collect the values you want and supply them in the WhereCondition of the DoCmd.OpenReport. This is much simpler and removes any dependency on outside objects from the reports. It allows you to open the report to show all records, or to filter it to any set of records you like at runtime. Albert has explained it all quite well in his answer. – David-W-Fenton 16 май. 10 2010-05-16 19:41:41
Thanks David. I brought up this up with my mentor (I’m a College student working on my summer co-op position), and although it makes sense to remove the dependencies that presently exist, the solution was to create two queries, and dependent on whether the data entry form is open, apply the report’s record source accordingly. It’s ugly, but so is this app. There are absolutely NO naming standards present, and that’s if they are even named to begin with. Dealing with a bunch of Text39’s and CommandButton291’s is making things difficult enough as it is. But in the end, I do what I’m told. – JPM 18 май. 10 2010-05-18 01:08:19
I’m also a new member here, so I don’t have the ability to vote you up just yet, so I will just have to extend my gratitude for now. Thanks again! – JPM 18 май. 10 2010-05-18 01:09:49
I’m not grubbing for reputation points, just trying to help, so you your thanks is more than enough reward. – David-W-Fenton 18 май. 10 2010-05-18 20:23:49
You also might want to suggest to your mentor that a useful project for you would be to «refactor» the Access app by converting to reasonable naming conventions. An excellent way to do this is to temporarily turn on Name AutoCorrect, and then alter the field names in the base tables (if the app is split, this will only work if you temporarily import the real tables, do the name changes, and then revert to the linked tables). This would be a good investment in terms of maintainability and expandability for the future, and in my opinion, a good use of a coop student’s time. – David-W-Fenton 18 май. 10 2010-05-18 20:27:40
Thanks David. Any help is greatly appreciated, and unfortunately, I don’t have the experience needed to offer any useful advice, so I feel bad that I can’t express my gratitude by voting up. But like I said, I really appreciate any help you have given me. I would be interested in doing the refactoring, but the tickets won’t stop coming, and from what I’ve heard from the other programmers is that they are backlogged with enough work to last them 2 years. I guess I will be maintaining this app for the entire summer, as all of my tickets have been for this app. 🙁 – JPM 18 май. 10 2010-05-18 23:06:04