Benutzerdefinierte Standardwerte

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Standardwerte für Felder legt in der Regel der Entwickler beim Definieren des Datenmodells in der Entwurfsansicht der Tabellen fest. Während man in bestimmten Maßen dynamische Werte nutzen kann wie beispielsweise mit der Datum()-Funktion für Datumsfelder, ist man ansonsten recht unflexibel. Leider können nur einige eingebaute Funktionen genutzt werden, benutzerdefinierte VBA-Funktionen sind nicht möglich. Aber welche benutzerdefinierten Standardwerte wollen wir überhaupt nutzen – und wo wollen wir diese festlegen? Die Einsatzzwecke sind vielfältig und prinzipiell können wir jedes Feld mit einem benutzerdefinierten Standardwert nutzen. Wichtig ist der Gedanke dahinter: Sinnvoll gewählte Standardwerte sparen dem Benutzer wertvolle Zeit bei der Dateneingabe. Wenn wir zum Beispiel eine Datenbank planen, die überwiegend weibliche Kontakte verwaltet, ist es sinnvoll, als Anrede „Frau“ vorzugeben. Oder wir haben eine Autowerkstatt, die hauptsächlich Fahrzeuge einer bestimmten Marke repariert – dann macht es Sinn, diese vorzubelegen. Wir können auch dynamisch den zuletzt verwendeten Eintrag als Standardwert angeben oder auch den meistgenutzen. Wie wir dies realisieren, zeigen wir im vorliegenden Beitrag.

Herkömmliche Standardwerte

Das Festlegen von Standardwerten ist recht einfach – man wechselt in den Entwurf der betroffenen Tabelle, klickt das Feld an, dem man einen Standardwert zuweisen will und stellt die Eigenschaft Standardwert auf den gewünschten Wert ein.

Bei Zahlenfeldern legt man so vielleicht den Wert 0 fest, bei Datumsfeldern mit dem Ausdruck =Datum() das aktuelle Datum oder bei Ja/Nein-Feldern den Wert Ja. Das ist schnell gemacht, aber relativ unflexibel – es gibt ein paar eingebaute Funktionen, die man hier nutzen kann, aber ansonsten ist man auf statische Werte angewiesen.

Dynamische Standardwerte

Wollen wir dies flexibel erledigen, haben wir im Tabellen- und Abfrageentwurf keine Möglichkeiten. Die nächste Ebene sind die Formulare: Hier können wir für Steuerelemente, die an Tabellenfelder gebunden sind, per VBA einen Standardwert definieren. Aber welche Werte wollen wir überhaupt als Standardwerte nutzen? Es gibt zum Beispiel die folgenden Möglichkeiten:

• Den beim zuletzt angelegten oder geänderten Datensatz verwendeten Wert,
• den Wert, der am meisten verwendet wurde oder
• Werte, die durch den Benutzer als Standardwert vordefiniert wurden.

Die ersten beiden Vorschläge erfordern keine aktive Mitarbeit des Benutzers, der letzte hingegen schon.

Zuletzt verwendeten Wert als Standardwert nutzen

Wenn wir wie in Bild 1 den Namen Andreas in das Textfeld txtVorname eingeben und wollen, dass dieser fortan als Standardwert verwendet wird, benötigen wir eine VBA-Prozedur, die entweder nach dem Aktualisieren des Steuerelements oder des Formulars ausgelöst wird und die Eigenschaft DefaultValue des Textfeldes auf den soeben eingegebenen Wert einstellt.

Bild 1: Dieser Wert wurde soeben als Standardwert übernommen

In diesem Fall wollen wir dies direkt nach dem Eingeben des Wertes und dem Aktualisieren des Steuerelements erledigen. Dazu hinterlegen wir die folgende Ereignisprozedur:

Private Sub txtVorname_AfterUpdate()
     Me!txtVorname.DefaultValue = _
         Chr(34) & Me!txtVorname & Chr(34)
End Sub

Wir weisen hier allerdings nicht einfach den Wert des Textfeldes zu, sondern müssen diesen noch in Anführungszeichen einfassen, die wir wie hier durch die entsprechende Chr-Funktion angeben oder alternativ beispielsweise mit vier Anführungszeichen:

Me!txtVorname.DefaultValue = """" & Me!txtVorname & """"

Auch wenn wir eine feste Zeichenkette angeben wollen, müssen wir diese übrigens in zusätzliche Anführungszeichen einfassen:

Me!txtVorname.DefaultValue = """Beispiel"""

Standardwert für weitere Datentypen

Bei Datumsangaben müssen wir das Datum zuvor in einen Zahlenwert konvertieren. Bei reinen Datumsangaben ohne Uhrzeit reicht dies aus:

Private Sub txtGeburtsdatum_AfterUpdate()
     Me!txtGeburtsdatum.DefaultValue = CLng(Me!txtGeburtsdatum)
End Sub

Wenn wir ein Feld verwenden, das auch eine Uhrzeit enthält, benötigen wir die Funktion CDbl – die Nachkommastellen nehmen in diesem Fall die Uhrzeit auf:

Me!txtGeburtsdatum.DefaultValue = CDbl(Me!txtGeburtsdatum)

Bei Ja/Nein-Feldern greifen wir direkt auf den jeweiligen Wert zu:

Private Sub chkAktiv_AfterUpdate()
     Me!chkAktiv.DefaultValue = Me!chkAktiv
End Sub

Bei Zahlenwerten und Währungsfeldern müssen wir das Komma durch den Punkt ersetzen:

Private     Sub txtTaschengeld_AfterUpdate()
     Me!txtTaschengeld.DefaultValue = _
         Replace(Me!txtTaschengeld, ",", ".")
End Sub 
Private Sub txtGewicht_AfterUpdate()
     Me!txtGewicht.DefaultValue = _
         Replace(Me!txtGewicht, ",", ".")
End Sub

Nachteil: Kein Speichern des Standardwerts

Der Nachteil dieser Methode ist, dass der zuletzt verwendete Wert erst als Standardwert genutzt wird, wenn wir den Inhalt des Steuerelements nach dem Öffnen des Formulars erstmals bearbeitet haben.

Wir müssten uns also eine Möglichkeit überlegen, diesen Wert alternativ zu speichern und ihn wiederherzustellen. Dazu kommen wir weiter unten – erst schauen wir uns die anderen Varianten an.

Am meisten verwendeten Wert als Standardwert nutzen

Wenn wir schon eine VBA-Prozedur verwenden, können wir auch gleich noch andere Methoden zur Ermittlung des Standardwertes nutzen. So können wir beispielsweise ermitteln, welcher Wert in einem Feld am meisten verwendet wurde und diesen als Standardwert in das Feld eintragen.

Dazu verwenden wir die Prozedur aus Listing 1. Diese ermittelt in einem Recordset den Wert des Feldes Nachname, der den höchsten Wert für die Anzahl der Werte der Gruppierungen über das Feld Nachname enthält. Sprich: Die Abfrage gruppiert die Datensätze nach dem Wert im Feld Nachname, zählt diese und verwendet die Anzahl gleich noch als Sortierkriterium.

Private Sub NachnameStandardwert()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Dim strNachname As String
     Set db = CurrentDb
     Set rst = db.OpenRecordset("SELECT TOP 1 Nachname FROM tblPersonen GROUP BY Nachname ORDER BY COUNT(*) DESC")
     If Not rst.EOF Then
         strNachname = rst.Fields(0)
     End If
     Me!txtNachname.DefaultValue = """" & strNachname & """"
End Sub

Listing 1: Einstellen des häufigsten Nachnamens als Standardwert

Davon holt sie mit TOP 1 den ersten Eintrag. Danach prüft sie, ob die Abfrage einen Datensatz gefunden hat und stellt den Wert der Variablen strNachname auf den gefundenen Nachnamen ein. Dieser wird dann als Standardwert des Feldes txtNachname festgelegt.

Damit wir sowohl beim Öffnen des Formulars als auch nach dem Aktualisieren eines der Nachnamen jeweils den korrekten Standardwert für dieses Feld angeben können, rufen wir diese Prozedur gleich zwei Mal auf – zuerst in der Prozedur, die durch das Ereignis Beim Öffnen des Formulars ausgelöst wird:

Private Sub Form_Open(Cancel As Integer)
     Call NachnameStandardwert
End Sub

Zweitens nach dem Ändern des Wertes für einen Datensatz im Feld Nachname:

Private Sub txtNachname_AfterUpdate()
     Call NachnameStandardwert
End Sub

Standardwert beim Öffnen einstellen

Den Standardwert können wir also auch gleich beim Öffnen des Formulars zu setzen – und zwar in den Prozeduren für die Ereigniseigenschaften Beim Laden und Beim Öffnen.

Wert aus dem zuletzt hinzugefügten Datensatz ermitteln

Nun fehlt noch eine Möglichkeit, diese Standardwerte zu speichern, denn wie wir oben beschrieben haben, sind diese nach dem erneuten Öffnen des Formulars wieder geleert. Wir haben zwar gesehen, dass wir in den Ereignisprozeduren Form_Load oder Form_Open die Werte neu einstellen können. Hier können wir aber beispielsweise nicht den zuletzt verwendeten Wert einstellen, da dieser nicht gespeichert wird. Dies würde höchstes gelingen, wenn wir die Anforderung so definieren, dass der Wert des zuletzt angelegten Datensatzes als Standardwert verwendet wird. Dann würden wir beispielsweise diese Prozedur nutzen, die durch das Ereignis Beim Anzeigen ausgelöst wird – also beim Wechseln zu einem anderen Datensatz und somit auch zu einem neuen, leeren Datensatz:

Private Sub Form_Current()
     Dim strAnrede As String
     strAnrede = Nz(DLookup("Anrede", "tblPersonen", _
         "ID = " & Nz(DMax("ID", "tblPersonen"), 0)), "")
     Me!txtAnrede.DefaultValue = """" & strAnrede & """"
End Sub

Hier ermitteln wir mit DMax die höchste Zahl im Feld ID, was in der Regel dem zuletzt angelegten Datensatz entspricht. Diese verwenden wir als Parameter für einen Aufruf der DLookup-Funktion, um die passende Anrede dazu zu finden. Das Ergebnis schreiben wir anschließend als Standardwert in das Textfeld txtAnrede.

Standardwerte speichern

Damit kommen wir endgültig zum Speichern der Standardwerte. Wenn dies gewünscht ist, benötigen wir einen Ort, wo wir die Werte speichern können.

Da wir ohnehin in einer Access-Datenbank arbeiten, legen wir dazu logischerweise eine eigene Tabelle an, die wir beispielsweise tblStandardwerte nennen. Hier wollen wir zunächst die folgenden Informationen speichern:

• Tabellenname
• Feldname

Danach folgt der eigentlich interessante Wert, nämlich der Standardwert. Hier stellt sich die Frage, ob wir für jeden Datentyp ein eigenes Feld anlegen oder einfach alle Werte in ein Feld mit dem Datentyp speichern, der alles speichern kann – nämlich in einem Textfeld.

Darüber hinaus ließen sich je nach Einsatzzweck noch weitere Felder einfügen:

• BenutzerID: Für den Fall, dass mehrere Benutzer mit der Anwendung arbeiten und jeder seine eigenen Standardwerte speichern möchte.
• Geloescht: Ermöglicht es, Datensätze als gelöscht zu markieren, die man gegebenenfalls nochmal wiederherstellen möchte.
• Aktiv: Erlaubt das Deaktivieren von Datensätzen für Standardwerte.
• Gruppe: Gegebenenfalls möchte man die Standardwerte nach Gruppen sortiert darstellen – das würde diese Einstellung ermöglichen.
• Datentyp: Erlaubt es, je nach Datentyp gezielt auf den gespeicherten Wert zuzugreifen. Noch wichtiger: Wenn wir ein eigenes Formular zur Verwaltung der Standardwerte anbieten, sollten wir validieren können, ob der Wert gültig ist.

Mehrbenutzer

Bevor wir an die Umsetzung gehen, schauen wir uns die Möglichkeiten für Mehrbenutzer an. Hier gibt es zwei Varianten:

• Die Tabelle mit den Standardwerten ist im Backend gespeichert. In diesem Fall müssten wir ein Feld wie BenutzerID oder MitarbeiterID verwenden, um festlegen zu können, welchem Benutzer oder Mitarbeiter der Standardwert gehört.
• Die Tabelle mit den Standardwerten ist im Frontend gespeichert und jeder Benutzer hat sein Frontend in seinen Benutzerdokumenten, auf die er nach der Anmeldung zugreifen kann. Dann können wir auf ein Feld wie BenutzerID oder MitarbeiterID verzichten.

Wir gehen der Einfachheit halber davon aus, dass wir entweder eine Einzelbenutzer-Anwendung haben oder dass jeder Benutzer eine Kopie des Frontends mit der Tabelle tblStandardwerte in seinem Benutzerverzeichnis hat.

Tabellen zum Verwalten der Standardwerte

Die Benutzeroberfläche gestalten wir je nach den möglichen Elementen des Datenmodells, die wir oben vorgestellt haben. Um den Rahmen nicht zu sprengen, wollen wir die folgenden Daten darstellen:

• Tabellenname
• Feldname
• Standardwert
• Gruppe
• Datentyp

Die Tabelle tblStandardwerte sieht in der Entwurfsansicht schließlich wie in Bild 2 aus. Für die Kombination der beiden Felder Tabellenname und Feldname haben wir einen eindeutigen Schlüssel definiert, damit kein Feldname doppelt zu einer Tabelle zugeordnet werden kann.

Bild 2: Festlegen eines eindeutigen, zusammengesetzten Indexes

Für das Feld DatentypID haben wir ein Nachschlagefeld eingerichtet, das mit der Tabelle tblDatentypen verknüpft ist. Auf diese Weise brauchen wir dort nur die dem Datentyp entsprechende Zahl zu speichern. Die damit verknüpfte Tabelle heißt tblDatentypen und enthält im Feld ID den Zahlenwert, den wir anschließend per VBA für das jeweilige Feld ermitteln, im Feld Datentyp die VBA-Konstante für den Datentyp sowie eine Beschreibung (siehe Bild 3).

Bild 3: Datentypen für die Standardwerte

Entwurf der Benutzeroberfläche

Um die Standardwerte zu verwalten, erstellen wir zwei Formulare. Das Unterformular sfmStandardwerte soll die Daten der Tabelle tblStandardwerte in der Datenblattansicht anzeigen. Dazu weisen wir dem Formular diese Tabelle für die Eigenschaft Datensatzquelle zu und ziehen die drei Felder Tabellenname, Feldname und Standardwert in den Detailbereich des Entwurfs.

Anschließend speichern und schließen wir das Unterformular und ziehen es aus dem Navigationsbereich in den Entwurf des Hauptformulars frmStandardwerte. Hier fügen wir noch eine Schaltfläche hinzu, mit der wir die Felder der Tabellen der aktuellen Datenbank in die Tabelle tblStandardwerte einlesen können.

Außerdem wollen wir mit dem Kontrollkästchen chkStandardwerteUebernehmen festlegen, ob die Standardwerte aus den Felddefinitionen übernommen werden sollen, was nur beim ersten Einlesen empfehlenswert ist (siehe Bild 4).

Bild 4: Entwurf von Haupt- und Unterformular

Felder und Standardwerte einlesen

Die Schaltfläche cmdFelderEinlesen löst die folgende Prozedur aus. Diese ruft zwei weitere Routinen auf, von denen die erste die im Datenmodell vorhandenen Felder einliest und die zweite prüft, ob alle in der Tabelle tblStandardwerte angegebenen Felder noch vorhanden sind. Danach aktualisiert sie das Unterformular sfmStandardwerte mit der Requery-Methode:

Private Sub cmdFelderEinlesen_Click()
     Call FelderEinlesen(Me!chkStandardwerteUebernehmen)
     Call FelderEntfernen
     Me!sfmStandardwerte.Requery
End Sub

Die Prozedur FelderEinlesen nimmt den Wert des Kontrollkästchens chkStandardwerteUebernehmen als optionalen Parameter entgegen, der standardmäßig auf False eingestellt ist (siehe Listing 2).

Public Sub FelderEinlesen(Optional bolStandardwerteUebernehmen As Boolean = False)
     Dim db As DAO.Database
     Dim tdf As DAO.TableDef
     Dim fld As DAO.Field
     Set db = CurrentDb
     For Each tdf In db.TableDefs
         Select Case True
             Case tdf.Name Like "MSys*"
             Case tdf.Name Like "USys*"
             Case tdf.Name = "tblStandardwerte"
             Case tdf.Name = "tblDatentypen"
             Case Else
                 For Each fld In tdf.Fields
                     On Error Resume Next
                     If bolStandardwerteUebernehmen Then
                         db.Execute "INSERT INTO tblStandardwerte(Tabellenname, Feldname, Standardwert, DatentypID) " _
                             & "VALUES(''" & tdf.Name & "'', ''" & fld.Name & "'', ''" & fld.DefaultValue & "'', " _
                             & fld.Type & ")", dbFailOnError
                     Else
                         db.Execute "INSERT INTO tblStandardwerte(Tabellenname, Feldname, DatentypID) VALUES(''" _
                             & tdf.Name & "'', ''" & fld.Name & "'', " & fld.Type & ")", dbFailOnError
                     End If
                     Select Case Err.Number
                         Case 3022
                             If bolStandardwerteUebernehmen Then
                                 db.Execute "UPDATE tblStandardwerte(Tabellenname, Feldname, Standardwert, " _
                                     & "DatentypID) VALUES(''" & tdf.Name & "'', ''" & fld.Name & "'', ''" _
                                     & fld.DefaultValue & "'', " & fld.Type & ") WHERE Tabellenname = ''" & tdf.Name _
                                     & "'' AND Feldname = ''" & fld.Name & "''", dbFailOnError
                             Else
                                 db.Execute "UPDATE tblStandardwerte(Tabellenname, Feldname, DatentypID) VALUES(''" _
                                     & tdf.Name & "'', ''" & fld.Name & "'', " & fld.Type & ") WHERE Tabellenname = ''" _
                                     & tdf.Name & "'' AND Feldname = ''" & fld.Name & "''", dbFailOnError
                             End If
                         Case 0
                         Case Else
                             MsgBox "Fehler " & Err.Number & ":" & vbCrLf & vbCrLf & Err.Description
                     End Select
                 Next fld
         End Select
     Next tdf
End Sub

[

Listing 2: Speichern der Felder in der Tabelle tblStandardwerte

Sie durchläuft in einer For Each-Schleife alle Tabellendefinitionen der aktuellen Datenbank und prüft in einer Select Case-Bedingung, ob der Tabellenname bestimmten Namen wie MSys*, USys*, tblStandardwerte oder tblDatentypen entspricht. Diese Tabellen sollen beim Einlesen nicht berücksichtigt werden.

Für alle anderen durchläuft die Prozedur die Fields-Auflistung mit allen Feldern. Nach der Deaktivierung der eingebauten Fehlerbehandlung prüft die Prozedur, ob die Standardwerte übernommen werden sollen. Falls ja, versucht sie, für die aktuelle Kombination aus Tabellenname und Feldname einen neuen Datensatz inklusive Standardwert und Felddatentyp in der Tabelle tblStandardwerte anzulegen. Das könnte einen Fehler auslösen, wenn diese Kombination bereits vorhanden ist. In diesem Fall wird in der folgenden Select Case-Bedingung geprüft, ob die Fehlernummer 3022 lautet. Dieser Fehler wird immer ausgelöst, wenn man versucht, einen Datensatz anzulegen, dessen Kombination aus Schlüsselwerten bereits in einem anderen Datensatz vorliegt. In diesem Fall wollen wir die Werte für diesen Datensatz überschreiben, was wir mit einer UPDATE-SQL-Anweisung erledigen, die genau die Daten für den Datensatz mit dem Tabellennamen und dem Feldnamen aktualisiert. Die Aktualisierung bezieht sich auf den Felddatentyp und gegebenenfalls auf den im Tabellenentwurf gespeicherten Standardwert. Auch haben wir zwei Varianten – eine mit Übernahme der Standardwerte aus dem Tabellenentwurf und eine ohne.

Wenn wir diese Prozedur ausführen, erhalten wir bereits die gewünschten Daten im Formular (siehe Bild 5).

Bild 5: Liste der Standardwerte

Mit der Prozedur FelderEinlesen haben wir auch bereits die Änderungen von Felddatentypen berücksichtigt – diese werden durch die UPDATE-Anweisungen in die Tabelle tblStandardwerte übernommen.

Gelöschte Felder aus den Standardwerten entfernen

Gelegentlich löscht man Felder aus dem Tabellenentwurf. Dem wollen wir gerecht werden, indem wir mit der Prozedur FelderEntfernen alle in der Tabelle tblStandardwerte gespeicherten Kombinationen aus Tabellen und Feldern prüfen.

Die Prozedur durchläuft alle Datensätze des Recordsets auf Basis der Tabelle tblStandardwerte. Dabei stellt sie den Wert der Field-Variablen fld zuerst auf Nothing ein. Danach versucht sie, bei deaktivierter Fehlerbehandlung auf das Feld zuzugreifen, das sich in der Tabellendefinition aus rst!Tabellenname befindet und dem Feld mit dem Namen aus rst!Feldname entspricht. Ist fld danach leer, ist das Feld offensichtlich nicht mehr vorhanden. In diesem Fall löscht die Prozedur den aktuellen Datensatz mit der Delete-Methode des Recordset-Objekts aus der Tabelle tblStandardwerte. Dieser Vorgang wird für alle in der Tabelle tblStandardwerte enthaltenen Datensätze wiederholt, sodass dort anschließend keine Standardwerte mehr für Felder vorliegen, die nicht in den Tabellen der Datenbank enthalten sind:

Public Sub FelderEntfernen()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Dim fld As DAO.Field
     Set db = CurrentDb
     Set rst = db.OpenRecordset( _
         & "SELECT * FROM tblStandardwerte", dbOpenDynaset)
     Do While Not rst.EOF
         Set fld = Nothing
         On Error Resume Next
         Set fld = db.TableDefs(rst!Tabellenname). _
             Fields(rst!Feldname)
         On Error GoTo 0
         If fld Is Nothing Then
             rst.Delete
         End If
         rst.MoveNext
     Loop
End Sub

Standardwerte einstellen

Nun wollen wir dem Benutzer erlauben, neue Standardwerte für die Felder der Tabellen festzulegen. Eigentlich können wir ihm dazu einfach erlauben, die Werte in das Feld Standardwert im Unterformular einzugeben. Wir müssen allerdings dafür Sorge tragen, dass er einen Wert eingibt, der dem Datentyp entspricht, der für dieses Feld festgelegt ist. Also müssen wir eine Validierung hinterlegen, die prüft, ob der Wert gültig ist.

Als Erstes wollen wir jedoch sicherstellen, dass der Benutzer nicht den Tabellen- und den Feldnamen ändern kann. Dazu stellen wir die Eigenschaft Aktiviert auf Nein und Gesperrt auf Ja ein. Dadurch erreichen wir, dass die Steuerelemente zwar nicht mehr editierbar sind, aber dennoch nicht ausgegraut werden, wie es der Fall wäre, wenn man nur die eigenschaft Aktiviert auf Nein einstellt (siehe Bild 6).

Bild 6: Deaktivieren der beiden Textfelder txtTabellenname und txtFeldname

Die Namen der Steuerelemente haben wir übrigens zuvor auf txtTabellenname, txtFeldname und txtStandardwert eingestellt.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo