Relacyjne Bazy Danych
wykład X
Język aplikacji baz danych – Visual Basic
Zostaną wprowadzone podstawowe pojęcia
takie jak procedura, moduł, moduł klasy,
zdarzenie i obsługa błędów.
Procedury
Procedury są podstawą programowania aplikacji bazodanowych MS Access w ramach paradygmatu programowania
zdarzeniowego. Oto ich cechy:
•Są często wiązane ze zdarzeniami interfejsu użytkownika jak:
•naciśnięcie przycisku na formularzu,
•zmiana wartości w polu tekstowym formularza,
•otwarcie lub zamknięcie formularza,
•drukowanie raportu,
•początkowe otwarcie bazy danych, itd.
•Każda powtarzająca się rutynowa czynność, w szczególności
każda, którą można wykonać za pomocą opcji znajdujących się
we wbudowanych menu, nadaje się do zautomatyzowania za
Na formularzu można umieścić zbiór przycisków, po których naciśnięciu użytkownik uzyskuje:
•wyświetlenie tekstu pomocy;
•wyświetlenie innego formularza;
•zamknięcie danego formularza;
•wydrukowanie zawartości formularza;
•wyświetlenie następnego (poprzedniego) rekordu, przejście do pierwszego, ostatniego lub nowego rekordu;
•wykonanie obliczeń i wyświetlenie wyniku w polu niezwiązanym;
•wykonanie kopii zabezpieczającej bazy danych na dyskietkę;
•przeniesienie danych do Worda, Excela lub innej bazy danych.
Visual Basic for Applications (w skrócie VBA) jest
językiem programowania dla aplikacji systemu Microsoft Office będący podzbiorem języka Visual Basic. Oto jego cechy:
•służy do powiązania obiektów bazy danych w jedną spójną aplikację;
•zawiera standardowe konstrukcje programistyczne jak If ...
Then ... Else, For, Case, procedury, zmienne;
•stosuje dwa typy procedur:
•funkcje (Function) - zwracające wartość; mogą być używane w wyrażeniach jak również jako wartości właściwości zdarzeń;
•podprogramy (Sub) - nie zwracające bezpośrednio
Moduł jest zbiorem deklaracji i definicji procedur języka VBA przechowywanych razem jako całość.
•Moduł może zawierać zarówno procedury zdarzeń jak i zwykłe nazwane procedury.
•Są dwa rodzaje modułów:
•moduły klas w tym:
• moduły klas obiektów MS Access jak formularze i raporty,
• moduły klas definiujące niezależne obiekty.
•moduły ogólne - nie związane z żadnym obiektem.
•Procedury typu Public mogą być wywoływane w dowolnym miejscu aplikacji (opcja domyślna).
•Procedury typu Private są prywatne dla danego modułu (w tym
modułu formularza i raportu) - nie można ich używać spoza
Typy zmiennych:
•lokalne dla procedury, deklarowane lokalnie w procedurze jako Dim,
•lokalne dla modułu, deklarowane jako Private w module (opcja domyślna),
•globalne, deklarowane jako Public w module - dostęp do
nich jest możliwy w całej aplikacji.
Typy danych dla zmiennych
Access Visual Basic Wartość domyślna
Text String ""
Number Integer, Single Long, Double, 0
Currency Currency 0
Yes/No Boolean False
Date/Time Date 30.12.1899
Specjalny typ danych Visual Basic - Variant - oznacza dowolny
typ wartości.
Domyślnie wszystkie argumenty przy wywoływaniu procedury są przekazywane metodą przez referencję.
Dzięki temu w kodzie procedury operujemy na rzeczywistej zmiennej, którą przekazujemy jako parametr.
Alternatywnie można przekazywać argument przy wywoływaniu procedury metodą przez wartość –
poprzedzając w deklaracji procedury nazwę parametru słowem kluczowym ByVal. Przy wywołaniu oblicza się
wartość argumentu i przekazuje do kodu procedury właśnie tę wartość. Jeśli argumentem jest zmienna, wartość tej
zmiennej nie ulega zmianie przy wykonywaniu kodu.
Przykład 1
Typowym zadaniem programistycznym jest obliczanie pierwiastka kwadratowego z liczby nieujemnej. Oto jego zapis w postaci funkcji Visual Basic.
Function SquareRoot (X As Double) As Double Dim Msg As String
Select Case Sgn(X) ' Oblicz znak argumentu.
Case 1 ' OK jeśli dodatni.
SquareRoot = Sqr(X) Exit Function
Case 0 ' Powiadom użytkownika jeśli 0.
Msg = "Przekazana wartość to 0."
Case -1 ' Powiadom użytkownika jeśli mniejsze niz 0.
Msg = "Niedozwolona liczba."
End Select
Przykład 2
Pokażemy na tym przykładzie deklaracje i definicje modułu.
Opcje:
•Option Compare Database - użycie metody z bazy danych w porównaniach;
•Option Explicit - wymagane jest deklarowanie zmiennych;
są zwykle domyślnie przyjmowane przez system.
Option Compare Database Option Explicit
---
Private licznik As Integer 'Zmienna lokalna Public pokaż As Integer 'Zmienna globalna ---
Sub Zeruj() 'Procedura globalna licznik = 0
pokaż = licznik End Sub
---
Sub Dodaj() 'Procedura globalna licznik = licznik + 1
pokaż = licznik
MsgBox licznik, , "LICZNIK"
W MS Access jest dostęny edytor kodu Visual Basic (Visual Basic Editor) – zawierający środowisko uruchamiania kodu VBA.
Okno analizy programu (Immediate Window) – uruchamiane przez sekwencję klawiszy CTRL-G lub z menu Widok "View -> Immediate Window" - daje możliwość wykonywania kodu (instrukcji, metod, funkcji i procedur) oraz sprawdzania
wartości wyrażeń, pól i właściwości – pisząc w pojedynczym wierszu np.
? Licznik
gdzie licznik jest zmienną, której wartość chcemy wypisać.
•Jest możliwość przerwania działania funkcji lub procedury przez ustawienie w kodzie punktu przerwania (breakpoint) – "Debug ->
Toggle Breakpoint" (F9).
• Można też wyświetlić - z menu Widok (View):
•nawigator po klasach bieżącego projektu (Project Explorer) oraz
•nawigator po klasach zarejestrowanych bibliotek jak Access, VBA (Object Browser).
Na ogół jednej klasie odpowiada dokładnie jeden obiekt tej klasy
(obiekt klasy – class object).
Przykład 3
Sprawdzanie, czy dany formularz jest otwarty w widoku Formularz - rozwiązanie używające standardowej funkcji SysCmd.
Function Otwarty(ByVal Mój As String) As Integer ' SysCmd - czy formularz jest otwarty
Otwarty = False
If SysCmd(acSysCmdGetObjectState,acForm,Mój) <> 0 Then
' CurrentView - czy jest otwarty w widoku Formularz
If Forms(Mój).CurrentView <> 0 Then Otwarty = True End If
End Function
Zwróćmy uwagę na to, że odróżniamy klasę danego formularza łącznie z arkuszem jego właściwości od konkretnych jego wcieleń widocznych na ekranie, na których możemy wykonywać operacje w kodzie VBA.
Wcielenie to nazywamy obiektem danego formularza lub w skrócie po prostu formularzem. Możemy więc powiedzieć, że otwarte formularze są obiektami klas swoich formularzy.
Do obiektu formularza o nazwie Pracownicy można się odwoływać w następujący sposób:
•Forms![Pracownicy]
•Forms("Pracownicy")
•Forms(numer) gdzie numer jest numerem
przyporządkowanym danemu formularzowi w sesji.
Używając dostępu kropkowego możemy odwoływać się do właściwości obiektu formularza a także do jego metod np.
•Forms![Pracownicy].Caption zwraca tytuł formularza,
•Forms![Pracownicy].SetFocus jest metodą, której wykonanie przenosi fokus do tego formularza.
Wykrzyknik ! oznacza wybór elementu z kolekcji. Np.
•Forms![Pracownicy] - wybór formularza z kolekcji wszystkich formularzy,
•Forms![Pracownicy]![Nazwisko] - wybór pola Nazwisko z
kolekcji wszystkich obiektów związanych z formularzem
Pracownicy.
PROCEDURA ZAMYKAJĄCA AUTOMATYCZNIE PRZY ZAMYKANIU DANEGO FORMULARZA DRUGI
OTWARTY FORMULARZ, np. formularz Książki Private Sub Form_Close()
If SysCmd(acSysCmdGetObjectState, acForm, "KSIAZKI") <> 0 Then
DoCmd.Close acForm, "KSIAZKI"
End If End Sub
sprawdza czy dany formularz
jest otwarty
Reasumując:
•wykrzyknik oznacza wybór elementu z kolekcji elementów,
•kropka oznacza wybór właściwości obiektu lub kolekcji.
Zapis Forms![Pracownicy]![Nazwisko] oznacza także domyślnie wartość pola tekstowego Nazwisko czyli
konkretne nazwisko – alternatywnie można używać pełnej notacji:
Forms![Pracownicy]![Nazwisko].Value
Przy wpisywaniu do pola tekstowego lub listowego,
bieżącą wartość – jeszcze nie zapisaną do bazy danych –
uzyskuje się przy użyciu:
Przy odczytywaniu wartości atrybutu Text fokus (bieżący kursor) musi znajdować się na danym polu:
[Nazwisko].SetFocus
MsgBox "Nazwisko = "&[Nazwisko].Text
Sprawdzenie czy formularz jest otwarty (w widoku Projekt lub Formularz) można zrealizować jeszcze w inny
sposób – przeglądając kolekcję wszystkich otwartych formularzy i sprawdzając ich nazwy.
Function Otwarty1 (ByVal Mój As String) As Integer ' Zwraca True, jeśli podany formularz jest otwarty w widoku projekt lub formularz.
Dim I As Integer Otwarty1 = False
' Forms.Count to liczba obiektów w kolekcji Forms For I = 0 To Forms.Count - 1
If Forms(I).Name = Mój Then Otwarty1 = True
Exit For
Jest możliwość tworzenia instancji klasy formularza w kodzie VBA:
Dim Kopia As New Form_Osoby
i wykonywanie na niej operacji jak na obiekcie. Instancja
ta nie jest widoczna na ekranie.
Przykład 4
Kolejna procedura specyfikuje reakcję na zdarzenie
naciśnięcia lewego przycisku myszy (obiekt: przycisk Witaj, zdarzenie: Przy kliknięciu).
Private Sub Witaj_Click()
[Pole] = "Witaj w klubie VBA"
End Sub
Przykład 5
Zwróćmy uwagę na procedurę, jaką tworzy kreator przycisków w celu zrealizowania zadania otwarcia formularza o nazwie Osoby:
Private Sub Przycisk_Click()
On Error GoTo Err_Przycisk_Click Dim stDocName As String
Dim stLinkCriteria As String stDocName = "Osoby"
DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Przycisk_Click:
Exit Sub
Err_Przycisk_Click:
MsgBox Err.Description
Resume Exit_Przycisk_Click
•Instrukcja DoCmd.OpenForm "Osoby" zawierająca wywołanie metody OpenForm wbudowanego obiektu o nazwie DoCmd, powoduje otwarcie formularza Osoby.
•W procedurze tej występuje obsługa błędów. Kreator przycisków tworząc procedurę zdarzenia Przy kliknięciu standardowo
realizuje następującą strategię:
•W przypadku wystąpienia błędu - to jest niemożliwości otwarcia formularza Osoby - przerwij obliczenia i przejdź do sekcji obsługi błędów przy etykiecie Err_Przycisk_Click.
•Wypisz informację o błędzie korzystając z metody Description specjalnego obiektu Err.
•Zakończ wykonywanie procedury.
W opisanej procedurze można byłoby standardowy tekst błędu Err.Description zastąpić własnym tekstem
skierowanym do użytkownika aplikacji:
MsgBox "Błąd aplikacji. Skontaktuj się z administratorem
aplikacji"
Korzystając z obsługi błędów napiszmy funkcję sprawdzającą czy formularz o nazwie będącej argumentem procedury jest otwarty (w widoku Projekt lub Formularz).
Function czy(ByVal formularz As String) As Boolean On Error GoTo Nie
Dim s As String
s = Forms(formularz).Name czy = True
Exit Function
Nie:
Synchronizacja dwóch formularzy Przykład 6
Otwarcie formularza może być bardziej skomplikowane, aby mogło wystarczyć użycie samego kreatora przycisków np.
procedura otwierająca formularz Uczestnictwo w projektach w
oparciu o wartość znajdującą się w polu Numer będącym częścią
podformularza przy czym sam przycisk Projekty znajduje się w
głównym formularzu Departament.
Procedura otwiera formularz Uczestnictwo w projektach w celu pokazania uczestnictwa w projektach wybranej aktualnie osoby (w podformularzu):
Private Sub Projekty_Click()
On Error GoTo Err_Projekty_Click Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Uczestnictwo w projektach"
stLinkCriteria = "Numer=Forms![Departament]![Osoba Subform].Form![Numer]"
DoCmd.OpenForm stDocName, , , stLinkCriteria Forms![Departament].SetFocus
Forms![Departament]![Osoba Subform].SetFocus Exit_Projekty_Click:
Exit Sub
Err_Projekty_Click:
Przykład 7
Formularz wyskakujący Uczestnictwo w projektach powinien pokazywać dane o aktualnie rozpatrywanym pracowniku z
podformularza w formularzu Departament. Uaktualnienie wartości następuje przy pojawieniu się nowego rekordu w
podformularzu czyli jako reakcja na zdarzenie Przy bieżącym.
Przy czym to uaktualnienie ma sens, tylko wtedy gdy użytkownik wcześniej otworzył formularz wyskakujący Uczestnictwo w
projektach naciskając przycisk Projekty. Z kolei naciśnięcie
przycisku Projekty ma sens tylko wtedy, gdy pole Numer w
podformularzu jest niepuste.
W procedurze występują dwie nowe właściwości, których wartościami są obiekty:
•Me – formularz lub raport, którego procedura jest wykonywana;
•Parent – formularz lub raport nadrzędny względem
danego formularza lub raportu.
Private Sub Form_Current()
On Error GoTo Err_Form_Current If IsNull(Me![Numer]) Then
Me.Parent![Projekty].Enabled = False Else
Me.Parent![Projekty].Enabled = True
If Otwarty("Uczestnictwo w projektach") Then Dim Projekt As String
Projekt = "[Numer]="& Me![Numer]
DoCmd.OpenForm "Uczestnictwo w projektach", , , Projekt Forms![Departament].SetFocus
Forms![Departament]![Osoba Subform].SetFocus End If
End If
Exit_Form_Current:
Exit Sub
Err_Form_Current:
MsgBox Err.Description
Uaktualnienie formularza wyskakującego jest konieczne nie tylko, gdy fokus w podformularzu przejdzie do nowego
rekordu, ale również gdy w bieżącym rekordzie zmieni się wartość pola Numer.
Realizuje to procedura zdarzenia Po aktualizacji dla pola
Numer - jej kod jest analogiczny do procedury zdarzenia Przy
bieżącym.
Utwórzmy dwie tabele Instytucje i Pracownicy połączone związkiem jeden do wiele.
Każda instytucja zatrudnia wielu pracowników; każdy pracownik jest zatrudniony w jednej instytucji. Utwórzmy dwa formularze: „Instytucja”, na którym są wyświetlane dane o instytucjach, oraz „Pracownicy” na którym są wyświetlane dane o pracownikach. Chcemy, aby na życzenie użytkownika poprzez naciśnięcie przycisku
„Pracownicy instytucji” na formularzu „Instytucja” otwierał się formularz wyświetlający pracowników danej instytucji.
Chcemy aby przy chodzeniu po rekordach formularza
„Instytucja” aktualizowały się dane o pracownikach
zatrudnionych w danej instytucji – oczywiście o ile
Private Sub PracownicyInstytucji_Click() On Error GoTo Form_Current_Err
Dim Ins As String
Ins = "Id_inst=Forms![Instytucje]!Id"
DoCmd.OpenForm "Pracownicy",,,Ins Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Err.Description
Resume Form_Current_Exit
End Sub
Sub Form_Current()
On Error GoTo Form_Current_Exit Dim s As String
s = Forms("Pracownicy").Name On Error GoTo Form_Current_Err Dim Ins As String
Ins = "Id_inst=Forms![Instytucje]!Id"
DoCmd.OpenForm "Pracownicy",,,Ins Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
Czy rozwiązanie umożliwia wprowadzanie pracowników do otwieranego z formularza Instytucje formularza Pracownicy?
Private Sub Form_BeforeInsert(Cancel As Integer) On Error GoTo Form_Current_Exit
Dim num As Integer
num = Forms("Instytucje").id
On Error GoTo Form_Current_Err Id_inst = num
Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Err.Description
Formularz – podformularz (wyskakujący)
formularz główny
podformularz
ma dane związane
z formularzem głównym
PROCEDURY UMOŻLIWIAJĄCE KOPIOWANIE KLUCZA GŁÓWNEGO DO PODFORMULARZA WYSKAKUJĄCEGO PRZY DOPISYWANIU DANYCH Option Compare Database
Option Explicit
Public klas As String
___________________________________________
Private Sub uczniow_Click()
On Error GoTo Err_uczniow_Click Dim stDocName As String
Dim stLinkCriteria As String stDocName = "uczniowie_pdf"
klas = Me![id_klasy]
stLinkCriteria = "[id_klasa]=" & Me![id_klasy]
DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_uczniow_Click:
Exit Sub
Err_uczniow_Click:
zmienna globalna
przypisanie wartości zmiennej globalnej
formularz główny
Private Sub nowy_rec_Click()
On Error GoTo Err_nowy_rec_Click
DoCmd.GoToRecord , , acNewRec [id_klasa] = Form_klasa_gl.klas Exit_nowy_rec_Click:
Exit Sub
Err_nowy_rec_Click:
MsgBox Err.Description
Resume Exit_nowy_rec_Click
End Sub
wczytanie wartości ze zmiennej globalnej podformularz