Richtig einsteigen: Access 2013 VBA-Programmierung

Lorenz Hölscher
Richtig einsteigen:
Access 2013 VBA-Programmierung
Von den Grundlagen bis zur professionellen Entwicklung
Inhaltsverzeichnis
Teil I: Erste Schritte
1
Einleitung 11
1.1
Die Themenschwerpunkte dieses Buchs Visual Basic for Applications Die Beispieldatenbank Wie Sie am besten mit diesem Buch arbeiten Aufbau des Buchs Die Icons Unterstützung für dieses Buch Genug der Vorrede und hinein ins Vergnügen 11
11
12
15
15
16
16
17
1.2
Teil II: Grundlagen von VBA
2
Die ersten VBA-Prozeduren erstellen 19
2.1
2.3
Die Programm-Oberfläche Wichtige Komponenten des VBA-Editors Das »klassische« erste Programm erstellen »Hello world!« unter der Lupe Weitere Parameter nutzen Eigene Prozeduren aufrufen Funktion mit Parametern Funktionen auch außerhalb von VBA aufrufen Properties Prozeduren verlassen Kommentare Übungen zu diesem Kapitel 19
19
21
23
24
26
32
39
40
44
45
45
3
Datentypen und Variablen 47
3.1
Datentypen im Überblick Die Speicherung von Daten im Computer Sonstige Datentypen Namen für Variablen Groß- und Kleinschreibung von Namen Ungarische Notation 47
47
52
54
54
55
2.2
3.2
6
3.3
3.4
Inhaltsverzeichnis
3.5
Deklaration von Variablen Gültigkeitsbereiche Lokale Gültigkeit Modul-öffentliche Gültigkeit Datei-öffentliche Gültigkeit Private Prozeduren und Module Übungen zu diesem Kapitel 56
64
65
66
69
75
78
4
Datentyp-Sammlungen 79
4.1
4.2
4.3
Spezielle Variablen im Überblick Eigene Datentypen Typ-Definitionen für API-Aufrufe Bessere Listenauswahl mit Enumerationen Arrays Mehrdimensionale Arrays Variant als Pseudo-Array Collection Übungen zu diesem Kapitel 79
79
83
85
89
93
93
95
96
5
Operatoren, Entscheidungen und Schleifen 97
5.1
5.2
Vergleichsoperatoren Logische Operatoren Nutzung von VBA-Konstanten in SQL Verzweigungen Bedingung überprüfen Die Select-Anweisung Schleifen For/Next-Schleifen For Each/Next-Schleifen Do/Loop While/Wend-Schleife Übungen zu diesem Kapitel 97
98
109
112
112
119
123
124
129
134
142
143
6
Fehler und Ausnahmen behandeln 145
6.1
Fehlerfreie Programme Fehler provozieren Fehlerbehandlung ankündigen Fehler beheben Fehler ignorieren Zentrale Fehlerbehandlung 145
146
147
150
151
153
4.4
4.5
4.6
4.7
5.3
5.4
5.5
Inhaltsverzeichnis
6.2
6.3
Programmablauf verfolgen Code zeilenweise ausführen Code anhalten Überwachungen hinzufügen Übungen zu diesem Kapitel 7
156
156
156
157
158
7
Klassen 159
7.1
7.2
Einführung in Klassenkonzepte Grundgerüst einer Klasse Eine erste Mini-Klasse Initialisieren und Terminieren Entwurf der Stoppuhr-Klasse Funktionalität ergänzen Bessere Funktionalität Aufgaben unterschiedlich lösen Gleiche Schale – anderer Kern Implements als »Klassen-Aufsicht« Klassen mit Unterobjekten Objekte von Objektauflistungen Klassen mit Oberfläche Übungen zu diesem Kapitel 159
159
160
161
163
164
165
170
173
176
180
181
185
188
7.3
7.4
7.5
7.6
7.7
7.8
7.9
7.10
Teil III: Datenzugriff
8
Domänen-Funktionen 189
8.1
8.2
Zugriff auf Daten per Methode Einfacher Zugriff Langsamer Zugriff Weitere Einschränkungen Übungen zu diesem Kapitel 189
191
192
193
194
9
Recordsets 195
9.1
9.2
Zugriff auf Tabellen und Abfragen Datensatzwechsel Prüfung auf vorhandene Datensätze Zugriff auf viele Datensätze Zugriff auf verschachtelte Datensätze Datensatz-Typen Datenquelle filtern Daten zusammenfassen Suchen und Finden 195
199
200
201
203
206
208
210
217
9.3
9.4
9.5
9.6
8
9.7
Inhaltsverzeichnis
Daten schreiben Datensätze anfügen Datensätze verändern SQL und VBA gemeinsam SQL-Aktionen ohne Warnmeldung Gespeicherte Aktionsabfragen ohne Warnmeldung Transaktionen Zugriff auf andere Programme Excel schreibt in Access-Tabellen Access schreibt in Word-Dokumente Übungen zu diesem Kapitel 224
225
230
231
231
232
233
236
236
239
241
10
Formulare 243
10.1
10.3
10.4
10.5
Programmierte Formulare Ereignisse Kontrollelemente per VBA ansprechen Andere Formulare steuern Unterformulare Weitere Formular-Steuerungen Benutzeranmeldung Haupt- und Unterformulare Dynamische Formulare Übungen zu diesem Kapitel 243
244
247
254
254
260
264
272
287
327
11
Berichte 329
11.1
Programmierte Berichte Berichte mit gemeinsamen Titeln Berichte mit dynamischen Inhalten Programmiertes Wasserzeichen Übungen zu diesem Kapitel 329
329
332
334
338
9.8
9.9
9.10
9.11
10.2
11.2
Teil IV: Anwendungen erstellen
12
Start-Einstellungen 339
12.1
AutoStart-Methoden Das Makro AutoExec Start-Dialog Start-Makros umgehen Notausstieg Start-Logo Übungen zu diesem Kapitel 339
339
340
341
342
343
344
12.2
12.3
12.4
Inhaltsverzeichnis
9
13
Menüband anpassen 345
13.1
Eigene Register Konzept eigener Register Access-Optionen Gruppen hinzufügen Callback-Prozeduren Kontrollelemente dynamisch aktivieren Besser als Menüs: Listen Menüband-Variable wiederherstellen Übungen zu diesem Kapitel 345
346
346
351
356
361
366
375
378
14
Sonstige Optimierungen 379
14.1
14.5
14.6
Weitere Verbesserungen SQL-gerechtes Datum Listen programmieren Listen mit Callback-Technik Callback-Funktion schreiben Fiktive Daten anzeigen Listen mit Mehrfachauswahl Aktualisierungsmeldungen Textmeldung in der Statuszeile Fortschrittsbalken in der Statuszeile Formular-Status ermitteln Parameterabfragen kapseln Abfragen mit Funktionen Externe Berichte einbinden Übungen zu diesem Kapitel 379
379
380
380
383
387
389
390
390
391
393
394
397
401
407
15
Lösungen zu allen Übungen 409
16
Stichwortverzeichnis 433
13.2
14.2
14.3
14.4
Formulare
Programmierte Formulare
10.1
Formulare bilden die Oberfläche, mit der sich eine Datenbank typischerweise einem
Benutzer präsentiert. Wie Sie bei der Arbeit mit Access 2013 sicherlich schon bemerkt haben,
leisten die Eigenschaften in Formularen und Kontrollelementen schon so viel, dass eigentlich
erst einmal überhaupt keine VBA-Programmierung notwendig wäre. Mit VBA allerdings
steigt die Bedienungsfreundlichkeit Ihrer Datenbank enorm, sowohl in der Benutzung als
auch für die Programmierung.
Dabei lassen sich verschiedene Aspekte für den Einsatz von VBA in Formularen skizzieren:
gabe oder Datenanalyse unterstützen:
zz
Schaltflächen zur Anzeige synchronisierter Formulare, damit der Benutzer nicht
den passenden Datensatz mühsam aus einem anderen Formular heraussuchen
muss
zz
Automatisch (de-)aktivierte Kontrollelemente, die nur in einem bestimmten
Zusammenhang sinnvoll, hilfreich oder überhaupt zulässig sind
zz
Erweiterte Gültigkeitsprüfungen, die mit den tabellarischen Regeln nicht möglich
wären
zz
Eingabe von Standardangaben wie etwa dem letzten Änderungszeitpunkt und
Namen des Benutzers, wenn ein Datensatz verändert wird
zz
Überwachung von Maximal- oder Mindestwerten für bestimmte Datensatzinhalte
und deren Anzeige beispielsweise als »Ampel« auf der Bedienungsoberfläche
zzEinfachere Programmierung: durch effektiveren Einsatz mehrfach genutzter Objekte:
zz
Aufruf eines einzigen Formulars für ähnliche Inhalte, sodass der Code darin nicht
parallel verwaltet werden muss
zz
Unterformulare mit wechselnden Inhalten, die ein einheitliches Corporate Design
auch für die Datenbank sicherstellen
zz
Zugriff auf allgemeine Inhalte wie den aktuell angemeldeten Benutzer, Dateien
aus einem Verzeichnis oder Optionen der Datenbank selbst
III – Datenzugriff
zzVerbesserte Benutzung: durch Kontrollelemente, die den Benutzer bei der Datenein-
244
Kapitel 10: Formulare
Diese Liste ließe sich noch beliebig erweitern, weil jede Datenbank weitere Anforderungen
in ihren Themenbereichen mitbringt. Sie werden aber sehen, dass hierin schon ausreichend
Potenzial für die VBA-Programmierung steckt und vor allem jede Lösung oft zwei neue Ideen
für weitere Verbesserungen nach sich zieht.
Ereignisse
Anhand eines einfachen Beispielformulars lassen sich die Konzepte und Methoden am
besten verfolgen.
a Erstellen Sie in der Datenbank Umbuchung.accdb basierend auf der Tabelle tblKunden ein
Standardformular. Das geht am schnellsten, indem Sie die Tabelle im Navigationsbereich
markieren und dann in der Registerkarte ERSTELLEN in der Gruppe Formulare auf das
Symbol Formular klicken.
b Speichern Sie dieses Formular unter dem Namen frmKundenEinfach und passen Sie nach
Belieben die Größen der Eingabefelder an. Es sollte anschließend aussehen wie in der
folgenden Abbildung:
Abbildung 10.1: Das
neu erstellte Formular
frmKundenEinfach
c Schon hier ist eine einfache Verbesserung denkbar, denn für einen Interessenten (der als
kndFunktion den Wert 0 hätte), sind die Angaben im rechten Teil des Fensters überflüssig.
Erst als Käufer oder Verkäufer sind Konto-Informationen notwendig.
d Wechseln Sie daher in die Entwurfsansicht des Formulars und fügen eine Schaltfläche
ein, die für die entsprechende Anpassung sorgen soll.
e Klicken Sie dazu in der Registerkarte ENTWURF in der Gruppe Steuerelemente auf das
Symbol Schaltfläche und ziehen im Formularentwurf ein Rechteck dafür auf.
f Die bereits erwähnte Ungarische Notation gilt auch und ganz besonders für Steuerelemente, daher benennen Sie die Schaltfläche von Befehl22 oder Ähnlichem direkt
in btnFunktion um. Für die Eigenschaft Beschriftung können Sie den Text An Funktion
anpassen wählen.
10.1 Programmierte Formulare
245
Kontrollelemente mit ihrem Code verbinden
Bis auf die Programmierung ist nun so weit alles vorbereitet, aber die Verbindung zwischen
dieser Schaltfläche und ihrem VBA-Code bedarf einer kurzen Erklärung.
Während bisher alle Prozeduren von Ihnen wissentlich (und in Modulen meistens mit der
F5-Taste) ausgelöst wurden, wird das in Formularen und Berichten anders sein. Jedes Objekt,
also sowohl das Formular selbst als auch jedes Kontrollelement, hat eine vorgegebene Liste
von Ereignissen, auf die es reagiert.
Wenn das Objekt markiert ist, können Sie im Eigenschaftenblatt auf dessen Registerkarte
Ereignis alle Ereignisse sehen, die für dieses Objekt genutzt werden können. Jeder dieser
(deutschsprachigen) Ereignisnamen führt im VBA-Code zu einem reservierten Prozedurnamen, der sich aus dem Objektnamen und der englischen Bezeichnung des Ereignisses
zusammensetzt.
Wenn Sie erst Ereignisse zu einem Kontrollelement erzeugen und dieses Objekt später
umbenennen, werden die Prozedurnamen nicht dynamisch angepasst! Daher sollten
Sie Kontrollelemente immer sofort korrekt (und im Sinne der Ungarischen Notation)
benennen.
Namen werden nicht
dynamisch verwaltet
Anders als das Beim Klicken-Ereignis bringen viele der automatisch erzeugten Prozeduren
auch Parameter mit. Diese sind ebenso vorgegeben und können nicht verändert werden.
a Um die Schaltfläche nun also mit dem zugehörigen Code zu verbinden, wählen Sie aus
der Auswahlliste hinter Beim Klicken den Eintrag [Ereignisprozedur] aus. Alternativ können
Sie auch per Doppelklick auf den Zeilentitel Beim Klicken zum jeweils nächsten Listeneintrag weiterschalten.
Abbildung 10.2: Die
Beim Klicken-Eigenschaft der Schaltfläche
��I – Da����u����
Im Falle der eben erstellten Schaltfläche (btn... als Abkürzung von Command Button, engl.:
Schaltfläche) und des Ereignisses Beim Klicken (engl.: click) ergibt sich daher im VBA-Code
der Prozedurname btnFunktion_Click. Dieser Name ist zwingend und wird exakt so aufgerufen, wenn später mal ein Benutzer diese Schaltfläche anklickt.
246
Kapitel 10: Formulare
b Wenn Sie nun die nebenstehende Schaltfläche mit den drei Pünktchen anklicken, steht
der Cursor in der automatisch erzeugten Prozedur btnFunktion_Click des zugehörigen
Moduls.
Abbildung 10.3: Die
automatisch erzeugte
Prozedur im Formularmodul
c Wenn Sie links oben im Projekt-Explorer nachsehen, werden Sie feststellen, dass dieses
Fenster zwar wie ein normales Modulfenster aussieht und auch so benutzt wird, dass es
aber nicht unter Module, sondern unter Microsoft Access Klassenobjekte einsortiert ist. Es
handelt sich nämlich bei Formularen (und Berichten) wie bereits erwähnt um Klassen,
was aber derzeit ohne Bedeutung ist.
d Die Prozedur wird ausdrücklich mit dem Zusatz Private versehen, ist also von anderen
Modulen aus nicht sichtbar. Auch diese Besonderheit können Sie im Moment einfach
ignorieren.
e Schreiben Sie nun in der Prozedur btnFunktion_Click eine MsgBox-Anweisung, damit Sie
gleich testen können, ob alles korrekt funktioniert:
Private Sub btnFunktion_Click()
MsgBox “Schaltfläche wurde angeklickt!”
End Sub
f Wechseln Sie zur Access-Oberfläche und in die Laufzeitansicht des Formulars. Sobald Sie
jetzt die Schaltfläche anklicken, erscheint erwartungsgemäß die Meldung:
Abbildung 10.4: Die
Schaltfläche zeigt korrekt die Meldung an
Damit hat auch dieser Code sozusagen den »Hello World!«-Test bestanden. Alles funktioniert
wie geplant, jetzt muss nur noch eine sinnvolle Anwendung daraus werden. Eigentlich sollen
10.1 Programmierte Formulare247
ja abhängig von der ausgewählten Funktion die Kontrollelemente auf der rechten Seite des
Formulars eventuell deaktiviert oder ausgeblendet werden.
Kontrollelemente per VBA ansprechen
Damit Kontrollelemente zur Laufzeit auf einem Formular ein- oder ausgeblendet werden
können, müssen Sie sie im Code eindeutig benennen können. Praktischerweise gilt auch
hier die bereits erwähnte Auflistung von Objekten, wie sie in VBA vielerorts üblich ist.
Sie müssen nur die oberste Instanz, das sogenannte Elternobjekt, finden. Die mühsame
Methode wäre die schon erwähnte Auflistung aller (geöffneten!) Formulare unterhalb des
Application-Objekts:
Application.Forms(“frmKundenEinfach”)
Das würde funktionieren, wäre aber nicht nur ein furchtbar langer Text, sondern vor allem
auch vom Namen des Formulars abhängig. Ändert sich dieser, müssen Sie überall im Code
nachbessern. Daher können Sie für jeden Code innerhalb eines Formular- und Berichtsmoduls
eine automatisch erzeugte Objektvariable Me nutzen. Im Formular frmKundenEinfach heißt
die Kurzfassung des oben genannten Elternobjekts daher ganz einfach:
Diese Objektvariable verweist immer auf das Formular (oder ebenso den Bericht, ohne das
jetzt weiterhin explizit zu erwähnen), in dem sich der Code befindet. Das ist nicht nur bedeutend lesefreundlicher und kürzer zu schreiben als die erste Fassung, sondern auch flexibel
und unabhängig vom tatsächlichen Namen des Formulars.
Sie könnten an dieser Stelle einwenden, dass Access doch eine Objektnamen-Autokorrektur bietet (in den Access-Optionen in der Kategorie Aktuelle Datenbank und dort in
der Gruppe Optionen für Objektnamen-Autokorrektur). Diese korrigiert bei FeldnamenÄnderungen in Tabellen beispielsweise in allen betroffenen Abfragen dieses Feld.
Das tut sie leider nicht! Schon für Abfragen greifen die Änderungen nur in Auswahlabfragen, nicht aber in Aktionsabfragen. Und selbst in Auswahlabfragen funktioniert dieser
Mechanismus nicht überall, denn auch Union-Abfragen werden missachtet. Formular- und
Berichts-Felder werden zwar berücksichtigt, aber VBA-Code wird überhaupt nicht
korrigiert.
Von diesem Elternobjekt Me aus finden Sie eine Auflistung aller Kontrollelemente, die dieses
konkrete Formular enthält:
Me.Controls
ObjektnamenAutokorrektur hilft
nicht
III – Datenzugriff
Me