Список використаних джерел

1 2 3 4 5 6 7

1. Дейт К. Дж.. Введение в систему баз данных.– СПб.: БХВ-Петербург, 2005. – 1309 с.

2. Мейер М. Теория реляционных баз данных. – М.: Мир, 1987 – 249с.

3. Дунаев В.В. Бази данных. Язык SQL. – СПб.: БХВ-Петербург, 2006. – 288 с.

4. Вікіпедія - http://uk.wikipedia.org/wiki/

5. Мельниченко Е.В., Самоцветов К.В. SQL. – М: Мир, 1996 – 315с.

6. Тихомиров А.Н., Прокди А.К. Microsoft Office. Все программы пакета. – СПб.: Наука и Техника, 2008. – 608 с.

7. Сергеев А. Access 2007. Новые возможности. – СПб.: Питер, 2008. – 176 с.

8. Демидова Л.А., Пылькин А.Н. Програмирование в среде VBA. – М.: Горячая линия – Телеком, 2004. – 175 с.

9. Хорев В.Д. Самоучитель програмирования на VBA а MS Office. – K.: Юниор, 2001. – 320 с.

ДОДАТОК А

Лістинг програми

UserForm1

Private Sub CommandButton1_Click()

emp

If OptionButton1.Value = True Then

str1 = TextBox1.Text

If (str1 = "1111") Then

TextBox1.Text = ""

Me.Hide

UserForm2.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

If OptionButton2.Value = True Then

str1 = TextBox1.Text

If (str1 = "2222") Then

TextBox1.Text = ""

Me.Hide

UserForm2.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

End Sub

Private Sub CommandButton2_Click()

emp

If OptionButton1.Value = True Then

str1 = TextBox1.Text

If (str1 = "1111") Then

TextBox1.Text = ""

Me.Hide

UserForm3.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

If OptionButton2.Value = True Then

str1 = TextBox1.Text

If (str1 = "2222") Then

TextBox1.Text = ""

Me.Hide

UserForm3.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

End Sub

Private Sub CommandButton3_Click()

emp

If OptionButton1.Value = True Then

str1 = TextBox1.Text

If (str1 = "1111") Then

TextBox1.Text = ""

Me.Hide

UserForm4.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

If OptionButton2.Value = True Then

str1 = TextBox1.Text

If (str1 = "2222") Then

TextBox1.Text = ""

Me.Hide

UserForm4.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

End Sub

Private Sub CommandButton4_Click()

emp

If OptionButton1.Value = True Then

str1 = TextBox1.Text

If (str1 = "1111") Then

TextBox1.Text = ""

Me.Hide

UserForm5.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

If OptionButton2.Value = True Then

str1 = TextBox1.Text

If (str1 = "2222") Then

TextBox1.Text = ""

Me.Hide

UserForm5.Show

Else: MsgBox "Ïàðîëü íå ïðàâèëüíèé!"

TextBox1.Text = ""

End If

TextBox1.Text = ""

End If

End Sub

Private Sub OptionButton1_Click()

CommandButton1.Enabled = True

CommandButton2.Enabled = True

End Sub

Private Sub OptionButton2_Click()

CommandButton1.Enabled = False

CommandButton2.Enabled = False

End Sub

Public Sub emp()

If OptionButton1.Value = False And OptionButton2.Value = False Then

MsgBox "Âèáåð³òü òèï êîðèñòóâà÷à"

End If

End Sub

UserForm2

Dim cn As ADODB.Connection

Dim rs As New ADODB.Recordset

Dim rs1 As New ADODB.Recordset

Private Sub CommandButton10_Click()

If (TextBox1.Text = Empty) Or (TextBox2.Text = Empty) Or (TextBox3.Text = Empty) Or (TextBox4.Text = Empty) Or (TextBox5.Text = Empty) Or (TextBox7.Text = Empty) Or (TextBox8.Text = Empty) Then

MsgBox "Ïîìèëêà!!! Âèáåð³òü çàïèñ, ÿêèé ïîòð³áíî âèäàëèòè"

rs.MoveFirst

Else

If rs.RecordCount >= 1 Then

If MsgBox("Âè ä³éñíî áàæàºòå âèäàëèòè çàïèñ?", vbYesNo + vbQuestion) = vbYes Then

rs.Delete

If rs.RecordCount > 0 Then

CommandButton4_Click

Else

ShowEmptyRecord

End If

rs.MoveFirst

ShowEmptyRecord

End Sub

Private Sub CommandButton11_Click()

CommandButton9.Enabled = False

CommandButton8.Enabled = False

CommandButton7.Enabled = False

CommandButton6.Enabled = False

CommandButton12.Enabled = False

CommandButton17.Enabled = False

ShowEmptyRecord1

rs1.MoveLast

rs1.AddNew

TextBox9.SetFocus

End Sub

Private Sub CommandButton12_Click()

If (TextBox9.Text = Empty) Or (TextBox10.Text = Empty) Or (TextBox11.Text = Empty) Or (TextBox12.Text = Empty) Or (TextBox13.Text = Empty) Or (TextBox16.Text = Empty) Then

MsgBox "Ïîìèëêà!!! Âèáåð³òü çàïèñ, ÿêèé ïîòð³áíî âèäàëèòè"

rs1.MoveFirst

Else

If rs1.RecordCount >= 1 Then

If MsgBox("Âè ä³éñíî áàæàºòå âèäàëèòè çàïèñ?", vbYesNo + vbQuestion) = vbYes Then

rs1.Delete

If rs1.RecordCount > 0 Then

CommandButton8_Click

Else

ShowEmptyRecord1

End If

rs1.MoveFirst

ShowEmptyRecord1

End Sub

Private Sub CommandButton13_Click()

FillRecord1

End Sub

Private Sub CommandButton14_Click()

FillRecord

End Sub

Private Sub CommandButton15_Click()

CommandButton13_Click

End Sub

Private Sub CommandButton16_Click()

CommandButton14_Click

End Sub

Private Sub CommandButton17_Click()

rs.Close

rs1.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

Me.Hide

UserForm1.Show

End Sub

Private Sub CommandButton4_Click() ' îñòàíí³é çàïèñ îðåíäàòîð

If Not rs.EOF Then

rs.MoveNext

If Not rs.EOF Then

ShowRecord

Else

rs1.MoveLast

End If

End Sub

Private Sub UserForm_Activate()

ComboBox1.AddItem ("Áàçàð")

ComboBox1.AddItem ("Îõîðîíà")

ComboBox1.AddItem ("Îô³ñ")

ComboBox1.AddItem ("Ëàáîðàòîð³ÿ")

ComboBox1.AddItem ("Ñêëàä")

ComboBox2.AddItem ("Çàâ³äóþ÷èé")

ComboBox2.AddItem ("Ïðèáèðàëüíèê")

ComboBox2.AddItem ("Ñòîðîæ")

ComboBox2.AddItem ("Âàíòàæíèê")

ComboBox2.AddItem ("Ëàáîðàíò")

ComboBox3.AddItem ("Ì'ÿñî-ìîëî÷íèé")

ComboBox3.AddItem ("Øìîòêè")

ComboBox3.AddItem ("Òåõí³êà")

ComboBox3.AddItem ("²íøå")

ComboBox3.AddItem ("Áóä.ìàòåð³àëè")

ComboBox3.AddItem ("Õ³ì³ÿ")

ComboBox4.AddItem ("Ïóñòî")

ComboBox4.AddItem ("Ïðèëàâîê")

ComboBox4.AddItem ("Ê³îñê 1 ï")

ComboBox4.AddItem ("Ê³îñê 2 ï")

ComboBox4.AddItem ("Áóä³âëÿ")

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

cn.ConnectionString = "D:\Central.mdb"

cn.Open

rs.CursorType = adOpenKeyset

rs1.CursorType = adOpenKeyset

rs.LockType = adLockOptimistic

rs1.LockType = adLockOptimistic

rs.Source = "SELECT Îðåíäàòîð.orendat, Îðåíäàòîð.dogov, Îðåíäàòîð.torg_pat, Ì³ñöå.misce, Ì³ñöå.sector, Ì³ñöå.tovar, Ì³ñöå.electr, Ì³ñöå.voda, Ñêëàä.sklad, Ñêëàä.komir, Ñêëàä.dogov, Ì³ñöå.dogov, Ì³ñöå.orendat, Ñêëàä.orendat, Ì³ñöå.bydova FROM (Îðåíäàòîð INNER JOIN Ì³ñöå ON Îðåíäàòîð.orendat = Ì³ñöå.orendat) INNER JOIN Ñêëàä ON Îðåíäàòîð.orendat = Ñêëàä.orendat"

rs1.Source = "SELECT * FROM Ïðàö³âíèê"

Set rs.ActiveConnection = cn

Set rs1.ActiveConnection = cn

rs.Open

rs1.Open

rs.MoveFirst

ShowRecord1

ShowRecord

End Sub

Private Sub CommandButton1_Click() ' äîäàòè çàïèñ îðåíäàòîðà

CommandButton1.Enabled = False

CommandButton2.Enabled = False

CommandButton3.Enabled = False

CommandButton4.Enabled = False

CommandButton5.Enabled = False

CommandButton10.Enabled = False

CommandButton17.Enabled = False

ShowEmptyRecord

rs.MoveLast

rs.AddNew

TextBox1.SetFocus

End Sub

Private Sub ShowRecord() 'îðåíäàòîð ïîêàçàòè

TextBox1.Text = rs.Fields(0).Value

TextBox2.Text = rs.Fields(1).Value

TextBox3.Text = rs.Fields(2).Value

TextBox4.Text = rs.Fields(3).Value

TextBox5.Text = rs.Fields(4).Value

ComboBox3.Text = rs.Fields(5).Value

CheckBox1.Value = rs.Fields(6).Value

CheckBox2.Value = rs.Fields(7).Value

TextBox7.Text = rs.Fields(8).Value

TextBox8.Text = rs.Fields(9).Value

ComboBox4.Text = rs.Fields(14).Value

End Sub

Private Sub ShowRecord1() ' ïðàö³âíèê ïîêàçàòè

TextBox9.Text = rs1.Fields(0).Value

TextBox10.Text = rs1.Fields(3).Value

TextBox11.Text = rs1.Fields(4).Value

TextBox16.Text = rs1.Fields(5).Value

TextBox12.Text = rs1.Fields(6).Value

TextBox13.Text = rs1.Fields(8).Value

ComboBox2.Text = rs1.Fields(14).Value

ComboBox1.Text = rs1.Fields(18).Value

End Sub

Private Sub CommandButton2_Click()

rs.MoveFirst

ShowRecord

End Sub

Private Sub CommandButton6_Click()

rs1.MoveFirst

ShowRecord1

End Sub

Private Sub CommandButton3_Click() 'ïîïåðåäí³é îðåíäàòîð

If Not rs.BOF Then

rs.MovePrevious

If Not rs.BOF Then

ShowRecord

Else

rs.MoveFirst

End If

End Sub

Private Sub CommandButton7_Click() 'ïîïåðåäí³é ïðàö³âíèê

If Not rs1.BOF Then

rs1.MovePrevious

If Not rs1.BOF Then

ShowRecord1

Else

rs1.MoveFirst

End If

End Sub

Private Sub CommandButton8_Click() ' íàñòóïíèé ïðàö³âíèê

If Not rs1.EOF Then

rs1.MoveNext

If Not rs1.EOF Then

ShowRecord1

Else

rs1.MoveLast

End If

End Sub

Private Sub CommandButton5_Click()

rs.MoveLast

ShowRecord

End Sub

Private Sub CommandButton9_Click()

rs1.MoveLast

ShowRecord1

End Sub

Public Sub ShowEmptyRecord()

TextBox1.Text = Empty

TextBox2.Text = Empty

TextBox3.Text = Empty

TextBox4.Text = Empty

TextBox5.Text = Empty

ComboBox3.Text = Empty

TextBox7.Text = Empty

TextBox8.Text = Empty

ComboBox4.Text = Empty

CheckBox1.Value = False

CheckBox2.Value = False

rs.MoveFirst

End Sub

Public Sub ShowEmptyRecord1()

TextBox9.Text = Empty

TextBox10.Text = Empty

TextBox11.Text = Empty

TextBox12.Text = Empty

TextBox13.Text = Empty

TextBox16.Text = Empty

ComboBox2.Text = Empty

ComboBox1.Text = Empty

rs1.MoveFirst

End Sub

Private Sub FillRecord()

If (TextBox1.Text = Empty) Or (TextBox2.Text = Empty) Or (TextBox3.Text = Empty) Or (TextBox4.Text = Empty) Or (TextBox5.Text = Empty) Or (TextBox7.Text = Empty) Or (TextBox8.Text = Empty) Then

MsgBox "Ïîìèëêà!!! Âñ³ ïîëÿ ìàþòü áóòè çàïîâíåí³!"

Else

If (IsNumeric(TextBox1.Text) = False) And (IsNumeric(TextBox2.Text) = True) And (IsNumeric(TextBox3.Text) = True) And (IsNumeric(TextBox4.Text) = True) And (IsNumeric(TextBox5) = True) And (IsNumeric(TextBox7.Text) = True) And (IsNumeric(TextBox8.Text) = True) Then

rs.Fields(0).Value = TextBox1.Text

rs.Fields(1).Value = TextBox2.Text

rs.Fields(2).Value = TextBox3.Text

rs.Fields(3).Value = TextBox4.Text

rs.Fields(4).Value = TextBox5.Text

rs.Fields(5).Value = ComboBox3.Text

rs.Fields(6).Value = CheckBox1.Value

rs.Fields(7).Value = CheckBox2.Value

rs.Fields(8).Value = TextBox7.Text

rs.Fields(9).Value = TextBox8.Text

rs.Fields(10).Value = TextBox2.Text

rs.Fields(11).Value = TextBox2.Text

rs.Fields(12).Value = TextBox1.Text

rs.Fields(13).Value = TextBox1.Text

rs.Fields(14).Value = ComboBox4.Text

rs.Update

CommandButton1.Enabled = True

CommandButton2.Enabled = True

CommandButton3.Enabled = True

CommandButton4.Enabled = True

CommandButton5.Enabled = True

CommandButton10.Enabled = True

CommandButton17.Enabled = True

MsgBox "Ãîòîâî ²íôîðìàö³ÿ âíåñåíà â áàçó äàíèõ"

Else: MsgBox "Ïîìèëêà!!! Òèï ââåäåíèõ äàíèíèõ íå ïðàâèëüíèé"

End If

End Sub

Private Sub FillRecord1()

If (TextBox9.Text = Empty) Or (TextBox10.Text = Empty) Or (TextBox11.Text = Empty) Or (TextBox12.Text = Empty) Or (TextBox13.Text = Empty) Or (TextBox16.Text = Empty) Then

MsgBox "Ïîìèëêà!!! Âñ³ ïîëÿ ìàþòü áóòè çàïîâíåí³!"

Else

If (IsNumeric(TextBox9.Text) = False) And (IsNumeric(TextBox10.Text) = True) And (IsDate(TextBox12.Text) = True) And (IsNumeric(TextBox11.Text) = True) And (IsNumeric(TextBox16.Text) = False) Then

rs1.Fields(0).Value = TextBox9.Text

rs1.Fields(3).Value = TextBox10.Text

rs1.Fields(4).Value = TextBox11.Text

rs1.Fields(5).Value = TextBox16.Text

rs1.Fields(6).Value = TextBox12.Text

rs1.Fields(8).Value = TextBox13.Text

rs1.Fields(14).Value = ComboBox2.Text

rs1.Fields(18).Value = ComboBox1.Text

rs1.Update

MsgBox "Ãîòîâî ²íôîðìàö³ÿ âíåñåíà â áàçó äàíèõ"

CommandButton6.Enabled = True

CommandButton7.Enabled = True

CommandButton8.Enabled = True

CommandButton9.Enabled = True

CommandButton12.Enabled = True

CommandButton17.Enabled = True

Else

MsgBox "Ïîìèëêà!!! Òèï ââåäåíèõ äàíèõ íå ïðàâèëüíèé"

End If

End Sub

UserForm3

Dim cn As ADODB.Connection

Dim rs As New ADODB.Recordset

Dim rs1 As New ADODB.Recordset

Dim rs2 As New ADODB.Recordset

Dim str$, str1$, str2$, str3$, docstr$

Dim rs3 As New ADODB.Recordset

Dim rs4 As New ADODB.Recordset

Dim WordApp As Word.Application ' ýêçåìïëÿð ïðèëîæåíèÿ

Dim DocWord As Word.Document ' ýêçåìïëÿð äîêóìåíòà

Private Sub ComboBox1_Change()

rs.CursorType = adOpenKeyset

rs1.CursorType = adOpenKeyset

rs1.LockType = adLockOptimistic

rs.LockType = adLockOptimistic

str1 = ComboBox1.Text

'MsgBox str1

If (str1 = Empty) Then

MsgBox "Ïîìèëêà! Âèáåð³òü ïîòð³áíèé â³ää³ë"

Else

str = "SELECT Ïðàö³âíèê.prizv, Ïðàö³âíèê.imia, Â³ää³ë.zav_vid, Â³ää³ë.nom_tel_vid FROM Â³ää³ë INNER JOIN Ïðàö³âíèê ON Â³ää³ë.viddil = Ïðàö³âíèê.viddil WHERE Ïðàö³âíèê.viddil = '" + str1 + " ' "

str2 = "SELECT inv_nom, naz_obl FROM Îáëàäíàííÿ WHERE viddil = '" + str1 + "'"

rs.Source = str

rs1.Source = str2

Set rs.ActiveConnection = cn

Set rs1.ActiveConnection = cn

rs.Open

rs1.Open

rs.MoveFirst

TextBox1.Text = rs.Fields(2)

TextBox2.Text = rs.Fields(3)

ListBox1.Clear

Do While Not rs.EOF

ListBox1.AddItem (rs.Fields(0))

docstr = docstr + rs.Fields(0) + vbCrLf

'MsgBox docstr

rs.MoveNext

Loop

If rs1.EOF = True Then

MsgBox "Óâàãà!!! ²íôîðìàö³ÿ ïðî îáëàäíàííÿ â³äñóòíÿ!"

Else

rs1.MoveFirst

ListBox2.Clear

ListBox3.Clear

Do While Not rs1.EOF

ListBox2.AddItem (rs1.Fields(1))

ListBox3.AddItem (rs1.Fields(0))

rs1.MoveNext

Loop

End If

rs.Close

rs1.Close

End If

End Sub

Private Sub ComboBox2_Change()

Dim st$

st = ComboBox2.Text

rs3.CursorType = adOpenKeyset

rs3.LockType = adLockOptimistic

rs3.Source = "SELECT Ïðàö³âíèê.viddil, Ïðàö³âíèê.nom_pas, Ïðàö³âíèê.ser_pas, Ïðàö³âíèê.nom_tel1, Ïðàö³âíèê.mis_proj, Ïðàö³âíèê.posada, Ïðàö³âíèê.data_nar FROM Ïðàö³âíèê WHERE prizv = '" + st + "'"

Set rs3.ActiveConnection = cn

rs3.Open

TextBox3.Text = rs3.Fields(0)

TextBox4.Text = rs3.Fields(2) + " " + CStr(rs3.Fields(1))

TextBox5.Text = rs3.Fields(3)

TextBox6.Text = rs3.Fields(4)

TextBox7.Text = rs3.Fields(5)

TextBox8.Text = rs3.Fields(6)

rs3.Close

End Sub

Private Sub ComboBox3_Change()

Dim st3$, st4$

rs4.CursorType = adOpenKeyset

rs4.LockType = adLockOptimistic

st3 = ComboBox3.Text

st4 = "SELECT Îðåíäàòîð.dogov, Îðåíäàòîð.torg_pat, Ì³ñöå.misce, Ì³ñöå.sector, Ì³ñöå.bydova, Ì³ñöå.tovar, Ì³ñöå.electr, Ì³ñöå.voda, Ñêëàä.sklad, Ñêëàä.komir FROM (Îðåíäàòîð INNER JOIN Ñêëàä ON Îðåíäàòîð.orendat = Ñêëàä.orendat) INNER JOIN Ì³ñöå ON Îðåíäàòîð.orendat = Ì³ñöå.orendat WHERE Îðåíäàòîð.orendat = '" + st3 + "'"

rs4.Source = st4

Set rs4.ActiveConnection = cn

rs4.Open

'rs4.MoveFirst

TextBox9.Text = rs4.Fields(0)

TextBox10.Text = rs4.Fields(1)

TextBox11.Text = rs4.Fields(2)

TextBox12.Text = rs4.Fields(3)

TextBox13.Text = rs4.Fields(4)

TextBox14.Text = rs4.Fields(5)

TextBox15.Text = rs4.Fields(8)

TextBox16.Text = rs4.Fields(9)

If rs4.Fields(6) = True Then

Label25.Caption = "Åëåêòðèô³êîâàíå - ÒÀÊ "

Else: Label25.Caption = "Åëåêòðèô³êîâàíå - Í² "

End If

If rs4.Fields(7) = True Then

Label26.Caption = "Âîäîïîñòà÷àííÿ - ÒÀÊ "

Else: Label26.Caption = "Âîäîïîñòà÷àííÿ - Í² "

End If

rs4.Close

End Sub

Private Sub CommandButton1_Click()

Dim str1$, str2$, i%

If ComboBox1.Text = "" Then

MsgBox "Äëÿ çì³ñòó ïîðò³áíî âèáðàòè â³ää³ë"

Else

str1 = "²íôîðìàö³ÿ ïðî â³ää³ë '" + ComboBox1.Text + "'" + vbCrLf + "Çàâ³äóþ÷èé - " + TextBox1.Text + vbCrLf + "Ïðàö³âíèêè â³ää³ëó - " + vbCrLf

str1 = str1 + docstr

Set WordApp = New Word.Application

WordApp.Visible = True

Set DocWord = WordApp.Documents.Add

DocWord.Activate

DocWord.Paragraphs(1).Alignment = wdAlignParagraphLeft

DocWord.Range(Start:=0, End:=0).InsertAfter str1

End If

End Sub

Private Sub CommandButton2_Click()

rs.CursorType = adOpenKeyset

rs1.CursorType = adOpenKeyset

rs1.LockType = adLockOptimistic

rs.LockType = adLockOptimistic

str1 = ComboBox1.Text

'MsgBox str1

If (str1 = Empty) Then

MsgBox "Ïîìèëêà! Âèáåð³òü ïîòð³áíèé â³ää³ë"

Else

str2 = "SELECT inv_nom, naz_obl FROM Îáëàäíàííÿ WHERE viddil = '" + str1 + "'"

rs.Source = str

rs1.Source = str2

Set rs.ActiveConnection = cn

Set rs1.ActiveConnection = cn

rs.Open

rs1.Open

rs.MoveFirst

TextBox1.Text = rs.Fields(2)

TextBox2.Text = rs.Fields(3)

ListBox1.Clear

Do While Not rs.EOF

ListBox1.AddItem (rs.Fields(0) + " " + rs.Fields(1))

rs.MoveNext

Loop

If rs1.EOF = True Then

MsgBox "Óâàãà!!! ²íôîðìàö³ÿ ïðî îáëàäíàííÿ â³äñóòíÿ!"

Else

rs1.MoveFirst

ListBox2.Clear

ListBox3.Clear

Do While Not rs1.EOF

ListBox2.AddItem (rs1.Fields(1))

ListBox3.AddItem (rs1.Fields(0))

rs1.MoveNext

Loop

End If

rs.Close

rs1.Close

End If

End Sub

Private Sub CommandButton3_Click()

Dim st$

st = ComboBox2.Text

rs3.CursorType = adOpenKeyset

rs3.LockType = adLockOptimistic

Set rs3.ActiveConnection = cn

rs3.Open

TextBox3.Text = rs3.Fields(0)

TextBox4.Text = rs3.Fields(2) + " " + CStr(rs3.Fields(1))

TextBox5.Text = rs3.Fields(3)

TextBox6.Text = rs3.Fields(4)

TextBox7.Text = rs3.Fields(5)

TextBox8.Text = rs3.Fields(6)

rs3.Close

End Sub

Private Sub CommandButton4_Click()

Dim str1$

If ComboBox2.Text = "" Then

MsgBox "Äëÿ ôîðìóâàííÿ çâ³òó ïîòð³áíî âèáðàòè ïðàö³âíèêà"

Else

str1 = Label8.Caption + vbCrLf + Label9.Caption + ComboBox2.Text + vbCrLf + Label10.Caption + TextBox3.Text + vbCrLf + Label11.Caption + TextBox4.Text + vbCrLf + Label15.Caption + TextBox5.Text + vbCrLf + Label12.Caption + TextBox6.Text + vbCrLf + Label13.Caption + TextBox7.Text + vbCrLf + Label16.Caption + TextBox8.Text + vbCrLf

Set WordApp = New Word.Application

WordApp.Visible = True

Set DocWord = WordApp.Documents.Add

DocWord.Activate

DocWord.Paragraphs(1).Alignment = wdAlignParagraphLeft

DocWord.Range(Start:=0, End:=0).InsertAfter str1

End If

End Sub

Private Sub CommandButton5_Click()

Dim str1$

If ComboBox3.Text = "" Then

MsgBox "Äëÿ ôîðìóâàííÿ çâ³òó ïîòð³áíî âèáðàòè îðåíäàòîðà"

Else

str1 = "²íôîðìàö³ÿ ïðî îðåíäàòîðà"

str1 = str1 + ComboBox3.Text + vbCrLf + Label8.Caption + TextBox9.Text + vbCrLf + Label19.Caption + TextBox10.Text + vbCrLf + Label20.Caption + TextBox11.Text + vbCrLf + Label24.Caption + TextBox14.Text + vbCrLf + Label21.Caption + TextBox12.Text + vbCrLf + Label22.Caption + TextBox13.Text + vbCrLf + Label24.Caption + TextBox14.Text + vbCrLf + Label25.Caption + vbCrLf + Label26.Caption + vbCrLf + Label27.Caption + TextBox15.Text + vbCrLf + Label28.Caption + TextBox16.Text + vbCrLf

Set WordApp = New Word.Application

WordApp.Visible = True

Set DocWord = WordApp.Documents.Add

DocWord.Activate

DocWord.Range(Start:=0, End:=0).InsertAfter str1

End If

End Sub

Private Sub CommandButton6_Click()

Me.Hide

UserForm1.Show

End Sub

Private Sub UserForm_Activate()

ComboBox1.AddItem ("Áàçàð")

ComboBox1.AddItem ("Îõîðîíà")

ComboBox1.AddItem ("Îô³ñ")

ComboBox1.AddItem ("Ëàáîðàòîð³ÿ")

ComboBox1.AddItem ("Ñêëàä")

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"

cn.ConnectionString = "D:\Central.mdb"

cn.Open

ComboBox2.Clear

rs2.CursorType = adOpenKeyset

rs2.LockType = adLockOptimistic

rs2.Source = "SELECT * FROM Ïðàö³âíèê"

Set rs2.ActiveConnection = cn

rs2.Open

rs2.MoveFirst

Do While Not rs2.EOF

ComboBox2.AddItem (rs2.Fields(0))

rs2.MoveNext

Loop

rs2.Close

rs2.CursorType = adOpenKeyset

rs2.LockType = adLockOptimistic

rs2.Source = "SELECT * FROM Îðåíäàòîð"

Set rs2.ActiveConnection = cn

rs2.Open

ComboBox3.Clear

Do While Not rs2.EOF

ComboBox3.AddItem (rs2.Fields(0))

rs2.MoveNext

Loop

rs2.Close

End Sub

Private Sub UserForm_Deactivate()

Me.Hide

MsgBox "Ôîðìà äåàêòèâîâàíà"

UserForm1.Show

End Sub

UserForm4

Dim cn As ADODB.Connection

Dim rs As New ADODB.Recordset

Dim WordApp As Word.Application

Dim WordApp1 As Word.Application

Dim DocWord As Word.Document

Dim DocWord1 As Word.Document

Dim Path$

Dim k%

Private Sub CommandButton1_Click()

k = 1

Set WordApp = New Word.Application

WordApp.Visible = True

WordApp.Caption = "Íàðàõóâàííÿ çàðïëàòè"

Set DocWord = WordApp.Documents.Add

DocWord.Activate

DocWord.Paragraphs(1).Alignment = wdAlignParagraphRight

DocWord.Range(0, 0).InsertAfter "Íàðàõóâàííÿ çàðïëàòè" + vbCrLf + vbCrLf + vbCrLf + ""

DocWord.Range(0, 20).Bold = True ' âèä³ëåíèé ôðàãìåíò ðîáèòü æèðíèì - íå ðîáèòü

'DocWord.Paragraphs.Last.Range.InsertAfter "Êîíåö."

rs.CursorType = adOpenKeyset

rs.LockType = adLockOptimistic

rs.Source = "SELECT Ïðàö³âíèê.prizv, Ïðàö³âíèê.viddil, Ïðàö³âíèê.stavka, Ïðàö³âíèê.staj FROM Ïðàö³âíèê"

Set rs.ActiveConnection = cn

rs.Open

rs.MoveFirst

Do While Not rs.EOF

k = k + 1

rs.MoveNext

Loop

rs.MoveFirst

Dim tbl As Table

Dim field$

Set Range1 = DocWord.Range(22, 22)

Set Table = DocWord.Tables.Add(Range1, k, 3)

Set tbl = DocWord.Tables(1)

tbl.Rows(1).HeadingFormat = True 'ñòâîðþº çàãîëîâîê òàáëèö³ - íå ðîáèòü

DocWord.Tables(1).Borders.Enable = True 'ñòâîðþº ðîçì³òêó òàáëèö³

DocWord.Tables(1).AutoFormat (wdTableFormatList1)

DocWord.Tables(1).Cell(1, 1).Range = "Ï.².Ï"

DocWord.Tables(1).Cell(1, 2).Range = "Â³ää³ë"

DocWord.Tables(1).Cell(1, 3).Range = "Ñóìà ãðí."

Dim i%, j%

j = 1

For i = 2 To tbl.Rows.Count - 1

field = rs.Fields(0).Value

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

j = 2

For i = 2 To tbl.Rows.Count - 1

field = rs.Fields(1).Value

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

j = 3

For i = 2 To tbl.Rows.Count - 1

field = CStr(CInt(rs.Fields(2).Value) * CInt(rs.Fields(3).Value))

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

DocWord.Tables(1).Cell(i + 1, j).AutoSum

DocWord.Tables(1).Cell(i + 1, 1).Range = "ÂÑÜÎÃÎ ÃÐÍ."

DocWord.Tables(1).Columns(1).Width = 250

DocWord.Tables(1).Columns(2).Width = 100

DocWord.Tables(1).Columns(3).Width = 80

rs.Close

End Sub

Private Sub CommandButton2_Click()

k = 1

Set WordApp = New Word.Application

WordApp.Visible = True

WordApp.Caption = "Íàðàõóâàííÿ ïëàòè äëÿ îðåíäàòîð³â"

Set DocWord = WordApp.Documents.Add

DocWord.Activate

DocWord.Paragraphs(1).Alignment = wdAlignParagraphRight

DocWord.Range(0, 0).InsertAfter "Íàðàõóâàííÿ ïëàòè äëÿ îðåíäàòîð³â" + vbCrLf + vbCrLf + vbCrLf + ""

DocWord.Range(0, 37).Bold = True ' âèä³ëåíèé ôðàãìåíò ðîáèòü æèðíèì - íå ðîáèòü

'DocWord.Paragraphs.Last.Range.InsertAfter "Êîíåö."

rs.CursorType = adOpenKeyset

rs.LockType = adLockOptimistic

rs.Source = "SELECT Îðåíäàòîð.orendat, Îðåíäàòîð.nom_pas, Îðåíäàòîð.dogov, Ì³ñöå.plata, Ì³ñöå.electr, Ì³ñöå.voda, Ñêëàä.plata, [Îðåíäîâàíå îáëàäíàííÿ].plata FROM ((Îðåíäàòîð INNER JOIN [Îðåíäîâàíå îáëàäíàííÿ] ON Îðåíäàòîð.orendat = [Îðåíäîâàíå îáëàäíàííÿ].orendat) INNER JOIN Ñêëàä ON Îðåíäàòîð.orendat = Ñêëàä.orendat) INNER JOIN Ì³ñöå ON Îðåíäàòîð.orendat = Ì³ñöå.orendat"

Set rs.ActiveConnection = cn

rs.Open

rs.MoveFirst

Do While Not rs.EOF

k = k + 1

rs.MoveNext

Loop

rs.MoveFirst

Dim tbl As Table

Dim field$

Set Range1 = DocWord.Range(36, 36)

Set Table = DocWord.Tables.Add(Range1, k, 4)

Set tbl = DocWord.Tables(1)

tbl.Rows(1).HeadingFormat = True 'ñòâîðþº çàãîëîâîê òàáëèö³ - íå ðîáèòü

DocWord.Tables(1).Borders.Enable = True 'ñòâîðþº ðîçì³òêó òàáëèö³

DocWord.Tables(1).AutoFormat (wdTableFormatList1)

DocWord.Tables(1).Cell(1, 1).Range = "Ï.².Ï"

DocWord.Tables(1).Cell(1, 2).Range = "Íîìåð ïàñïîðòà"

DocWord.Tables(1).Cell(1, 3).Range = "Òîðãîâèé ïàòåíò"

DocWord.Tables(1).Cell(1, 4).Range = "Íàðàõîâàíî"

Dim i%, j%

j = 1

For i = 2 To tbl.Rows.Count - 1

field = rs.Fields(0).Value

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

j = 2

For i = 2 To tbl.Rows.Count - 1

field = rs.Fields(1).Value

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

j = 3

For i = 2 To tbl.Rows.Count - 1

field = rs.Fields(2).Value

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

j = 4

For i = 2 To tbl.Rows.Count - 1

field = CStr(CInt(rs.Fields(3).Value) + CInt(rs.Fields(6).Value) + CInt(rs.Fields(6).Value))

If rs.Fields(4) = True Then

field = CStr(CInt(field) + 50)

End If

If rs.Fields(5) = True Then

field = CStr(CInt(field) + 85)

End If

rs.MoveNext

tbl.Cell(i, j).Range.Text = field

Next i

rs.MoveFirst

DocWord.Tables(1).Cell(i + 1, j).AutoSum

DocWord.Tables(1).Cell(i + 1, 1).Range = "ÂÑÜÎÃÎ ÃÐÍ."

DocWord.Tables(1).Columns(1).Width = 150

DocWord.Tables(1).Columns(2).Width = 90

DocWord.Tables(1).Columns(3).Width = 90

DocWord.Tables(1).Columns(4).Width = 90

rs.Close

End Sub

Private Sub CommandButton4_Click()

Dim str$, str1$

str = ListBox1.Text

If ListBox1.Text = "" Then

MsgBox "Äëÿ ñòâîðåííÿ äîãîâîðó ïîòð³áíî âèáðàòè îðåíäàòîðà"

Else

Set WordApp1 = New Word.Application

WordApp1.Visible = True

'Set DocWord1 = WordApp1.Documents.Add

Path = FileSystem.CurDir()

'Set DocWord1 = WordApp1.Documents.Open("D:\Äîãîâ³ð.docm")

Set DocWord1 = WordApp1.Documents.Open(Path + "\Äîãîâ³ð.docm")

rs.CursorType = adOpenKeyset

rs.LockType = adLockOptimistic

rs.Source = "SELECT Îðåíäàòîð.orendat, Îðåíäàòîð.dogov, Ì³ñöå.tovar, Ì³ñöå.misce, Ì³ñöå.sector, Ñêëàä.komir, Ñêëàä.sklad FROM (Îðåíäàòîð INNER JOIN Ñêëàä ON Îðåíäàòîð.orendat = Ñêëàä.orendat) INNER JOIN Ì³ñöå ON Îðåíäàòîð.orendat = Ì³ñöå.orendat WHERE Îðåíäàòîð.orendat = '" + str + "'"

Set rs.ActiveConnection = cn

rs.Open

rs.MoveFirst

'str1 = rs.Fields(1)

'MsgBox str1

'DocWord1.Unprotect

DocWord1.FormFields("ÒåêñòîâåÏîëå11").Result = rs.Fields(1)

DocWord1.FormFields("ÒåêñòîâåÏîëå1").Result = rs.Fields(0)

DocWord1.FormFields("ÒåêñòîâåÏîëå5").Result = rs.Fields(2)

DocWord1.FormFields("ÒåêñòîâåÏîëå6").Result = rs.Fields(3)

DocWord1.FormFields("ÒåêñòîâåÏîëå7").Result = rs.Fields(4)

DocWord1.FormFields("ÒåêñòîâåÏîëå8").Result = rs.Fields(5)