Создание интерактивных офисных документов в Excel

Запросы с фильтрацией

Возможно, Вы обратили внимание на то, как я решал данную задачу по организации поиска нужных мне данных, - вначале получил весь набор записей, а затем организовал их фильтрацию средствами VBA. Возможен и другой способ, в ряде случаев более предпочтительный, - он состоит в том, чтобы фильтрацию выполнять в момент чтения набора записей, задав соответствующим образом оператор SELECT языка SQL. Оператор Select имеет в своем арсенале конструкцию Like, позволяющую задать шаблон поиска и тем самым проводить фильтрацию непосредственно при чтении записей, что может приводить к существенному уменьшению объема набора записей, передаваемого клиенту.

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

Начнем повторно наше рассмотрение с того момента, когда после задания критериев поиска пользователь нажимает кнопку "Найти" в форме LookCustomer. Я изменил код обработчика события Click для этой кнопки следующим образом:

Private Sub CommandButton1_Click()
	'Найти заказчика по заданным реквизитам
	'LookingFor
	Version2LookingFor
End Sub

Как видите, вместо ранее вызываемой процедуры LookingFor я буду вызывать ее версию, которую и рассмотрим подробно. Начнем с текста:

Public Sub Version2LookingFor()
 'Найти заказчика по заданным реквизитам
	If (LookCustomer.TextBox1 <> "") Or (LookCustomer.TextBox2 <> "") Or _
		(LookCustomer.TextBox3 <> "") Or (LookCustomer.TextBox4 <> "") Or _
		(LookCustomer.TextBox5 <> "") Then
		'Критерии поиска заданы.
		'Спрятать форму.
		LookCustomer.Hide
		'Создать набор отфильтрованных записей с реквизитами заказчиков.
		CreateFiteredCustomers
		'Сформировать список заказчиков, удовлетворяющих критериям поиска.
		FormListSelectedCustomers
		If SelectedCustomers.ListBox1.ListCount > 0 Then
			'Найдены заказчики, удовлетворяющие критериям.
			SelectedCustomers.Show
		Else
			'Показ всех заказчиков.
			MsgBox ("Нет записей, удовлетворяющих заданным критериям!" _
				& " Будут показаны все заказчики!")
			Choose
		End If
	Else
		MsgBox ("Задайте значение хотя бы в одном поле!")
	End If
End Sub

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

Приведу программный код процедуры CreateFilteredCustomers:

Public Sub CreateFiteredCustomers()
	'Создание	и выполнение команды,
	'позволяющей получить данные о заказчиках,
	'удовлетворяющих фильтру
	Dim strSQL1 As String
	'Вызов функции FormSQLStatement, формирующей строку SQL
	strSQL1 = FormSQLStatement
	'задание объекта Command
	Cmd1.CommandText = strSQL1
	'вызов команды на исполнение методом Execute
	Set Rst1 = Cmd1.Execute
	'Перенос данных из набора записей в список
	SelectedCustomers.ListBox1.Clear
	RowIndex = 0
	With Rst1
		.MoveFirst
		Do While Not .EOF
				'Текущая запись переносится в список
				'Первый столбец
				SelectedCustomers.ListBox1.AddItem .Fields(1)
				'Остальные столбцы
				On Error Resume Next
				 For i = 1 To ColumnCount - 1
					txt = ""
					txt = .Fields(i + 1)
					SelectedCustomers.ListBox1.Column(i, RowIndex) = txt
				Next i
				RowIndex = RowIndex + 1
			.MoveNext
		Loop
	End With
 End Sub

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

То новое, на что хочу обратить внимание, связано с формированием строки SQL. Как положено, я написал специальную функцию FormSQLStatement, которая и решает поставленную задачу. Именно она и интересует нас в первую очередь. Вот ее текст:

Public Function FormSQLStatement() As String
	'Возвращает SQL оператор, фильтрующий записи
	Dim strSQL As String
		strSQL = "Select * FROM [Заказчики] WHERE "
	Dim txt As String
	Const Кавычка = "'"
	txt = LookCustomer.TextBox1.Text
	If txt <> "" Then strSQL = strSQL & "[Название] Like " & _
		 Кавычка & "%" & txt & "%" & Кавычка & " Or "
	txt = LookCustomer.TextBox2.Text
	If txt <> "" Then strSQL = strSQL & "[Адрес] Like " & _
		Кавычка & "%" & txt & "%" & Кавычка & " Or "
	txt = LookCustomer.TextBox3.Text
	If txt <> "" Then strSQL = strSQL & "[Город] Like " & _
		Кавычка & "%" & txt & "%" & Кавычка & " Or "
	txt = LookCustomer.TextBox4.Text
	If txt <> "" Then strSQL = strSQL & "[Телефон] Like " & _
		Кавычка & "%" & txt & "%" & Кавычка & " Or "
	txt = LookCustomer.TextBox5.Text
	If txt <> "" Then strSQL = strSQL & "[Директор] Like " & _
		Кавычка & "%" & txt & "%" & Кавычка & " Or "
	'Удалить последние пять символов - Or
	strSQL = Left(strSQL, Len(strSQL) - 4)
	FormSQLStatement = strSQL
End Function

Поговорим подробнее о работе этой функции. Наша цель состоит в том, чтобы задать сложное выражение WHERE оператора Select, задающее фильтр. По условиям задачи это выражение состоит из нескольких слагаемых - логических условий, соединенных связкой OR (ИЛИ). Число слагаемых формируется динамически и может быть от одного до пяти, в зависимости от того, сколько полей заполнил пользователь в форме LookCustomer. Напомню, что хотя бы одно поле он обязан задать, чтобы можно было говорить о ключе поиска. Все слагаемые формируются по одной схеме, - если задано соответствующее ключевое поле в форме, то формируется и соответствующее ему слагаемое.

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

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

Замечание 1. Шаблоны не новость ни в математике, ни в программировании. Традиционно, символ * (звездочка) в шаблоне соответствует произвольной строке символов, а символ ? (знак вопроса) соответствует произвольному одиночному символу. Мне совершенно непонятно, почему в шаблонах на VBA нужно было изменять традиции и использовать другие символы - знак % (процент) вместо звездочки и знак _ (подчеркивание) вместо знака вопроса. Это тем более странно, что при формировании запроса с выражением Like непосредственно в Access используются традиционные символы.

Замечание 2. Экспериментируя с шаблонами, я наткнулся на одну ошибку в Access, связанную, видимо, с локализацией. Ошибка проявляется в следующей ситуации. Пусть в Access с помощью конструктора строится запрос по таблице, у которой первое поле является ключевым и содержит, скажем, название организации. Предположим, что на это поле накладывается фильтр типа Like - S* , где S - буква русского алфавита. Этот фильтр, естественно, означает, что запросу удовлетворяю организации, начинающиеся с буквы S . Так вот, все работает правильно за исключением, когда в качестве начальной буквы задается "Г" или "К". Замечу, что в других полях шаблон "Г*" работает правильно.

Сохранение реквизитов

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

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

Private Sub CommandButton2_Click()
	'Сохранить реквизиты заказчика
	CreateNewCustomer
End Sub

Процедура CreateNewCustomer, решающая задачу имеет вид:

Public Sub CreateNewCustomer()
	'добавление записи в базу данных
	Dim recExist As Boolean
	Dim curField As Range
	Dim txtField As String, txtTel As String, txtMail As String
	Cmd1.CommandText = "Select * From [Заказчики]"
	Set curField = Range("D19")	'Поле с названием организации
	txtField = curField.Text
	'Открытие обновляемого объекта Recordset
	With Rst1
		If Rst1.State = adStateOpen Then Rst1.Close
		.Open Source:=Cmd1, CursorType:=adOpenDynamic, _
			LockType:=adLockOptimistic
		
		recExist = False
		.MoveFirst
		Do While Not .EOF
			'Проверка существования записи
			If LCase(!Название) = LCase(txtField) Then recExist = True
			.MoveNext
		Loop
		If Not recExist Then
			 .AddNew
			!Название = txtField
			txtField = curField.Offset(1).Text
			!Адрес = txtField
			txtField = curField.Offset(2).Text
			Call Parsing(txtField, txtTel, txtMail)
			!Телефон = txtTel
			!Email = txtMail
			'Запрос значений недостающих полей
			AddingFields.Show
			.Update
		Else
			MsgBox ("В базе данных уже существует" _
			& " организация с таким названием!")
		End If
	End With
End Sub

На три момента хочу обратить внимание:

• При добавлении записей в базу данных приходится иметь дело с обновляемым набором записей, который открывается методом Open, а не создается, как это было ранее, при выполнении метода Execute объекта Command. Подробнее об этом я писал в предыдущих главах, посвященных объектам ADO.
• Наш пример сконструирован так, что он позволяет продемонстрировать некоторые побочные проблемы, возникающие при передаче данных из документа в базу данных. В частности, информация о телефоне и электронном адресе записана в одном поле бланка документа. Поэтому, прежде чем она попадет в соответствующие поля записи базы данных, необходим разбор соответствующей строки. В нашем случае разбор прост, и осуществляет его вызываемая процедура Parsing. Приведу текст этой процедуры:

  Public Sub Parsing(Field As String, Tel As String, Mail As String)
  	'разбор поля бланка, содержащего телефон и EMail организации
  	Dim Ind1 As Integer
  	Ind1 = InStr(5, Field, "Email")
  	Tel = Mid(Field, 5, Ind1 - 5)
  	Mail = Right(Field, Len(Field) - Ind1 - 5)
  End Sub
• Еще одна часто возникающая проблема состоит в том, что запись базы данных может содержать больше информации, чем задается в полях документа. В этом случае целесообразно запросить всю необходимую информацию. Я демонстрирую этот подход на примере полей "Город" и "Директор", которые есть в базе данных, но не вынесены в раздел реквизитов нашего документа. Заметьте, с этой целью, прежде чем обновить запись, вызывается специальная форма, которой я дал имя AddingFields, и которая содержит текстовые поля для занесения необходимой информации. Вот как выглядит эта форма в процессе работы:

Рис. 7.6. Форма AddingFields, позволяющая добавить сведения о полях базы данных

При нажатии кнопки "OK" формы AddingFields текст из полей формы переносится в поля записи, после чего форма закрывается:

Private Sub CommandButton1_Click()
	'Добавление полей "Город" и "Директор" в запись
	Rst1!Город = Me.TextBox1.Text
	Rst1!Директор = Me.TextBox2.Text
	Me.Hide
End Sub

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