Vereinsverwaltung: Mitglieder, Bankverbindungen und Mandate

Eine IBAN mit falscher Prüfziffer fällt meist erst auf, wenn die Bank die Lastschriftdatei zurückweist. Dann ist der Einzug bereits verzögert. Wir prüfen die IBAN deshalb schon bei der Eingabe, bilden die Mandatsreferenz per Funktion und leiten den Folgetyp aus den bisherigen Lastschriften ab. Wie das in einem Formular für Mitglieder, Bankverbindungen und Mandate gelingt, zeigt der vorliegende Beitrag.

Das Formular im Überblick

Die Beispieldatenbank VereinsverwaltungMitgliederUndMandate.accdb baut auf der Datenbank aus dem Beitrag Vereinsverwaltung: Datenmodell (www.access-im-unternehmen.de/1629) auf.

Kopieren Sie diese Datenbank und nennen Sie die Kopie VereinsverwaltungMitgliederUndMandate.accdb.

Das Hauptformular frmMitglieder zeigt eine Person mit ihren Stammdaten. Darunter stehen zwei Unterformulare, die wir mit dem Präfix sfm kennzeichnen: sfmMitgliedschaften zeigt die Mitgliedschaften der Person, sfmBankverbindungen ihre Bankverbindungen.

Das Unterformular sfmBankverbindungen enthält wiederum das Unterformular sfmMandate mit den Mandaten zur Bankverbindung (siehe Bild 1).

Bild 1: Das Formular frmMitglieder

Wir bauen die Formulare von innen nach außen auf.

Zuerst legen wir die Funktionen und Hilfsprozeduren an, die wir im Beitrag verwenden. Danach erstellen wir die Unterformulare sfmMandate, sfmMitgliedschaften und sfmBankverbindungen und zuletzt das Hauptformular frmMitglieder.

Die Funktionen vorbereiten

Die Formulare rufen Funktionen auf, die in Standardmodulen enthalten sind. Die Prüfung der IBAN übernimmt die Funktion IstIban.

Sie stammt aus dem Beitrag Prüfziffern per VBA berechnen und prüfen (www.access-im-unternehmen.de/1618). Wählen Sie Externe Daten|Neue Datenquelle|Aus Datenbank|Access und importieren Sie aus der Beispieldatenbank dieses Beitrags die Module mdlPruefziffern und mdlRegEx in Ihre Datenbank.

Die Funktion IstIban akzeptiert die IBAN mit und ohne Leerzeichen.

Die weiteren Funktionen legen Sie in einem neuen Standardmodul an, das Sie mdlMandate nennen. Die erste Funktion, NeueMandatsreferenz, bildet die Mandatsreferenz wie in den Beispieldaten aus dem Buchstaben M, dem Jahr der Unterschrift, einem Bindestrich (–) und der vierstellig aufgefüllten PersonID (siehe Listing 1).

Public Function NeueMandatsreferenz(ByVal lngPersonID As Long, ByVal datUnterschrift As Date) As String
    ' Bildet die Referenz aus Jahr und PersonID, bei einer Kollision mit Zaehler
    Dim strBasis As String
    Dim strReferenz As String
    Dim lngZaehler As Long
    strBasis = "M" & Year(datUnterschrift) & "-" & Format(lngPersonID, "0000")
    strReferenz = strBasis
    Do While DCount("*", "tblMandate", "Mandatsreferenz='" & strReferenz & "'") > 0
        lngZaehler = lngZaehler + 1
        strReferenz = strBasis & "-" & lngZaehler
    Loop
    NeueMandatsreferenz = strReferenz
End Function

Listing 1: Eine eindeutige Mandatsreferenz bilden

Die Funktion prüft mit DCount, ob die Referenz bereits in der Tabelle tblMandate steht.

Falls ja, hängt sie einen Zähler an und prüft erneut. Die Referenz besteht nur aus Jahr und PersonID.

Legt eine Person im selben Jahr ein zweites Mandat an, würde der eindeutige Index idxMandatsreferenz den doppelten Wert mit einem Fehler zurückweisen.

Die Zählung vermeidet diesen Fehler. Im Direktbereich des VBA-Editors sehen wir die Referenzen für zwei Unterschriftsdaten.

Die Datumsangaben stehen dort im amerikanischen Format, #3/5/2027# ist der 5. März 2027:

Debug.Print NeueMandatsreferenz(1, #3/5/2027#)
M2027-0001
Debug.Print NeueMandatsreferenz(1, #10/1/2019#)
M2019-0001-1

In den Beispieldaten hat die Person mit der PersonID 1 bereits die Referenz M2019-0001. Im Jahr 2027 ist M2027-0001 frei.

Im Jahr 2019 ergänzt die Funktion deshalb den Zähler 1.

Die zweite Funktion, Folgetyp, leitet den Folgetyp eines Mandats aus den bisherigen Lastschriften ab (siehe Listing 2).

Public Function Folgetyp(ByVal lngMandatID As Long) As String
    ' Leitet den Folgetyp aus den eingereichten Laeufen ab
    Dim strBedingung As String
    If DLookup("Mandatsart", "tblMandate", "MandatID=" & lngMandatID) = "Einmalig" Then
        Folgetyp = "OOFF"
        Exit Function
    End If
    strBedingung = "MandatID=" & lngMandatID & " AND LaufID IN " & _
        "(SELECT LaufID FROM tblLastschriftlaeufe WHERE Status='Eingereicht')"
    If DCount("*", "tblLastschriftpositionen", strBedingung) = 0 Then
        Folgetyp = "FRST"
    Else
        Folgetyp = "RCUR"
    End If
End Function

Listing 2: Den Folgetyp eines Mandats ableiten

Wir speichern ihn nicht, sondern berechnen ihn bei Bedarf. Er kennzeichnet in der Lastschriftdatei die erste (FRST), eine wiederkehrende (RCUR) oder eine einmalige Lastschrift (OOFF).

Hat das Mandat die Mandatsart Einmalig, liefert die Funktion OOFF. Andernfalls zählt sie die Positionen in der Tabelle tblLastschriftpositionen, die zu diesem Mandat gehören und deren Lastschriftlauf (Tabelle tblLastschriftlaeufe) den Status Eingereicht hat.

Gibt es keine, liefert sie FRST. Gibt es mindestens eine, liefert sie RCUR.

Ein Lastschriftlauf mit dem Status Angelegt zählt nicht, denn die Bank hat die Lastschriften noch nicht erhalten.

Die Beispieldatenbank enthält noch keinen Lauf. Die Prozedur BeispiellaufAnlegen erzeugt einen Lastschriftlauf mit dem Status Eingereicht und fünf Positionen für die ersten fünf Mandate.

Sie steht zusammen mit der Prozedur BeispiellaufLoeschen im Modul mdlMandate der Beispieldatenbank. Wir vergleichen den Folgetyp vorher und nachher im Direktbereich:

Debug.Print Folgetyp(1)
FRST
BeispiellaufAnlegen
Debug.Print Folgetyp(1)
RCUR
Debug.Print Folgetyp(6)
FRST

Das Mandat mit der MandatID 1 hat nach dem Lauf den Folgetyp RCUR. Das Mandat mit der MandatID 6 gehört nicht zum Lauf, daher bleibt sein Folgetyp FRST.

Rufen Sie danach die Prozedur BeispiellaufLoeschen auf, damit die Beispieldaten wieder dem Ausgangszustand entsprechen.

Die dritte Funktion, HatGueltigesMandat, ermittelt, ob eine Person für den Einzug infrage kommt (siehe Listing 3).

Public Function HatGueltigesMandat(ByVal lngPersonID As Long) As Boolean
    ' Liefert, ob die Person mindestens ein aktives Mandat besitzt
    HatGueltigesMandat = (DCount("*", "qryMandateUebersicht", "PersonID=" & lngPersonID & " AND Status='Aktiv'") > 0)
End Function

Listing 3: Prüfen, ob eine Person ein aktives Mandat hat

Die Formulare in diesem Beitrag benötigt diese Funktion nicht. Sie dienen später dazu, die Personen für die Lastschriftdatei auszuwählen.

Diesen Schritt behandeln wir in einem Folgebeitrag.

Die Funktion zählt in der Abfrage qryMandateUebersicht die Mandate der Person mit dem Status Aktiv. In den Beispieldaten liefert sie für die Person mit der PersonID 1 den Wert Wahr.

Das Unterformular sfmMandate

Das Unterformular sfmMandate zeigt die Mandate einer Bankverbindung in einer Liste mit einer Zeile je Mandat (siehe Bild 2).

Bild 2: Das Unterformular sfmMandate in der Entwurfsansicht

Wir bauen es wie folgt Schritt für Schritt auf:

• Wählen Sie Erstellen|Formularentwurf. Blenden Sie bei Bedarf mit Formularentwurf|Eigenschaftenblatt das Eigenschaftenblatt ein.
• Markieren Sie im Eigenschaftenblatt oben im Auswahlfeld den Eintrag Formular. Stellen Sie auf der Registerkarte Daten die Eigenschaft Datensatzquelle auf tblMandate ein. Auf der Registerkarte Format wählen Sie bei Standardansicht den Eintrag Endlosformular. Die Eigenschaften Navigationsschaltflächen und Datensatzmarkierer setzen Sie auf Nein, die Eigenschaft Bildlaufleisten auf Nur vertikal.
• Blenden Sie über das Kontextmenü des Formularentwurfs den Formularkopf/-fuß ein. Öffnen Sie mit Formularentwurf|Vorhandene Felder hinzufügen die Feldliste und ziehen Sie die Felder Mandatsreferenz, Unterschriftsdatum, Mandatsart und Status in den Detailbereich, nebeneinander in einer Zeile. Ordnen Sie die Textfelder nebeneinander an und geben Sie ihnen passende Breiten.
• Markieren Sie die vier Bezeichnungsfelder, schneiden Sie sie mit Strg + X aus, klicken Sie in den Formularkopf und fügen Sie sie mit Strg + V ein. Die Bezeichnungsfelder dienen jetzt als Spaltenüberschriften. Richten Sie die Bezeichnungsfelder im Formularkopf über den zugehörigen Textfeldern aus. Ändern Sie die Beschriftung des Bezeichnungsfeldes Unterschriftsdatum in Unterschrift und stellen Sie bei allen Bezeichnungsfeldern die Eigenschaft Schriftschnitt auf Fett ein.
• Benennen Sie die Textfelder über die Eigenschaft Name auf der Registerkarte Andere: txtMandatsreferenz und txtUnterschriftsdatum. Das Textfeld txtMandatsreferenz erhält die Eigenschaft Gesperrt mit dem Wert Ja, denn der Benutzer soll die Referenz nicht ändern. Das Textfeld txtUnterschriftsdatum erhält bei Format den Wert Datum, kurz und bei Standardwert den Ausdruck =Datum().
• Wandeln Sie die Textfelder Mandatsart und Status über das Kontextmenü mit Ändern in|Kombinationsfeld in Kombinationsfelder um und benennen Sie sie cboMandatsart und cboStatus. Stellen Sie bei beiden die Eigenschaft Herkunftsart auf Wertliste ein. Die Eigenschaft Datensatzherkunft lautet bei cboMandatsart Wiederkehrend;Einmalig und bei cboStatus Aktiv;Gesperrt;Widerrufen. Die Einträge trennen Sie mit dem Semikolon (;).
• Fügen Sie rechts daneben ein ungebundenes Textfeld ein und benennen Sie es txtFolgetyp. Schneiden Sie das zugehörige Bezeichnungsfeld aus, fügen Sie es in den Formularkopf ein und ändern Sie die Beschriftung in Folgetyp. Setzen Sie Gesperrt auf Ja und tragen Sie bei Steuerelementinhalt den folgenden Ausdruck ein. In der deutschen Oberfläche geben Sie dabei die Funktionsnamen auf Deutsch ein:

=Wenn(IstNull([MandatID]);Null;Folgetyp([MandatID]))

• Fügen Sie rechts davon eine Schaltfläche ein, ohne den Assistenten zu verwenden. Benennen Sie sie btnSperren und geben Sie ihr die Beschriftung Sperren.

Der Ausdruck für das Textfeld txtFolgetyp ruft die Funktion Folgetyp aus mdlMandate auf. Die Fallunterscheidung mit Wenn ist nötig, weil die Zeile für ein neues Mandat noch keine MandatID hat. Ohne sie zeigt das Textfeld in dieser Zeile den Fehlerwert #Typ!.

Jetzt ergänzen wir den Code. Wählen Sie im Eigenschaftenblatt für das Formular auf der Registerkarte Ereignis bei Vor Aktualisierung den Eintrag [Ereignisprozedur] und klicken Sie auf die Schaltfläche mit den drei Punkten.

Access öffnet den VBA-Editor mit dem Rahmen der Prozedur. Vervollständigen Sie die Prozedur wie in Listing 4.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Neue Mandate erhalten automatisch eine Referenz
    If IsNull(Me.Mandatsreferenz) Then
        Me.Mandatsreferenz = NeueMandatsreferenz( _
            DLookup("PersonID", "tblBankverbindungen", _
            "BankverbindungID=" & Me.BankverbindungID), Nz(Me.Unterschriftsdatum, Date))
    End If
End Sub

Listing 4: Neuen Mandaten die Referenz zuweisen

Die Prozedur läuft vor dem Speichern eines Datensatzes.

Ist das Feld Mandatsreferenz leer, ermittelt sie mit DLookup die PersonID der Bankverbindung und übergibt sie mit dem Datum der Unterschrift an die Funktion NeueMandatsreferenz.

Das Feld BankverbindungID füllt Access über die Verknüpfung zum übergeordneten Formular sfmBankverbindungen aus. Für die Schaltfläche wählen Sie im Eigenschaftenblatt bei Beim Klicken ebenfalls [Ereignisprozedur]. Die Prozedur setzt den Status des aktuellen Mandats auf Gesperrt und speichert den Datensatz:

Private Sub btnSperren_Click()
    ' Sperrt das aktuelle Mandat
    Me.Status = "Gesperrt"
    Me.Dirty = False
    Me.cboStatus.Requery
End Sub

Speichern Sie das Formular unter dem Namen sfmMandate.

Das Unterformular sfmMitgliedschaften

Das Unterformular sfmMitgliedschaften zeigt die Mitgliedschaften einer Person (siehe Bild 3). Es entsteht nach demselben Muster:

Bild 3: Das Unterformular sfmMitgliedschaften