Kontynuujemy naukę programowania aplikacji bazodanowych.
Rozważane są obiekty reprezentujące w kodzie VBA: polecenie – DoCmd, błąd
– Err, aplikację – Application, uruchamianie – Debug.
Omawiane są takie techniki programistyczne jak obsługa błędów, aktualizacja listy pola
kombo, anulowanie zachodzących zdarzeń, wykonywanie instrukcji SQL,
programowanie wyboru wielokrotnego z listy.
Przegląd ważniejszych poleceń języka VBA
Są to metody predefiniowanych obiektów:
jak i samych obiektów interfejsu użytkownika. Oto przykłady poleceń języka VBA pogrupowane względem operacji na formularzu.
1. Chodzenie po rekordach formularza w tym wyświetlenie pustego rekordu do wprowadzeniadanych, np. przejście do pierwszego rekordu formularza realizuje polecenie:
DoCmd.GoToRecord , , acFirst
(w miejsce acFirst można użyć innych predefiniowanych stałych: acPrevious, acNext, acLast, acNewRec realizując odpowiednią akcję).
2. Otwieranie formularza realizuje polecenie:
DoCmd.OpenForm nazwa_formularza[,widok][,nazwa_filtru][,warunek_WHERE]
[,tryb_danych][,tryb_okna]
Domyślnie gdy nie zamieści się
argumentu są przyjmowane następujące wartości: widok = acNomal
,
oznacza, że formularz zostaje otwarty w widoku formularz, tryb_danych=acFormPropertySettings
,
przyjmowane są ustawienia z arkusza właściwości formularza, tryb_okna=acWindowNormal
oznacza zwykłe okno.
Na przykład:
DoCmd.OpenForm "Wprowadz_wyswietl
firmy", , , , acFormEdit
(w miejsce acFormEdit można użyć innych predefiniowanych stałych: acFormAdd, acFormPropertySettings, acFormReadOnly). Operacja otwierania raportu jest analogiczna z tym, że ma mniej parametrów:
DoCmd.OpenReport nazwa-raportu[,widok][,nazwa-filtru][,warunek-WHERE]
3. Zamykanie obiektów
DoCmd.Close
DoCmd.Close acForm, "Firmy"
4. Drukowanie bieżącego formularza (również raportu, arkusza danych, stron dostępu do danych, modułu - jeśli jest na nich fokus)
DoCmd.PrintOut
5. Wykonanie kwerendy
DoCmd.OpenQuery nazwa-kwerendy, acNormal, acEdit
lub
DoCmd.RunSQL instrukcja-SQL
6. Ponowienie kwerendy (aktualizacja) dla pola listowego lub formularza.
Dla pola listowego:
DoCmd.Requery
"Id firmy"
– tylko dla pola w aktualnym formularzu lub Me![Id firmy].Requery
lub Forms![Kontrahenci]![Id
firmy].Requery
i wówczas można używać także dla pól na innych
formularzach.Dla formularza:
DoCmd.Requery
lubMe.Requery
7. Zakończenie aplikacji:
DoCmd.Quit
lubApplication.Quit
8. Filtrowanie zawartości formularza
np. aktualizacja formularza wyświetlającego książki po zmianie niezwiązanego pola tekstowego
[Data od]
:
DoCmd.ApplyFilter
, "[Data ukazania]>=Forms![Wyszukaj według wymagań]![Data od]"
9. Wyświetlenie wszystkich rekordów:
DoCmd.ShowAllRecords
Operacje 8 i 9 można wykonać korzystając ze sposobu reprezentowania filtrów przez Access. Mianowicie, filtrowanie rekordów odbywa się za pomocą dwóch właściwości formularza:
A więc istniejący filtr można włączyć za pomocą instrukcji
Me.FilterOn = True
a wyłączyć za pomocą:
Me.FilterOn = False
(równoważne operacji 9)Wartość właściwości Filter zostaje
zachowana po zamknięciu formularza (w jego arkuszu właściwości). Przy powtórnym
otwarciu jest ona dostępna, przy czym na początku filtr jest wyłączony
Me.FilterOn = False
, a więc aby zastosować istniejący filtr trzeba wykonać:
Me.FilterOn = True
Teraz zadanie dla czytelnika.
Zbuduj formularz oparty na pojedynczej tabeli ze zbiorem przycisków umożliwiających: chodzenie po wierszach tabeli, przechodzenie do wstawienia nowego wiersza, usuwanie aktualnego wiersza, drukowanie zawartości tabeli, zamykanie formularza, ustawianie filtru przy pomocy właściwości Me.Filter, włączanie i wyłączanie ustawionego filtru przy pomocy właściwości Me.FilterOn, wyświetlanie tekstu pomocy. |
Specjalną grupę stanowią polecenia realizujące wykonanie akcji z wbudowanych menu - z zastosowaniem metody RunCommand obiektu DoCmd (obiekt DoCmd można pominąć).
[DoCmd.]RunCommand polecenie
gdzie polecenie jest stałą reprezentującą odpowiednie polecenie z wbudowanych menu - pełna lista znajduje się w pomocy Help MS Access pod tematem "RunCommand Method Constants" np.
DoCmd.RunCommand acCmdOptions
otwiera okienko dialogowe "Opcje" ("Options") z menu "Narzędzia" ("Tools").
Teraz zadanie dla czytelnika.
Wykonaj to samo zadanie co uprzednio z tym, że operacje na rekordach zaprogramuj używając metody RunCommand i odpowiednich operacji z menu rozwijanych. |
Print jest metodą predefiniowanego obiektu Debug - wypisuje wartości kontrolne w okienku analizy programu (Immediate Window). Np.
Debug.Print
"Przechodzę
do obliczania współczynnika zysku"
Tak jak instrukcja Debug.Print
służy do
wypisywania informacji (z myślą o osobie tworzącej aplikację) przy
wykrywaniu błędów w aplikacji, tak instrukcje MsgBox i InputBox służą do
bezpośredniego kontaktu z użytkownikiem w trakcie wykonywania aplikacji
poprzez wyświetlanie odpowiednich informacji w okienkach dialogowych oraz
poprzez przyjmowanie danych od użytkownika.
Na przykład, wykonanie instrukcji
MsgBox "Formularz zostaje zamknięty!",,"UWAGA"
powoduje wyświetlenie następującego okienka dialogowego:
A wykonanie instrukcji
x = MsgBox("Czy zapisać na dysku?",vbYesNo ,"UWAGA")
powoduje wyświetlenie następującego okienka dialogowego:
Bezpośredni kontakt z użytkownikiem jest potrzebny na przykład, gdy trzeba poinformować użytkownika o wystąpieniu błędu. Załóżmy, że naciśnięcie przycisku powoduje przejście do poprzedniego rekordu. Gdy użytkownik naciśnie przycisk w sytuacji, gdy aktualnie w formularzu jest wyświetlany pierwszy rekord, instrukcja
DoCmd.GoToRecord , , acPrevious
nie może być wykonana i powstaje błąd. W poniższej procedurze ten błąd zostaje obsłużony, poprzez skok do etykiety Error_handler - co realizuje instrukcja On Error GoTo. Następuje wypisanie numeru błędu i tekstu opisu błędu, po czym instrukcja
Resume Next
kieruje sterowanie do następnej instrukcji po instrukcji powodującej błąd.
Private Sub Polecenie_Click () |
Informację o błędzie zaistniałym w czasie wykonywania kodu VBA reprezentuje obiekt Err. W szczególności jego atrybut Err.Description podaje opis ostatniego błędu a atrybut Err.Number numer tego błędu.
Do obsługi błędów używamy instrukcji On Error. Specyfikuje ona co zrobić, gdy w trakcie wykonywania następnych instrukcji wystąpi błąd:
On Error GoTo etykieta -
przejdź
do obsługi błędu w instrukcji oznaczonej przez etykieta,
On Error Resume -
powtórz
instrukcję powodującą błąd,On Error Resume Next -
przejdź
do następnej instrukcji
po powodującej błąd,On Error Resume etykieta -
wznów obliczenia od etykieta
,On Error Stop -
zatrzymaj
obliczenia.Podobne znaczenie mają instrukcje służące do zakończenia obsługi błędów:
GoTo etykieta | Resume [ | Next | etykieta]|Stop
Poza tym są jeszcze dodatkowe konstrukcje związane z obsługą błędów:
Err.Raise numer_błędu
- spowoduje
powstanie błędu o podanym numerze i wymuszenie (w sposób sztuczny) jego obsługi;
Error(i)
- komunikat o błędzie dla błędu
o numerze i.
Wykonanie instrukcji
On Error GoTo 0
wyłącza obsługę błędów.
MsgBox może być również użyte jako funkcja za pomocą, której użytkownik przekazuje do aplikacji swój wybór np. wartość "Tak" lub "Nie".
x = "Czy chcesz wprowadzić nowe wymagania do oferty?" |
W przypadku, gdy nie można wykonać pewnej akcji np. zapisu do bazy danych, używając MsgBox jako polecenia, informujemy o tym użytkownika, wymuszając na nim przyjęcie tej wiadomości przez naciśnięcie przycisku OK w odpowiednim okienku dialogowym. Rodzaj odpowiedniego okienka uzyskujemy dobierając argumenty operacji MsgBox.
MsgBox może być również użyte gdy w przypadku niemożliwości wykonania operacji np. zapisu do bazy danych, jest konieczne odwołanie odpowiedniego zdarzenia - w poniższym przykładzie zdarzenia "Przed aktualizacją".
Procedura wyświetla komunikat, jeśli pole kombo IDklienta jest puste a następnie odwołuje zdarzenie "Przed aktualizacją" zapobiegając aktualizacji:
Private Sub IDklienta_BeforeUpdate(Cancel As Integer) |
Reasumując, funkcja/polecenie MsgBox ma następujący format:
MsgBox Tekst, Wygląd_okienka, Tytuł_okienka
Tekst i Tytuł_okienka to odpowiednie
teksty, które mają się ukazać w okienku dialogowym, natomiast Wygląd_okienka
to wyrażenie liczbowe określające liczbę i typ wyświetlanych przycisków i
ich rodzaj, styl używanych ikon, identyfikator domyślnego przycisku oraz
modalność okna komunikatu. Brak tego parametru spowoduje przyjęcie wartości
domyślnej vbOKOnly=0
.
Obok instrukcji MsgBox język VBA dostarcza jeszcze funkcji InputBox służącej do wyświetlenia okienka dialogowego, do którego użytkownik wprowadza dane np.
Firma = InputBox("Podaj nazwę firmy, której ogłoszenia chcesz
obejrzeć?")
Zakładamy, że właściwość pola kombo "Ogranicz do listy" ("Limit to list") jest ustawiona na "Tak".
Gdy do pola kombo wpiszemy wartość spoza źródła danych tego pola kombo i będziemy chcieli opuścić to pole, zajdzie zdarzenie "Przy wartości spoza listy" ("On Not in List"), którego standardowa obsługa polega na poinformowaniu o błędzie i nie dopuszczeniu do wyjścia z pola. Procedura obsługi zdarzenia "Przy wartości spoza listy" ma jeden parametr wejściowy:
- NewData – przekazujący wprowadzoną przez użytkownika do pola kombo wartość,
oraz jeden parametr wyjściowy:
- Response – informujący system o przyjętej metodzie postępowania:
w procedurze następuje dodanie nowego elementu do źródła danych pola kombo:
Response
= acDataErrAdded
w procedurze następuje usunięcie wartości NewData z pola kombo za pomocą metody Undo
Response = acDataErrContinue
.Response = acDataErrDisplay
Przykład
Załóżmy, że mamy listę kolorów o
nazwie Kolory i chcemy oprogramować dodanie
nowego koloru do listy. Lista kolorów jest bezpośrednio wpisana do pola "Źródło wierszy"
("Row Source"). Właściwość
"Ogranicz do listy" ("Limit to List") jest ustawiona
na "Tak".
Private Sub Kolory_NotInList(NewData As String, Response As Integer) |
Na zmiennej ctl zapisujemy referencję
do pola kombo będącego w VBA obiektem klasy
Control. W
instrukcji warunkowej prosimy użytkownika o potwierdzenie dodania nowego koloru.
Jeśli użytkownik potwierdzi, ustawiamy argument Response na
acDataErrAdded
czyli na dodanie nowego koloru będącego
wartością argumentu NewData do
źródła wiersza. Jeśli użytkownik wybierze "Cancel", ustawiamy
argument Response
na acDataErrContinue
czyli na kontynuowanie bez wyświetlania komunikatu o
błędzie. Następnie
kasujemy wprowadzoną przez użytkownika wartość (przy pomocy metody Undo
pola formularza).
Uwaga: Zmiana wartości właściwości „Row Source” dotyczy tylko bieżącego obiektu formularza i nie jest zapisywana na stałe do arkusza właściwości samego formularza.
Teraz zadanie dla czytelnika.
Sprawdź w jakiej kolejności występują zdarzenia "Przy wartości spoza listy" ("On Not in List"), "Przed aktualizacją" ("Before Update") oraz "Wyjście" ("On Exit") dla pola listowego. |
Część procedur będących wartościami właściwości zdarzeń, jak to już było widoczne w rozważanych przykładach, ma parametry. Przy tworzeniu procedury zdarzenia, MS Access sam wpisuje nazwy tych parametrów.
Poniżej zamieszczamy listę nagłówków procedur zdarzeń z parametrami w odniesieniu do formularzy i wyjaśniamy znaczenie tych parametrów. Pomijamy tylko zdarzenia związane z użyciem filtrów.
Podstawowymi parametrami są Cancel i Response.
Oto grupa procedur, w nagłówkach których występują te parametry:
Sub Form_Open (Cancel As Integer)
Sub Form_Unload (Cancel As Integer)
Sub Form_BeforeInsert (Cancel As Integer)
Sub Form_BeforeUpdate (Cancel As Integer)
Sub Form_Delete (Cancel As Integer)
Sub Form_BeforeDelConfirm (Cancel As
Integer, Response As Integer)
Pozostałe procedury, mające parametry to:
Sub Form_AfterDelConfirm (Status As
Integer)
Samego zdarzenia "Po usunięciu" nie można już
anulować. Status
określa czy rekord został usunięty:
acDeleteOK - rekord został usunięty,
acDeleteCancel - usuwanie zostało anulowane w Visual Basic,
acDeleteUserCancel - usuwanie zostało anulowane przez użytkownika.
Oprogramuj wszystkie zdarzenia związane z usuwaniem rekordu dając użytkownikowi możliwość anulowania usuwania oraz informując użytkownika o wszystkim co się w tym czasie dzieje. |
Sub Form_KeyPress (KeyAscii As Integer)
-
KeyAscii to kod ASCII naciśniętego klawisza klawiatury.
Sub Form_Error(DataErr As Integer, Response
As Integer)
- DataErr to kod błędu. Umożliwia globalną obsługę błędów w module klasy formularza.
Zdarzenia, których procedury mają parametr Cancel mogą zostać anulowane. Przedstawimy przykład anulowania zdarzenia poprzez ustawienie parametru Cancel na True.
W przykładzie poniżej w procedurze zwolnienia formularza Form1 następuje sprawdzenie, czy w danej chwili pozostał otwarty formularz Form2. Jeśli tak, zdarzenie zwolnienia formularza zostaje anulowane (przez ustawienie parametru Cancel na True) - tj. formularz Form1 pozostaje dalej otwarty.
Private Sub Form_Unload (Cancel As Integer) MsgBox
"Najpierw zamknij formularz Form2." End If
|
Efekt użycia kwerendy wybierającej będącej źródłem danych dla formularza jest widoczny bezpośrednio na formularzu. Często zachodzi potrzeba użycia instrukcji SQL (kwerendy funkcjonalnej) zmieniającej dane w tabeli bazy danych i ta zmiana jest uzależniona od wartości pól formularza.
Do wykonywania instrukcji SQL w kodzie służy metoda RunSQL obiektu DoCmd. Jako parametr podaje się napis instrukcji SQL, która ma być wykonana.
Przykład 1
Używając zwykłej semantyki usuwania, nie można usunąć działu, do którego są przypisani pracownicy. Najbardziej naturalnym rozwiązaniem w takiej sytuacji jest zmiana identyfikatora działu w rekordach wszystkich pracowników usuwanego działu na Null, dokonywana przed usunięciem działu, w procedurze zdarzenia "Przy usunięciu" ("On Delete").
Private Sub Form_Delete(Cancel As Integer) |
Zwróćmy uwagę na sposób formowania instrukcji
UPDATE, w szczególności
sposób dołączenia do niej zawartości pola formularza. Gdyby
Id_działu nie było liczbą tylko napisem, zapis warunku WHERE wyglądałby
następująco:
"WHERE Id_działu = '" & Me![Id_działu] & "'"
. W
sytuacji konieczności zagnieżdżenia cydzysłowów, wewnątrz napisu stosuje
się pojedyncze cudzysłowy ' .. '.
Zwróćmy też uwagę na to, że w napisie instrukcji
SQL nie jest dozwolone umieszczanie odwołań postaci
Forms![nazwa_formularza]![nazwa_pola]
tak jak w poleceniu:
DoCmd.OpenForm
"Pracownicy",,,"Id_inst=Forms![Instytucje]!Id"
Przykład 2
Jeśli użytkownik wprowadza do pola kombo nową wartość, której nie ma na stowarzyszonej liście rozwijanej i chce aby odpowiedni rekord został dopisany do tabeli bazy danych, dołączenie nowego rekordu najlepiej zrealizować za pomocą instrukcji INSERT. Załóżmy na przykład, że przy wprowadzaniu nowego pracownika, zachodzi potrzeba wprowadzenia jednocześnie nowego działu. Użytkownik podaje nazwę nowego działu i zachodzi zdarzenie "Przy wartości spoza listy".
Private Sub Id_działu_NotInList(NewData As String, Response As Integer) |
Zwróćmy uwagę na brak w instrukcji INSERT pola Id_działu typu Autonumer. Jego wartość jest automatycznie ustalana przez MS Access.
Teraz zadanie dla czytelnika.
Do tabeli opisującej osoby dodaj kolumnę „Kolor samochodu”, której wartości znajdują się w tabeli słownikowej Kolory (utwórz ją). Na formularzu do edycji osoby umieść pole kombo z listą rozwijaną o źródle wierszy z tabeli Kolory. Zaprogramuj wpisywanie nowego koloru do tabeli Kolory poprzez wpisanie go do pola kombo. Użyj metody RunSQL. Jakie zdarzenia trzeba oprogramować? |
Za pomocą kodu jest możliwa obsługa jednoczesnego wyboru wielu elementów na liście. Właściwość "Wybór wielokrotny" ("Multi Select") powinna być ustawiona na "Prosty" ("Simple") lub "Rozszerzony" ("Extended"). Domyślne ustawienie "Brak" ("None") oznacza, że wybór dotyczy tylko jednego elementu listy. Korzystając z możliwości wyboru wielokrotnego możemy zaznaczyć wszystkie wymagane elementy z listy a następnie zbiorczo wykonać na nich pewną operację. Przy wyborze prostym wybierane elementy zaznacza się przy pomocy klawisza spacji lub myszki. Przy wyborze rozszerzonym używa się klawiszy CTRL i SHIFT razem z myszką.
Rozważmy formularz działu, na którym jest wyświetlana lista zatrudnionych w nim pracowników. Pole listowe o nazwie Lista składa się z dwóch kolumn zawierających odpowiednio identyfikator pracownika (kolumna o szerokości 0cm - nie wyświetlana) oraz nazwisko pracownika. Po zaznaczeniu grupy pracowników, możemy spowodować ich usunięcie.
Przeglądanie wybranych na liście elementów odbywa się przy użyciu kolekcji ItemsSelected pola listowego oraz funkcji Column zwracającej wartość odpowiedniego elementu na liście. Pierwszym argumentem jest numer kolumny. Drugim argumentem jest pozycja (na liście) będąca elementem kolekcji ItemsSelected. Pozycja na liście jest wartością typu Variant. W przypadku listy powyżej, kolekcja ItemsSelected składa się z numerów 0,3,4. Przy wyborze jednokrotnym można pominąć drugi argument. Do usunięcia osoby wykorzystujemy tutaj metodę RunSQL obiektu DoCmd, której argumentem jest instrukcja SQL. Odpowiednia procedura wygląda następująco:
Private Sub Usuń_Click() |
Zauważmy, w jaki sposób na zmiennej
ZapSQL jest tworzona instrukcja SQL do wykonania – przez wstawienie konkretnej
wartości liczbowej elem.Column(0,poz) do warunku WHERE.
Na wykładzie kontynuowana była nauka programowania
aplikacji bazodanowych. Czytelnik zapoznał się z obiektami reprezentującymi w
kodzie: polecenie – DoCmd, błąd – Err, aplikację – Application,
uruchamianie – Debug. Czytelnik poznał takie techniki
programistyczne jak obsługa błędów,
aktualizacja listy pola kombo, anulowanie zachodzących zdarzeń, wykonywanie
instrukcji SQL, programowanie wyboru wielokrotnego z listy.
Strona przygotowana przez Lecha Banachowskiego - 01/04/03 .