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

Перенос результатов запроса в поля бланка

Приведу вначале текст процедуры FromRstToFields, а потом уже дам краткий комментарий, поясняющий ее работу:

Public Sub FromRstToFields()
'Перенос данных из набора записей Rst1 в поля бланка
	Dim curField As Range
	VBA.Randomize
	Set curField = Range("D19")	'Поле с названием организации
	curField = Rst1!Название
	curField.Offset(1) = Rst1!Адрес
	curField.Offset(2) = "tel: " & Rst1!Телефон & _
		"	Email: " & Rst1!Email
	curField.Offset(3) = VBA.Int(VBA.Rnd * 999999999 + 100000000)
	'Заполнение полей - грузотправитель и грузополучатель
	Set curField = Range("E25:J25")
	curField = "Издательство: Родная Речь, " & Range("F9")
	curField.Offset(1) = Rst1!Название & ", " & Rst1!Адрес
End Sub

Процедура, как и положено, коротенькая, так что текст ее самодостаточен для понимания. Значения полей записи присваиваются свойству Value объектов Range, задающих поля бланка. В последней части процедуры заполняются поля документа, задающие грузоотправителя и грузополучателя, в качестве которых выступает офис РР и выбранный заказчик книг.

На этом можно было бы закончить комментарий, если бы не необходимость привести некоторые пояснения моим читателям. Дело в том, что я проектировал бланк документа и базу данных в разное время. По этой причине есть определенная несогласованность между реквизитами заказчика, представленными на бланке и реквизитами, хранящимися в таблице "Заказчики" базы данных офиса РР. В частности в базе данных хранятся такие реквизиты, как, например, "Город", "Директор", которые не вынесены на бланк, но нет реквизита "ИНН", присутствующего на бланке. Я не стал приводить в полное соответствие формат бланка документа и базу данных, найдя в этом даже некоторую пользу для целей обучения. Примером того, что некоторые поля документа могут быть вычислимыми, в данном случае является поле "ИНН", которое я формирую случайным образом, используя функции Randomize и Rnd.

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

Поиск заказчика

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

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

Private Sub CommandButton3_Click()
	'Поиск заказчика
	'Открыть форму для задания реквизитов поиска
	LookCustomer.Show
End Sub

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

Рис. 7.4. Форма LookCustomer для задания ключей поиска

В приведенном на рисунке примере будут отобраны все заказчики, в названии которых содержится слово "лавка" или директором которых является "Рондова", или заказчики, расположенные в Твери. Заметьте, не обязательно задание всех полей формы, достаточно задание хотя бы одного поля. Обращаю внимание и на мое решение о том, что при поиске поля будут объединяться логической связкой " ИЛИ ", а не " И ".

Нажатие кнопки "Найти" в форме LookCustomer инициирует начало поиска. Взгляните на код обработчика события Click:

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

Реальную работу выполняет процедура LookingFor стандартного модуля. Вот ее текст:

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

Приведу краткие комментарии к работе этой процедуры:

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

  Public Sub CreateCustomers()
  	'Создание	и выполнение команды,
  	'позволяющей получить данные о заказчиках
  	Dim strSQL1 As String
  	strSQL1 = "Select * FROM [Заказчики]"
  	'задание объекта Command
  	Cmd1.CommandText = strSQL1
  	'вызов команды на исполнение методом Execute
  	Set Rst1 = Cmd1.Execute
  End Sub
• После того, как получены данные обо всех заказчиках, идет отбор заказчиков, удовлетворяющих заданным критериям поиска. Эта основная задача решается в процедуре FormListSelectedCustomers. О ней подробнее скажу чуть позже.
• Если найдутся заказчики, удовлетворяющие заданным условиям поиска, то их список будет показан в специальной форме SelectedCustomers для окончательного выбора между ними. Замечу, что в отличие от ранее приводимого списка, содержащего только названия заказчиков, теперь для выбора предъявляется список из нескольких столбцов, содержащий все основные реквизиты.
• Если по результатам поиска не будет найден ни один из заказчиков, то вызывается процедура Choose, которая уже рассмотрена ранее. Заметьте, в этом случае будет показан полный список всех заказчиков.

Формирование и показ списка отобранных заказчиков

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

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

Приведу текст процедуры, которая решает эту задачу:

Public Sub FormListSelectedCustomers()
	'Готовит список заказчиков, удовлетворяющих критериям поиска
	Dim txt As String
	Dim i As Integer
	Dim RowIndex As Integer
	Dim B1 As Boolean, B2 As Boolean, B3 As Boolean
	Dim B4 As Boolean, B5 As Boolean
	'Число столбцов списка - реквизитов поиска
	Const ColumnCount = 5
	
	'Поиск заказчиков в наборе по заданным реквизитам
	SelectedCustomers.ListBox1.Clear
	SelectedCustomers.ListBox1.ColumnCount = ColumnCount
	RowIndex = 0
	With Rst1
		.MoveFirst
		Do While Not .EOF
			B1 = False: B2 = False: B3 = False: B4 = False: B5 = False
			On Error Resume Next
			txt = LookCustomer.TextBox1.Text
			B1 = txt <> "" And VBA.InStr(!Название, txt)
			txt = LookCustomer.TextBox2.Text
			B2 = txt <> "" And VBA.InStr(!Адрес, txt)
			txt = LookCustomer.TextBox3.Text
			B3 = txt <> "" And VBA.InStr(!Город, txt)
			txt = LookCustomer.TextBox4.Text
			B4 = txt <> "" And VBA.InStr(!Телефон, txt)
			txt = LookCustomer.TextBox5.Text
			B5 = txt <> "" And VBA.InStr(!Директор, txt)
			If B1 Or B2 Or B3 Or B4 Or B5 Then
				'Текущая запись переносится в список
				'Первый столбец
				SelectedCustomers.ListBox1.AddItem .Fields(1)
				'Остальные столбцы
				For i = 1 To ColumnCount - 1
					txt = ""
					txt = .Fields(i + 1)
					SelectedCustomers.ListBox1.Column(i, RowIndex) = txt
				Next i
				RowIndex = RowIndex + 1
			End If
			.MoveNext
		Loop
	End With
	
End Sub

Фильтр, через который проходят все записи, состоит из пяти условий, объединенных операцией " ИЛИ ". Каждое условие определяет, задан ли ключ для определенного поля записи, и, если да, то является ли ключевое слово частью поля записи. Записи, прошедшие фильтр, добавляются в список формы SelectedCustomers, при этом поля записи переносятся в соответствующие столбцы списка. Обратите внимание на оператор обработки исключительных ситуаций - On Error Resume Next - позволяющий справиться с возможной ошибкой при выполнении функции InStr, возникающей в ситуации, когда некоторые из полей записи базы данных не определены.

В заключение, взгляните, как выглядит спроектированная форма SelectedCustomers в процессе работы:

Рис. 7.5. Форма SelectedCustomers, содержащая список выбранных заказчиков

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

Private Sub CommandButton1_Click()
	'Данные о реквизитах заказчика переносятся
	'из набора записей в поля бланка Счет-фактура
	FromSelectedListToFields
	
End Sub

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

Public Sub FromSelectedListToFields()
	'Данные о заказчике, выбранном из списка,
	'ищутся в базе данных и затем переносятся
	'из набора записей в поля бланка Счет-фактура
	Const Кавычка = "'"
	Dim Key As String
	Dim strSQL1 As String
	
	If SelectedCustomers.ListBox1.ListIndex >= 0 Then 'Выбор сделан
		'Формирование запроса к базе данных
		Key = SelectedCustomers.ListBox1.Column _
				(0, SelectedCustomers.ListBox1.ListIndex)
		strSQL1 = "Select * FROM [Заказчики]WHERE [Название]= " _
			& Кавычка & Key & Кавычка
		Cmd1.CommandText = strSQL1
		Set Rst1 = Cmd1.Execute
		'Перенос данных из набора записей в поля бланка
		FromRstToFields
	 'Форма сделала свое дело - форма закрывается
	 SelectedCustomers.Hide
	Else
		MsgBox ("Выберите заказчика!")
	End If
End Sub

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