Programmierung der KVA

Im Beitrag Kunden, Verträge und Anschreiben verwalten lernen Sie eine komplette Anwendung kennen. Der vorliegende Beitrag beschreibt die Technik, die sich hinter dieser Anwendung verbirgt – angefangen mit dem Datenmodell über die Abfragen, Formulare und Berichte bis hin zu den verwendeten VBA-Prozeduren. Spezielle Themen werden in weiteren Beiträgen vertieft, auf die wir an geeigneter Stelle verweisen.

Datenmodell

Das Datenmodell umfasst einige Tabellen, welche die Daten für die eigentliche Anwendung liefern – nämlich die Verwaltung von Kunden, Verträgen und Anschreiben bezüglich dieser Kunden und Verträge. Zusätzlich gibt es noch eine Optionen-Tabelle, eine Tabelle, welche Informationen über Laufzeitfehler speichert sowie eine zum Speichern temporärer Daten beim Erstellen eines Word-Dokuments.

Wir durchlaufen das Datenmodell etwa in der Reihenfolge, in der wir die Elemente der Benutzeroberfläche im Beitrag Kunden, Verträge und Anschreiben verwalten (www.access-im-unternehmen.de/854) beschrieben haben. Dort beginnen wir mit dem Übersichtsformular zur Anzeige der Kunden, welches als Datenherkunft die Tabelle tblKunden verwendet. Zumindest steht dies in der entsprechenden Eigenschaft des Unterformulars sfmKundenuebersicht – tatsächlich gibt es in der Datenbank aber gar keine Tabelle namens tblKunden. Diese Tabelle gab es aber im ersten Entwurf der Datenbank – und sie sah genau so aus wie die Tabelle, die jetzt tblKundenBase heißt (s. Bild 1). Warum der Name dieser Tabelle geändert wurde, erfahren Sie weiter unten.

Bild 1: Entwurf der Tabelle tblKundenBase (früher tblKunden)

Die Tabelle enthält alle für die Kommunikation mit einem Kunden notwendigen Daten wie die Kundennummer (die manuell vergeben wird und vom Wert des Primärschlüsselfeldes KundeID abweichen kann), AnredeID, Vorname, Nachname und weitere Felder.

Felder, die auf ID enden, deuten normalerweise auf das Vorhandensein von Beziehungen zu anderen Tabellen hin, die als Lookup-Tabellen zur Auswahl von Werten für das betroffene Feld dienen können. So ist es auch bei der Tabelle tblKundenBase im Fall der Felder AnredeID und PersoenlicheAnredeID. Beide Felder sind mit entsprechenden Lookup-Tabellen verknüpft, die Sie in Bild 2 vorfinden.

Bild 2: Die Tabellen tblAnreden und tblPersoenlicheAnreden

Verträge speichern

Die Verträge zu jedem Kunden werden in der Tabelle tblVertraege gespeichert. Diese enthält Felder zum Eintragen der wesentlichen Vertragsdaten, die selbst nicht in der Datenbank verwaltet werden (s. Bild 3).

Bild 3: Entwurf der Tabelle tblVertraege

Ein sehr wichtiges Feld dieser Tabelle heißt KundeID und dient zum Eintragen des Wertes des Feldes KundeID der Tabelle tblKundenBase für den Kunden, dem der Vertrag zugeordnet ist.

Weitere Fremdschlüsselfelder dieser Tabelle heißen ZahlweiseID, GesellschaftID, VertragsartID und BankverbindungID.

Bei der mit dem Feld ZahlweiseID verknüpften Tabelle tblZahlweisen handelt es sich wiederum um eine einfache Lookup-Tabelle, die lediglich die entsprechenden Werte zur Auswahl enthält (s. Bild 5 aussehen.

Bild 4: Datenblattansicht der Tabelle tblZahlweisen

Bild 5: Eigenschaften des Nachschlagefeldes ZahlweiseID

Das Feld VertragsartID soll ebenfalls als Nachschlagefeld eingerichtet werden und die Datensätze der Tabelle tblVertragsarten anzeigen. Dies sieht später so aus wie in Bild 6.

Bild 6: Das Nachschlagefeld VertragsartID

Etwas umfangreicher und nicht mehr als einfache Lookup-Tabelle zu bezeichnen ist die Tabelle tblGesellschaften, welche die für das Feld GesellschaftID zur Verfügung stehenden Einträge anbietet. Diese Tabelle enthält aber nicht nur die im Nachschlagefeld angezeigten Datensätze, sondern auch noch weitere Felder, die Sie in Bild 7 erkennen können.

Bild 7: Entwurf der Tabelle tblGesellschaften

Richtig kompliziert wird es dann beim Fremdschlüsselfeld BankverbindungID. Diese dient zwar letztlich auch wieder nur zur Auswahl von Datensätzen einer verknüpften Tabelle namens tblBankverbindungen, allerdings handelt es sich bei der verknüpften Tabelle um eine Hilfe zur Herstellung einer Beziehung zwischen den Tabellen tblVertraege und tblGeldinstitute – und dabei wird dann auch noch das Fremdschlüsselfeld KundeID einbezogen.

Um dies nachzuvollziehen, werfen wir einen Blick auf die Übersicht des kompletten Datenmodells in Bild 8.

Bild 8: Übersicht über das Datenmodell

Dort wird deutlich, dass die Tabelle tblBankverbindungen zunächst einmal die Tabellen tblKunden und tblGeldinstitute zusammenführt. Eigentlich könnte man dem Kunden auch einfach ein Feld namens BLZ und eines namens Kontonummer zuweisen, aber die Anwendung soll für jeden Kunden mehrere Bankverbindungen speichern können – was ja auch realistisch ist, denn Hausbesitzer mögen beispielsweise alle Verträge bezüglich des Hauses von einem eigenen Konto aus bezahlen und andere, wie etwa eine Lebensversicherung, von einem anderen Konto aus.

Aber dies hätte sich realisieren lassen, indem wir einfach eine Tabelle mit den Feldern BLZ, Kontonummer und KundeID erstellen, der wir dann pro Kunde mehrere Bankverbindungen zuweisen können. Für die Kontonummer ist dies in Ordnung, denn die ist wohl für jede Bankverbindung eindeutig, aber die BLZ kann durchaus bei mehreren Bankverbindungen verschiedener Kunden gleich sein. Also legen wir eine eigene Tabelle an, die tblGeldinstitute heißt und neben dem Namen des Geldinstituts auch noch die Bankleitzahl enthält. Statt eines Feldes BLZ fügen wir der Tabelle tblBankverbindungen also ein Fremdschlüsselfeld namens GeldinstitutID zur Auswahl des Geldinstituts hinzu.

So können Sie also nun die Bankverbindungen für einen Kunden definieren. Um diese Bankverbindungen nun auch den Verträgen zuweisen zu können, damit die Beiträge auch vom richtigen Konto abgebucht werden, wenn ein Kunde mehrere Bankverbindungen angegeben hat, erhält die Tabelle tblVertraege auch noch ein Fremdschlüsselfeld, mit dem die Bankverbindung ausgewählt werden kann. Später, beim Entwurf des Formulars zur Eingabe dieser Daten, zeigt dieses Feld natürlich nur die Bankverbindungen und Geldinstitute zum aktuellen Kunden an.

Beschriftungen für die Tabellenfelder einstellen

Die meisten Tabellenfelder werden auf die eine oder andere Art in einem Formular in der Formular- oder Datenblattansicht landen. Dabei ziehen Sie die Felder am einfachsten direkt aus der Feldliste in den Formularentwurf. Access legt dann automatisch Bezeichnungsfelder an, die sich an den Feldnamen orientieren. Bei Feldnamen wie Strasse der EMail ist hier in der Regel Nacharbeit erforderlich, um die Bezeichnungsfelder mit Texten wie Straße oder E-Mail auszustatten. Um diese Arbeit kommen Sie nicht herum, aber Sie können diese zumindest minimieren – nämlich durch Eintragen entsprechender Texte für die Eigenschaft Beschriftung der Felder im Tabellenentwurf. Wenn nämlich Beschriftungen vorhanden sind, übernimmt Access diese statt der Feldnamen als Text für die Bezeichnungsfelder.

In den folgenden Abschnitten schauen wir uns nun die Formulare der Anwendung an.

Die Kundenübersicht

Das Formular frmKundenuebersicht soll gleich beim Start der Anwendung geöffnet werden und eine Übersichtsliste der Kunden anzeigen. Dabei soll es die Möglichkeit bieten, die Details zu einem Kunden anzuzeigen, einen Kunden zu löschen oder einen neuen Kunden anzulegen.

Die Anzeige der Kundenliste soll in einem Unterformular in der Datenblattansicht erfolgen, da dieses gegenüber einem Listenfeld ausreichend Komfort zur Anpassung von Spaltenbreiten und -anordnung oder auch zur Sortierung der Datensätze bietet. Deshalb legen Sie zuerst ein entsprechendes Unterformular namens sfmKundenuebersicht an. Dieses verwendet die Tabelle tblKunden als Datenherkunft und zeigt all ihre Felder an (s. Bild 9).

Bild 9: Entwurf des Unterformulars sfmKundenuebersicht

Dieses Formular fügen wir nach dem Speichern und Schließen gleich nach dem Anlegen in ein weiteres neues, leeres Formular ein – und zwar wie in Bild 10 in den Detailbereich. Dies erledigen Sie, indem Sie das Unterformular sfmKundenuebersicht aus dem Datenbankfenster beziehungsweise Navigationsbereich in den Formularentwurf ziehen und dann seine Größe anpassen. Unter Access 2007 und jünger stellen Sie außerdem die Eigenschaften Horizontaler Anker und Vertikaler Anker jeweils auf den Wert Beide ein.

Bild 10: Haupt- und Unterformular

Dieses Formular speichern Sie unter dem Namen frmKundenuebersicht. Fügen Sie dem Kopfbereich ein geeignetes Bild und eine Beschriftung hinzu. Legen Sie dort außerdem eine Schaltfläche namens cmdSuche an. Im Formularfuß landen weitere Schaltflächen namens cmdOK, cmdNeu, cmdBearbeiten und cmdLoeschen – die Beschriftungen können Sie der Abbildung entnehmen.

Die Schaltfläche cmdOK soll schlicht das Formular schließen. Dies erreichen Sie durch Anlegen der folgenden Ereignisprozedur:

Private Sub cmdOK_Click()
    DoCmd.Close acForm, Me.Name
End Sub

Zum Anlegen der Prozedur markieren Sie die Schaltfläche cmdOK in der Entwurfsansicht, aktivieren mit F4 das Eigenschaftsfenster, wählen für die Eigenschaft Beim Klicken den Wert [Ereignisprozedur] aus und klicken dann auf die Schaltfläche mit den drei Punkten. Die nun erscheinende Prozedur im VBA-Editor ergänzen Sie wie oben angegeben.

tblKunden und tblKundenBase

Nun kümmern wir uns um die weiteren Schaltflächen, die ja zum Beispiel dem Löschen eines der Kundendatensätze dienen. Wollen wir einen Kunden aber wirklich gleich komplett löschen Und damit auch alle Daten, die mit diesem verknüpft sind Nein, das soll nicht geschehen. Zumal Sie ja Ihre Kommunikation dokumentiert wissen wollen. Wie also löscht man einen Kunden, der nicht mehr aktiv ist Man könnte diesen archivieren und diesen anschließend löschen. Einfacher ist es aber, den Kunden nur als gelöscht zu markieren. Und alle Kunden, die so markiert sind, nicht mehr in den Formularen der Anwendung anzuzeigen. Also kommt nun das Feld GeloeschtAm zum Einsatz, das ja bereits in der Abbildung weiter oben zu sehen war.

Wenn Sie sich nun vorstellen, dass die Anwendung in der Entwicklung schon weiter fortgeschritten ist als es im Rahmen dieses Beitrags beschrieben wurde, müssten Sie alle Stellen, an denen die Anwendung auf die Tabelle tblKunden zugreift, so ändern, dass nur noch die Datensätze angezeigt werden, deren Feld GeloeschtAm den Wert Null hat.

Nun unterscheidet Access an keiner Stelle, die sich auf Datenherkünfte oder Datensatzherkünfte bezieht, zwischen Tabellen oder Abfragen. Es spricht also nichts dagegen, einfach die Tabelle tbKunden in tblKundenBase umzubenennen und eine Abfrage namens tblKunden zu erstellen, die nur noch die Datensätze anzeigt, die noch nicht als gelöscht markiert wurden. Diese Abfrage sieht wie in Bild 11 aus.

Bild 11: Die Abfrage tblKunden liefert alle Kunden, die nicht als gelöscht markiert wurden.

Schaltflächen aktivieren und deaktivieren

Abhängig davon, ob das Unterformular überhaupt einen Datensatz anzeigt, sollen die Schaltflächen mit den Beschriftungen Bearbeiten und Löschen aktiviert oder deaktiviert werden.

Dazu stellen wir eine kleine Funktion namens SchaltflaechenAktivieren bereit, die prüft, ob das Unterformular aktuell Datensätze enthält, und die Schaltflächen cmdBearbeiten und cmdLoeschen entsprechend aktiviert oder deaktiviert:

Private Sub SchaltflaechenAktivieren()
    Dim bolNotNull As Boolean
    bolNotNull = Not (objSfmKundenuebersicht.Recordset.RecordCount = 0)
    Me!cmdBearbeiten.Enabled = bolNotNull
    Me!cmdLoeschen.Enabled = bolNotNull
End Sub

Was aber ist objSfmKundenuebersicht – wir wollen doch das Unterformular sfmKundenuebersicht abfragen, das mit Me!sfmKundenuebersicht.Form referenziert werden kann Nun, wir haben doch einiges mehr mit diesem Unterformular vor und deshalb speichern wir eine Referenz auf dieses Unterformular in einer eigenen Objektvariable, die im Kopf der Klasse des Formulars wie folgt deklariert wird:

Dim WithEvents objSfmKundenuebersicht As Form

Und wo belegen wir dieses Objekt mit dem entsprechenden Verweis auf das im Unterformular-Steuerelement enthaltene Formular In der Prozedur, die durch das Ereignis Beim Laden des Hauptformulars ausgelöst wird (s. Listing 1).

Listing 1: Ereignis beim Laden des Formulars frmKundenuebersicht

Private Sub Form_Load()
    Set objSfmKundenuebersicht = Me!sfmKundenuebersicht.Form
    With objSfmKundenuebersicht
        .OnCurrent = "[Event Procedure]"
    End With
    Set objColumnWidths = New clsColumnWidths
    With objColumnWidths
        Set .DataSheetForm = objSfmKundenuebersicht
        .OptimizeColumnWidths
    End With
    Set objDatasheetSelector = New clsDatasheetSelector
    Set objDatasheetSelector.DataSheetForm = objSfmKundenuebersicht
    SchaltflaechenAktivieren
End Sub

Und dort geschieht noch eine Menge mehr. Zum Beispiel stellen wir die Eigenschaft OnCurrent für die Variable objSfmKundenuebersicht auf den Wert [Ereignisprozedur] ein. Dies sorgt dafür, dass wir im gleichen Klassenmodul eine Ereignisprozedur anlegen können, die ausgelöst wird, wenn das referenzierte Unterformular einen neuen Datensatz anzeigt – und das geschieht auch beim Datensatzwechsel. Alles, was wir tun müssen, damit wir diese Ereignisprozedur im Klassenmodul des Hauptformulars statt im Klassenmodul des Unterformulars anlegen können, sind vier Schritte:

• Deklarieren einer Objektvariablen auf das Unterformular mit dem Schlüsselwort With-Events (bereits erledigt),
• Festlegen der Eigenschaft OnCurrent mit dem Wert "[Event Procedure]" (auch schon erledigt),
• Einstellen der Eigenschaft Hat Modul des Unterformulars auf den Wert Ja und
• Anlegen der entsprechenden Ereignisprozedur im Klassenmodul des Hauptformulars.

Diese Ereignisprozedur sieht wie folgt aus:

Private Sub objsfmKundenuebersicht_Current()
    SchaltflaechenAktivieren
End Sub

Dies sorgt dafür, dass die Prozedur SchaltflaechenAktivieren bei jedem Datensatzwechsel im Unterformular ausgelöst wird und die beiden dadurch aktivierten oder deaktivierten Schaltflächen immer den richtigen Status haben. Dies gilt auch, wenn der letzte Datensatz im Unterformular gelöscht wird – in diesem Fall werden die beiden Schaltflächen dann deaktiviert.

Spaltenbreite anpassen

Die Prozedur Form_Load erledigt aber noch mehr. Zum Beispiel sorgt sie dafür, dass die Spaltenbreiten entsprechend des Inhalts der einzelnen Spalten optimiert werden.

Die wesentliche Funktionalität hierzu befindet sich in einem Klassenmodul namens clsColumnWidths, das Sie im Kopf des Moduls Form_frmKundendetails wie folgt deklarieren (sollten Sie diese Klasse aus der Beispieldatenbank in eine andere Datenbank übertragen können, öffnen Sie einfach den VBA-Editor beider Anwendungen und ziehen Sie die Klasse von einem VBA-Projekt zum anderen):

Dim objColumnWidths As clsColumnWidths

In der Prozedur Form_Load sorgen diese Zeilen dafür, dass die Klasse instanziert und die Spaltenbreiten optimiert werden:

Set objColumnWidths = New clsColumnWidths
With objColumnWidths
    Set .DataSheetForm = objSfmKundenuebersicht
    .OptimizeColumnWidths
End With

Die erste Anweisung instanziert ein neues Objekt auf Basis der Klasse, die übrigen weisen der Klasse das zu optimierende Formular zu und führen schließlich mit OptimizeColumnWidth die Optimierung durch (mehr zu dieser Klasse erfahren sie im Beitrag Datenblatt-Spaltenbreiten automatisch anpassen, www.access-im-unternehmen.de/752).

Zeile bei Klick komplett markieren

Weitere zwei Zeilen sorgen dafür, dass der Benutzer die Daten nicht direkt im Datenblatt bearbeiten kann. Dazu wird beim Anklicken eines Datensatzes direkt der komplette Datensatz markiert. Die dafür nötige Klasse deklarieren Sie im Modulkopf:

Dim WithEvents objDatasheetSelector As clsDatasheetSelector

Diese Klasse wird wieder mit dem Schlüsselwort WithEvents deklariert. Das bedeutet, dass es wieder die Möglichkeit gibt, auf Ereignisse dieser Klasse zu reagieren – dazu gleich mehr. Zunächst die beiden Zeilen, mit denen Sie die Klasse einrichten und einen Verweis auf das betroffene Unterformular übergeben:

Set objDatasheetSelector = New clsDatasheetSelector
Set objDatasheetSelector.DataSheetForm = objSfmKundenuebersicht

Nun zum Ereignis, das Sie für dieses Objekt implementieren können. Es sieht so aus und wird durch einen Doppelklick auf einen der Datensätze ausgelöst:

Private Sub objDatasheetSelector_DblClick()
    KundeBearbeiten objSfmKundenuebersicht!txtKundeID
End Sub

Die einzige Anweisung ruft die Prozedur KundeBearbeiten auf. Dabei übergibt sie als Parameter den aktuellen Wert im Steuerelement txtKunde-ID des Unterformulars – hierzu später mehr.

Damit die Datensätze nicht nur nicht bearbeitet werden können, sondern auch noch kein neuer Datensatz angezeigt wird, stellen Sie die drei Eigenschaften Anfügen zulassen, Löschen zulassen und Bearbeitungen zulassen jeweils auf den Wert Nein ein.

Schaltflächen aktivieren

Schließlich wird beim Laden des Formulars noch die Prozedur SchaltflaechenAktivieren ausgelöst, damit die Schaltflächen gleich beim Öffnen den richtigen Status aufweisen. Man könnte auch davon ausgehen, dass es ausreicht, wenn dies beim Auswählen eines Datensatzes im Unterformular geschieht – schließlich ist das auch beim Öffnen des Formulars der Fall. Allerdings löst das Unterformular das Ereignis Beim Anzeigen bereits zum ersten Mal aus, bevor das Ereignis Beim Laden im Hauptformular feuert.

Das heißt, dass die im Klassenmodul des Hauptformulars implementierte Ereignisprozedur, die durch den Datensatzwechsel im Unterformular ausgelöst wird, beim Anzeigen des ersten Datensatzes nach dem Öffnen des Unterformulars noch gar nicht zum Einsatz kommt. Also müssen wir die Prozedur SchaltflaechenAktivieren im Ereignis Beim Laden des Hauptformulars einmal auf andere Art und Weise auslösen.

Löschen eines Kundendatensatzes

Einen Kundendatensatz löscht der Benutzer durch einen Mausklick auf die Schaltfläche cmdLoeschen. Dies löst die Prozedur aus Listing 2 aus. Wie oben besprochen, sollen Datensätze der Tabelle tblKundenBase aber gar nicht gelöscht, sondern nur als gelöscht markiert werden.

Listing 2: Löschen eines Kundendatensatzes per Schaltfläche

Private Sub cmdLoeschen_Click()
    Dim db As DAO.Database
    Dim lngKundeID As Long
    Dim strSQL As String
    lngKundeID = objSfmKundenuebersicht!KundeID
    Set db = CurrentDb
    strSQL = "UPDATE tblKundenBase SET GeloeschtAm = " & ISODatum(Now) & " WHERE KundeID = " _
        & lngKundeID
    db.Execute strSQL, dbFailOnError
    objSfmKundenuebersicht.Requery
    SchaltflaechenAktivieren
    Set db = Nothing
End Sub

Username or Email

Passwort

Angemeldet bleiben