Tag: procedura VBA

Edytor VBA – prywatne i publiczne

W edytorze  VBA każdy moduły własne obiektów ma charakter prywatny. Oznacza to, że wszystkie zapisane w nim funkcje i procedury są dostępne tylko z poziomu VBA.

Moduły ogólne są domyślnie publicznie. Zdefiniowane w nich procedury i funkcje publiczne są widoczne również z poziomu Accessa (m.in. w kreatorze wyrażeń). Funkcje i procedury prywatne mogą być wykorzystane jedynie z poziomu procedur zapisanych w tym samym module. 

Np. taka funkcje w module ogólnym:

Public Function PierwszyNumer()
   'funkcja publiczna
Pierwszy = DFirst(„NumerKatalogowy”, „TabelaKsiazki”, „DataP is null”)
End Function

Private Function OstatniNumer()
   'funkcja prywatna
OstatniNumer = DLast(„NumerKatalogowy”, „TabelaKsiazki”, „DataP is null”)
End Function

W kreatorze wyrażeń wygląda to tak:

kliknij, aby powiększyć

W bazie Biblioteka, w module ModuleFunction  widoczna jest tylko jedna funkcja – zdeklarowana jako Public. Prywatna funkcja OstatniNumer nie jest tu widoczna, może być wykorzystana tylko w obrębie tego samego modułu. 

Przy bardziej rozbudowanych projektach i  wielu procedurach w kodzie może być więcej modułów ogólnych z różnymi procedurami i funkcjami. Nie wszystkie z nich muszą być widoczne i dostępne również bezpośrednio w Accessie, czasem warto je „utajnić”. Wystarczy na początku modułu ogólnego wstawić Option Private Module

kliknij, aby powiekszyć

Od tej pory – wszystkie funkcje i procedury publiczne w takim module będą dostępne z innych modułów, ale nie w Accessie. 
W praktyce zawsze tak robię, porządkując funkcje i procedury i część z nich umieszczając je modułach prywatnych, a część – publicznych. Zmieniam też nazwy modułów, żeby się nie pogubić i utrzymać porządek w kodzie. 
Np. tak:

kliknij, aby powiększyć

 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Zdarzenie formularza: przy zamknięciu

 Przy zamknięciu to jedno ze zdarzeń uruchamiających się automatycznie przy zamykaniu formularza. Dostępne jest w arkuszu właściwości formularza oraz w module VBA formularza. 

W edytorze VBA jest to procedura o nazwie Form_Close. Jest uruchamiana automatycznie przy zamknięciu, niezależnie od sposobu, a jaki formularz jest zamykany: makrem, procedurą VBA czy po prostu przyciskiem Zamknij.
W ramach tej procedury można uruchomić każdą dowolną procedurę, sprawdzić np. czy wszystkie zmiany zostały zapisane, otworzyć inny formularz, tak, żeby  użytkownik nagle nie zobaczył pustego okna bazy Access itp. Możliwości jest sporo, w zależności od potrzeb. 

Trzeba jednak pamiętać o tym, że gdy w formularzu mamy przycisk do zamykania formularza, a jednocześnie jest oprogramowane zdarzenie Form_Close, zadziałają obie procedury. Musimy uważać, żeby nie były sprzeczne z sobą. 

Np.w takich procedurach:

Private Sub Form_Close()
DoCmd.OpenForm „menustart”    ’2 linia
End Sub
Private Sub Zamknij_Click()
DoCmd.Close   ’1 linia
DoCmd.OpenForm „ksiazka”     ’3 linia
End Sub

Po naciśnięciu przycisku Zamknij, uruchomiona zostanie przypisana do niego procedura i wykonana pierwsza linia kodu. Ponieważ do procedura zamykająca formularz, nastąpi przejście do automatycznego zdarzenia formularza czyli Form_Close i wykonana zostanie 2 linia kodu. Potem powrót do procedury Zamknij_Click i trzecia linia kodu. W efekcie zostaną otwarte 2 różne formularze, aktywny będzie formularza „ksiazka”. 

Linki do innych zdarzeń formularza:

Najczęściej używane zdarzenia formularza

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Zdarzenie formularza: przy otwarciu

 Przy otwarciu to jedno ze zdarzeń uruchamiających się automatycznie po otwarciu formularza. Dostępne jest w arkuszu właściwości formularza oraz w module VBA formularza. 
Zdarzenie to sprawdza, czy dany użytkownik ma prawo otworzyć ten formularz, przekazuje argumenty wejściowe itp. Dopiero później następuje załadowanie rekordów. 

W edytorze VBA jest to procedura o nazwie Form_Open:Procedura ma jeden argument: 
   Cancel – as Integer, argument opcjonalny. Wartość domyślna to 0 lub False

Przypisanie do zmiennej Cancel wartości 1 (lub Prawda/True) spowoduje anulowanie zdarzenia inicjującego czyli otwarcia formularza. W efekcie formularz nie zostanie otwarty. 

Przykładowa procedura:

Private Sub Form_Open(Cancel As Integer)
Dim DzienTygodnia As Integer
DzienTygodnia = Weekday(Date, vbMonday)
If DzienTygodnia = 7 Then
   MsgBox „Dziś jest niedziela, odpocznij”
Cancel = 1
End If
End Sub

W procedurze tej następuje sprawdzenie bieżącego dnia tygodnia i jeżeli jest to niedziela – wyświetla się komunikat i formularz zostaje zamknięty. 
Oczywiście w ramach tej procedury można uruchomić każdą dowolną procedurę, sprawdzić przekazany argumentem wejściowym login użytkownika itp. Możliwości jest sporo, w zależności od potrzeb. 

Linki do innych zdarzeń formularza:

Najczęściej używane zdarzenia formularza

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Etykieta w kodzie VBA

Etykieta w kodzie VBA to konkretne miejsce w kodzie VBA. Jest to zawsze jedno słowo (ciąg znaków bez spacji) zakończone dwukropkiem.
Linia z etykietą zawsze zaczyna się od pierwszego znaku w danej linii.

If Warunek Then GoTo Etykieta1
   ’ciag instrukcji A
Etykieta1:
  ’ciag instrukcji B
End If

W tym kodzie VBA jeżeli jest spełniony Warunek, za pomocą wyrażenia GoTo następuje od razu skok wykonywania kodu do znajdującej się tuż pod Etykietą1 ciągu instrukcji B, z pominięciem ciągu instrukcji A.
Linia  z etykietą nie zatrzymuje kodu, nie można tez w niej ustawić punktu przerwania.

Generalnie jednak taki przykład kodu nie jest wzorcowy i wprawdzie działa i to skutecznie, ale raczej, jeżeli są inne możliwości, nie powinno się go stosować.
Za to jest to zdecydowanie najlepsze i standardowo stosowane działanie do obsługi błędów wykonania.

 

Data jako parametr kwerendy

Czy data/czas może być parametrem kwerendy? Oczywiście.
W dodatku najczęściej stosowana jest tu nie pojedyncza data tylko zakres dat od-do.

W siatce kwerendy wpisujemy kryterium:

kliknij, aby powiększyć

Zakres dat wpisujemy przy zastosowaniu słów kluczowych Between i And. Oczywiście można też wstawić tu kryterium w formie pola parametru, gdzie zamiast sztywnego wpisania dat – można wstawić je jako parametr np.
Between [DataOd] And [DataDO]
Jakoś jednak nie polecam, gdyż praktyka wskazuje, że tam gdzie użytkownik wpisuje daty – wcześniej czy później pojawią się problemy związane z formatem tej daty i w konsekwencji – będą błędy.

Jako parametry kwerendy można też wykorzystać pola formularza.
Np.

kliknij, aby powiększyć

W formularzu są 2 pola TOD i TDO, w których wpisujemy daty, a w kwerendzie odwołania do tych pól:
Between [Formularze]![SpisKasiazek]![TOD] And [Formularze]![SpisKasiazek]![TDO]
Oczywiście w porządnej aplikacji należałoby zabezpieczyć się przed wstawieniem tu innej wartości niż data, rozważyć możliwość, że jedno pole jest puste lub data DO jest mniejsza od daty OD itp., ale w formularzu da się bez problemu wprowadzić takie mechanizmy przed błędami.

A jak to wygląda w kodzie VBA? Na przykład tak.

Private Sub PolecenieSzukaj_Click()
Dim MojaKwerenda As String
Dim DataOD As Date
Dim DataDO As Date
DataOD=Me.TOD
DataDO=Me.TDO
MojaKwerenda = „SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Cena, TabelaKsiazki.Dzial, TabelaKsiazki.DataP ” & _
„FROM TabelaKsiazki LEFT JOIN TabelaDzial ON TabelaKsiazki.Dzial = TabelaDzial.IDKat ” & _
„WHERE TabelaKsiazki.DataP  Between #” & DataOD &_
„# AND #” &  DataDO & „#;”
Me.RecordSource = MojaKwerenda
Me.Requery
End Sub

Teoretycznie – wszystko to powinno działać. A w praktyce – może się okazać, że nie zawsze i nie wszędzie. Na kilku komputerach – jest OK, a na jakimś jednym – nagle nie. Ostatnio taki problem pojawił się u mnie w aplikacje Excela – opisałam to tu:
Filtrowanie tabeli
Tego typu problemy zdarzały mi się już wcześniej. Teraz zapobiegawczo, wszędzie tam gdzie daty są kluczowym elementem – zamieniam je na liczby, gdyż:
Data i czas to liczba

W tym konkretnym przypadku:

    • w kwerendzie, w której wstawiam kryteria parametryczne – dokładam dodatkową kolumnę oparta o formułę:
      =Clng(DataP)
    • taką samą konwersję wykonuję w stosunku do dat w polach formularza
Private Sub PolecenieSzukaj_Click()
Dim MojaKwerenda As String
Dim DataOD As Date
Dim DataDO As Date
Dim LDataOD As Long
Dim LDataDO As Long
DataOD=Me.TOD
DataDO=Me.TDO
LDataOD=Clng(DataOD)
LDataDO=Clng(DataDO)
MojaKwerenda = „SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Dzial,  ” & _ „TabelaKsiazki.DataP, CLng([DATAP]) AS LDataP ” & _
„FROM TabelaKsiazki LEFT JOIN TabelaDzial ON TabelaKsiazki.Dzial = TabelaDzial.IDKat ” & _
„WHERE TabelaKsiazki.LDataP  Between ” & LDataOD &_
” AND ” &  LDataDO & „;”
Me.RecordSource = MojaKwerenda
Me.Requery
End Sub

Oczywiście – tu już nie ma znaków # przed i po zmiennych – one są tylko w stosunku do zmiennych typu Data.

 

Kwerenda parametryczna w kodzie VBA

Prostą kwerendę parametryczną opisałam tu:
Kwerenda parametryczna
Operator Like

Oczywiście to tylko proste przykłady i dla kwerend bezpośrednio w Accessie. A jak zrobić to w VBA?
Załóżmy, że mamy taki formularz ciągły:

kliknij, aby powiększyć

Jego źródłem rekordów jest kwerenda

SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Cena, TabelaKsiazki.Dzial, TabelaKsiazki.DataP FROM TabelaKsiazki LEFT JOIN TabelaDzial ON TabelaKsiazki.Dzial = TabelaDzial.IDKat;

W nagłówku formularza jest też niezwiązane pole tekstowe TSzukaj  oraz przycisk polecenia PolecenieSzukaj , pod którym jest procedura VBA.  Załóżmy, że chcemy wyfiltrować rekordy, gdzie w tytule jest zawarty jest tekst wpisany do pola TSzukaj. Taka procedura mogłaby wyglądać tak:

Private Sub PolecenieSzukaj_Click()
Dim MojaKwerenda As String
Dim CoSzukam As String
CoSzukam = Nz(Me.TSzukaj, „”)
If CoSzukam = „” Then
MojaKwerenda = „SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Cena, TabelaKsiazki.Dzial, TabelaKsiazki.DataP ” & _
„FROM TabelaKsiazki LEFT JOIN TabelaDzial ON TabelaKsiazki.Dzial = TabelaDzial.IDKat;”
Else
MojaKwerenda = „SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Cena, TabelaKsiazki.Dzial, TabelaKsiazki.DataP ” & _
„FROM TabelaKsiazki LEFT JOIN TabelaDzial ON TabelaKsiazki.Dzial = TabelaDzial.IDKat ” & _
„WHERE TabelaKsiazki.Tytul Like '*” & CoSzukam & „*’;”
End If
Me.RecordSource = MojaKwerenda
Me.Requery
End Sub

We wpisie na blogu, w zależności od przeglądarki,  może to różnie wyglądać, więc na wszelki wypadek zwracam uwagę na łamanie linii w zapisie kodu SQL w edytorze VBA – jest to ciąg tekstowy, więc koniec linii musi być zakończony znakami & _  (pomiędzy znakami jest spacja).

Sam parametr – w tym przypadku prezentowany przez zmienną CoSzukam, też ma swoje wymagania. Jest zapisany w linii kodu:
WHERE TabelaKsiazki.Tytul Like ’*” & CoSzukam & „*’;”
Na czerwono zaznaczyłam znaki apostrofu górnego – sa konieczne, jeśli będąca parametrem zmienna jest typu String czyli tekstowa. Natomiast te gwiazdki – to symbole zastępcze związane z operatorem Like. Oczywiście można użyć innych symboli z listy tam wymienionych. Gdyby na początku nie było gwiazdki – kwerenda zwróciłaby rekordy, gdzie powiązane pole zaczynałoby się dokładnie tym, co jest wpisane w TSzukaj.

Dla wartości wartości typu Data – zamiast apostrofów musi być natomiast znak #. Dla wartości liczbowych – nie ma w ogóle znaków, w które wstawiany jest parametr. Nie stosuje się też tu operatora Like.

Kurs SQL w analizie danych - zaawansowane techniki

 

Kwerenda usuwająca w widoku SQL

Kwerenda usuwająca  w kodzie SQL zaczyna się zawsze od słów DELETE …. SET
Pełen kod SQL takiej kwerendy można podejrzeć w widoku SQL projektu kwerendy:

kliknij, aby powiększyć
DELETE TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Cena, TabelaKsiazki.Dzial, TabelaKsiazki.DataP, TabelaKsiazki.Bestseller
FROM TabelaKsiazki;

Cała procedura VBA uruchamiająca taką kwerendę może wyglądać tak:

Public Sub MojaProcedura()
Dim Kwera As String
Kwera = „DELETE TabelaKsiazki.NumerKatalogowy, ” & _
„FROM TabelaKsiazki;”
DoCmd.SetWarnings False
DoCmd.RunSQL Kwera
DoCmd.SetWarnings True
End Sub

We wpisie na blogu  może to różnie wyglądać, ale na wszelki wypadek zwracam uwagę na łamanie linii w zapisie kodu SQL w edytorze VBA – jest to ciąg tekstowy, więc koniec linii musi być zakończony znakami & _ 
(w środku jest spacja).

No i to co ważne – w ten sposób po uruchomieniu kwerendy – usuwane są wszystkie rekordy w tabeli. Takie operacje w bazie raczej rzadko się zdarzają. Kwerenda usuwająca (i nie tylko) jest stosowana raczej w formie kwerendy parametrycznej, gdy usuwane są tylko wybrane rekordy tabeli.

Kurs SQL w analizie danych - zaawansowane techniki