Translate

Dienstag, 14. Juli 2015

Mit Klasse die Access-Gültigkeitsmeldung bei erforderlichen Felder verbessern



Aufgabe 1: Umgang mit erforderlichen Feldern
Enthält eine Tabelle erforderlichen Felder, werden diese erst beim speichern des Datensatzes mit folgender Access-System-Meldung angefordert:








Diese Standard-Meldung ist bescheiden und lässt sich nicht beeinflussen.
Des Weiteren kommt jeweils eine Meldung für jedes einzelne erforderliche Feld, was nicht sehr Anwenderfreundlich ist.

Abhilfe Schaft hier ein VBA-Code, der über das Form-Event BeforeUpdate diesen Fall abfängt, um eine selbstdefinierte Meldung erscheinen zu lassen.
Selbstverständlich möchten wir nun diesen Anwenderfreundliche "Service" in sämtlichen Formularen zur Verfügung stellen.
Es liegt nah den VBA-Code unter BeforeUpdate in die anderen Formularen zu kopieren, mit Anpassung der erforderlichen Felder. Programm-Code der letztendlich dasselbe macht x-mal zu kopieren zeugte schon immer von schlechter Codierung. Hinzu kommt das in gewissen Formularen, die Event Prozedur BeforeUpdate bereits VBA-Code für andere Anforderungen enthält. Also können wir in diesen Fällen den Code nicht einfach 1:1 übertragen.
Daher ist die Idee entstanden den VBA-Code inkl. dem Form-Event BeforeUpdate in einer Klasse auszulagern. Somit muss in den Formularen lediglich die Klasse instanziiert werden, mit Übergabe der gewünschten erforderlichen Felder. Der VBA-Code selbst, der die Funktionalität umsetzt, steht somit nur einmalig in einem Klassenmodul.

Aufgabe 2: Formulare sollen mit den Schaltflächen OK/Abbrechen/Übernehmen versehen werden
Die Schaltfläche 'OK' soll den aktuellen Datensatz speichern und das Formular schliessen.

Die Schaltfläche 'Abbrechen' soll den aktuellen Datensatz verwerfen (mit Rückfrage, falls erforderlich) und das Formular schließen.

Die Schaltfläche 'Übernehmen' soll den aktuellen Datensatz speichern und dabei nur aktiv sein, wenn der Datensatz sich im Änderungsmodus befindet.

Auch hier sollte der VBA-Code und die diversen Event-Prozeduren die dazu nötig sind nicht in den Formularen vervielfältigt werden.
Diese passen Thematisch sehr gut in unsere Klasse mit der Gültigkeitsprüfung, also lagern wir den VBA-Code mit den Form-/Button-Events auch in dieser Klasse aus.

In unseren Formularen benötigen wir nur noch den folgenden kleinen Code-Schnipsel:
Option Compare Database
Option Explicit

    Dim cRV As clsFormRecordValidation

Private Sub Form_Load()
    Set cRV = New clsFormRecordValidation
   
    cRV.AddRequiredField Me.TitleOfCourtesy
    cRV.AddRequiredField Me.LastName
    cRV.AddRequiredField Me.Country
    cRV.AddRequiredField Me.ReportsTo
   
    cRV.ButtonOK = Me.cmdOK
    cRV.ButtonApply = Me.cmdApply
    cRV.ButtonCancel = Me.cmdCancel
End Sub
   
Private Sub Form_Close()
    Set cRV = Nothing
End Sub

Die fertige Klasse integriert in einer Kleinem Beispiel kann hier heruntergeladen werden. Der VBA-Code ist kommentiert, in der Hoffnung, dass das Konzept verständlich wird. Ansonsten einfach Fragen stellen über die Kommentar-Funktion. Kritik (Positiv/Negativ) wie auch Anregungen sind erwünscht.

Ich wünsche weiterhin viel Erfolg mit nicht nur guten VBA-Code, sondern zukünftig auch mit Klasse.

Dienstag, 17. Februar 2015

EXCEL: Zellenbereich unterhalb der aktiven Zelle bis Tabellenende auswählen



Jeder Excel-Anwender steht häufig vor der Aufgabe alle Zellen vertikal bis Tabellenende ausgehend von der aktiven Zelle zu markieren oder diesen Bereich mittels VBA bearbeiten zu wollen.
Hinweis: Mit Tabellenende ist nicht die letzte gefüllte Zelle im Arbeitsblatt gemeint, sondern die letzte Zeile einer Datenliste.
Die bekannte Tastenkombination [Umschalt] + [Strg] + [PfeilNachUnten] ist leider nicht immer hilfreich, da diese bei Leer-Zellen endet und nicht zwangsläufig bei Tabellenende.
Die Tastenkombination [Strg]+[a] findet zuverlässig das Tabellenende (insofern keine durchgehende Leerzeile sich in der Tabellenliste sich befindet, wo von ich ausgehe).
Leider ist dann die gesamte Tabelle ausgewählt und nicht der eigentliche gewünschter Bereich.

Daher schreiben wir uns schnelle die folgende VBA-Prozedur:
Public Function CurrentRegionDownward(Optional StartupRange As Range _
                           , Optional SelectRange As Boolean = True) As Range
    'Copyright (r) by Jean Pierre Allain
    Dim t As Range, r As Long
   
    'Wenn kein StartupRange angegeben wurde, dann wird die Aktive Zelle verwendet
    If StartupRange Is Nothing Then Set StartupRange = ActiveCell
   
    'Der StartupRange wird auf eine Zelle reduziert
    Set StartupRange = Cells(StartupRange.Row, StartupRange.Column)
   
    'Die Methode CurrentRegion wählt den gesamten zusammenhängenden Bereich
    'in einer Liste. Dieser wird in t gespeichert
    Set t = StartupRange.CurrentRegion
   
    'Die letzte Zeile aus t wird ausgelesen und in r gespeichert
    r = t.Row + t.Rows.Count - 1
   
    'Nun wird der Bereich von StartupRange bis zum Tabellenende gespeichert
    Set StartupRange = Range(StartupRange, Cells(r, StartupRange.Column))
   
    'Wenn SelectRange=True, dann wird der ermittelte Bereich
    'markiert/ausgewählt/selektiert
    If SelectRange Then StartupRange.Select
   
    'Die Funktion liefert als Rückgabewert den ermittelte Bereich
    'als Range-Objekt zurück
    Set CurrentRegionDownward = StartupRange
End Function

Um den zuvor beschriebenen Bereich zu markieren, muss lediglich diese Funktionsprozedur ausgeführt werden.
Diese Funktionalität kann schnell und einfach verfügbar gemacht werden, in dem sie in die Schnellzugriffsleiste oder in einem Menüband zugeordnet wird.

Hierfür benötige wir asllerdings noch einen kleinen Trick, den bei der Makrozuweisung listet Excel leider nur Sub-Prozeduren ohne Übergabeparameter auf.
Daher tricksen wir Excel aus, in dem wir kurzfristig unsere Prozedur in einer Sub-Prozedur umwandeln:
Public Sub CurrentRegionDownward()
‘Public Function CurrentRegionDownward(Optional StartupRange As Range
                       _, Optional SelectRange As Boolean = True) As Range

Nun lässt sich unsere Prozedur problemlos auf eine Schaltflächensymbol in der Schnellzugriffsleiste oder ins Menüband zuweisen.
Anschließend bitte nicht vergessen, die Sub-Prozedur wieder in eine Funktionsprozedur umzuwandeln:
‘Public Sub CurrentRegionDownward()
Public Function CurrentRegionDownward(Optional StartupRange As Range
                       _, Optional SelectRange As Boolean = True) As Range


Diese Lösung kann auch sehr einfach und effizient in der VBA-Programmierung eingesetzt werden.
Beispiele:
CurrentRegionDownward(, False).FillDown
‘Kopiert den Wert in der aktiven Zelle bis zum Tabellenende (Ohne den Zielbereich zu markieren)

CurrentRegionDownward(Range("A10")).FillDown
‘Kopiert den Wert in der Zelle A10 bis zum Tabellenende (dabei wird der Zielbereich markiert)

Mit dieser kleinen Funktion wünsche ich alle eine Große Arbeitserleichterung.

Freitag, 6. Februar 2015

ACCESS: (Haupt-) Formular aus einem Steuerelement (Control) ermitteln

Heute möchte ich aufzeigen, wie es möglich ist aus einer Objektvariable die ein Steuerelement (Control) speichert, das Formular herauszufinden in welches sich das Steuerelement befindet.

Auch hier scheint die Aufgabe recht trivial, in dem einfach das Objekt Parent verwendet wird. Eine allgemeine Prozedur könnte so sehen:

Public Function ParentformOfControl(ctl As Access.Control) As Access.Form
    Set ParentformOfControl = ctl.Parent
End Function

So einfach ist es leider dann doch nicht, da allgemeine Prozeduren mit allen Eventualitäten umgehen können sollten. Und dies ist mit der oben aufgezeigten Lösung nicht der Fall.
Angenommen, unser Steuerlelement befindet sich in einer RegisterSeite von einem Registersteuerelement, dann zeigt CTL.Parent nicht auf das Formular, sondern auf das Registersteuerelement. In diesem Fall würden wir CTL.Parent.Parent benötigen.
In einem anderen Fall brauchen wir ggf. sogar CTL.Parent.Parent.Parent

Daher setzen wir einfach eine Schleife ein, die bis zum Form-Objekt dreht:

Public Function ParentformOfControl(ctl As Access.Control) As Access.Form
    Dim c As Object
   
    Set c = ctl
    Do
       Set c = c.Parent
    Loop Until Left(TypeName(c), 5) = "Form_"
    Set ParentformOfControl = c
End Function

Und wenn wir schon dabei sind, können wir unsere kleine Routine gleich so erweitern, dass diese auch in der Lage ist das Hauptformular zu ermitteln (optional):

Public Function ParentformOfControl(ctl As Access.Control _
                                   , Optional MainForm As Boolean) As Access.Form
    Dim c As Object
   
    On Error Resume Next
    Set c = ctl
    Do
       Set c = c.Parent
       If Not Err.Number = 0 Then Exit Do
    Loop Until Left(TypeName(c), 5) = "Form_" And MainForm = False
    Set ParentformOfControl = c
End Function

Hinweis: Da es nicht feststellbar ist, ob es noch ein weiteres übergeordnetes Objekt existiert, können wir dies nur mit der Fehlerabfangmethode lösen. Nicht elegant, aber es funktioniert ;-)
Andere Fehler haben wir nicht zu erwarten das hier eine Fehlerbehandlung benötigt wird. Schlimmsten Fall liefert unsere Funktion ParentformOfControl den Wert Nothing zurück, was wiederum in der aufrufenden Prozedur behandelt werden kann.

Fazit:
Einfach scheinende Aufgaben, sind in der Praxis dann oft doch nicht so trivial.
Wirklich komplex ist es jedoch auch nicht und es lohnt sich!
Beim Programmieren von allgemein gültigen Funktionen, ist darauf zu achten, dass diese anschließend alle zu erwartenden Fälle behandeln können.