Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Autor: André Minhorst und Michael Kessel, Duisburg
Eine Datenbank ist prädestiniert für die Pflege der Informationen über Ihre Mitarbeiter. Sie dient der Speicherung der Mitarbeiterdaten sowie der Daten über deren Beschäftigungsverhältnisse und weiterer Informationen – wie z. B. der Projekte, an denen sie beteiligt sind. In der Ausgabe 7/2001 haben Sie im ersten Teil des vorliegenden Beitrags erfahren, wie Sie mit Access Tabellen und deren Beziehungen auf der Basis der MSDE aufbauen können, und einige Beispiele für gespeicherte Prozeduren und Trigger kennen gelernt. Nun geht es an das Frontend – und damit an die Programmierung der Formulare und Berichte.
Hinweis
Der vorliegende Beitrag setzt das Vorhandensein von Access 2000 sowie die Installation der MSDE bzw. des SQL Servers voraus. Wie das funktioniert, erfahren Sie im Beitrag Die Microsoft Data Engine der Ausgabe 1/2000 von Access im Unternehmen. Sie finden den Beitrag im PDF-Format auf der Heft-CD.
Die Mitarbeiterverwaltung benötigt fünf Formulare, von denen zwei allerdings Unterformulare eines weiteren Formulars sind.
Das erste Formular namens frmMitarbeiterAuswahl (siehe Bild 1) dient zur Auswahl des anzuzeigenden Mitarbeiters aus einem Listenfeld. Dabei gibt es noch einige Optionen, mit denen die Auswahl der angezeigten Mitarbeiter eingeschränkt werden kann. Von diesem Formular aus kann man Detailinformationen zu dem ausgewählten Mitarbeiter aufrufen.
Die Detailinformationen werden in dem Formular frmMitarbeiterDetail angezeigt (siehe Bild 8). Das Formular ist gleichzeitig Hauptformular für zwei Unterformulare.
Das Unterformular sfmMitarbeiterBeschaeftigungen zeigt das aktuelle und ggf. vorherige Beschäftigungsverhältnis des jeweiligen Mitarbeiters an. Das Unterformular namens sfmMitarbeiterProjekte zeigt die aktuellen Projekte eines Mitarbeiters.
Hinweis
Regelmäßigen Lesern von Access im Unternehmen und des Praxishandbuchs Access sind die meisten der nachfolgend beschriebenen Techniken zur Erstellung von Formularen, Unterformularen und deren Steuerelementen bekannt. Daher wird verstärkt auf solche Techniken eingegangen, bei denen es Unterschiede zwischen herkömmlichen Access-Datenbanken und Access-Projekten gibt.
Das Formular zur Anzeige der Mitarbeiterdetails soll wie in Bild 1 aussehen. Es beinhaltet vier Schaltflächen, ein Kontrollkästchen, zwei Textfelder und ein Listenfeld.
Legen Sie am besten zunächst ein neues Formular mit den entsprechenden Steuerelementen an und bezeichnen Sie die Steuerelemente wie in Tab. 1 angegeben. Speichern Sie es anschließend unter dem Namen frmMitarbeiterAuswahl.
Bild 1: Das fertige Formular zur Verwaltung der Mitarbeiter
|
Steuerelementname |
Steuerelement bezeichnung |
|
cmdNeuerMitarbeiter |
Neuer Mitarbeiter |
|
cmdMitarbeiterLoeschen |
Mitarbeiter löschen |
|
cmdBeschaeftigungLoeschen |
Beschäftigung löschen |
|
cmdListeDrucken |
Aktuelle Liste drucken |
|
ctlAlle |
|
|
txtEintrittVon |
Eintritt von: |
|
txtEintrittBis |
Eintritt bis: |
|
lstMitarbeiter |
Tab. 1: Steuerelemente des Formulars frmMitarbeiterAuswahl
Die Steuerelemente zur Filterung der Datensatzherkunft des Listenfeldes
Um den Rahmen des vorliegenden Beitrags nicht zu sprengen, soll der Inhalt des Listenfeldes nur nach zwei Kriterien gefiltert werden. Das erste Argument liefert das Kontrollkästchen ctlAlle. Es soll festlegen, ob alle Beschäftigungsverhältnisse aller Mitarbeiter oder nur aktuelle Beschäftigungsverhältnisse angezeigt werden sollen.
Außerdem soll der Benutzer den Zeitraum des Starts des Beschäftigungsverhältnisses eingeben können. Dazu dienen die beiden Textfelder txtEintrittVon und txtEintrittBis.
Die Schaltflächen zum Auslösen weiterer Aktionen
Das Formular enthält vier Schaltflächen. Die Schaltflächen dienen zum Anlegen eines neuen Mitarbeiters, zum Löschen eines Mitarbeiters oder eines Beschäftigungsverhältnisses sowie zum Drucken einer Liste mit den aktuell ausgewählten Mitarbeitern bzw. Beschäftigungsverhältnissen.
Die Beschreibung der Funktionalität der Schaltflächen folgt im Anschluss an die Beschreibung des Listenfeldes.
Das Listenfeld lstMitarbeiter
Kernstück des Formulars ist das Listenfeld. Es dient primär zur Anzeige der Mitarbeiter. Außerdem kann der Benutzer per Doppelklick auf einen Eintrag des Listenfeldes die Detailinformationen eines Mitarbeiters in einem weiteren Formular anzeigen.
Während es bei Access-Datenbanken leicht ist, den Inhalt eines Listenfeldes entsprechend bestimmter Auswahlkriterien zu filtern, ist dies bei Access-Projekten in Zusammenarbeit mit der MSDE oder dem SQL Server etwas komplizierter.
Sie können zwar einfach eine entsprechende SQL-Anweisung zusammensetzen und als Datensatzherkunft des Listenfeldes angeben. Dies geht allerdings zu Lasten der Performance, da die Abfrage jedes Mal neu kompiliert und an den SQL Server geschickt werden muss.
Um die Performance bei der Abfrage der gewünschten Daten zu erhöhen, sollten Sie die Abfrage möglichst auf dem SQL Server ausführen. Für einfache Abfragen verwenden Sie einfach eine Sicht. Diese hilft aber nicht mehr weiter, wenn die Abfrage die Verwendung von Parametern erfordert. Daher verwenden Sie eine gespeicherte Prozedur.
Bild 2: Die gespeicherte Prozedur procLstMitarbeiter
Bild 3: Ergebnis einer gespeicherten Prozedur
Datensatzherkunft des Listenfeldes
An das Listenfeld sollen drei Parameter übergeben werden: Der Wert des Kontrollkästchens ctlAlle sowie die Inhalte der beiden Textfelder txtEintrittVon und txtEintrittBis.
Eine gespeicherte Prozedur (siehe Bild 2) soll die übergebenen Parameter auswerten und das Ergebnis als Datensatzherkunft des Listenfeldes zurückgeben.
Die gespeicherte Prozedur verwendet drei Parameter. Der Parameter @Alle hat den Datentyp Integer. Der Hintergrund ist die unterschiedliche Anwendung von Ja/Nein-Werten in Access-Datenbanken und im SQL Server. Während es in Access-Datenbanken die Werte 0 (Falsch), -1 (Wahr) und NULL gibt, verwendet der Datentyp BIT des SQL Servers lediglich die Werte 1 (Wahr) und 0 (Falsch).
Die gespeicherte Prozedur besteht aus einer IF-Abfrage, die den Inhalt der Variablen @Alle überprüft. Falls vom Formular der Wert 0 übergeben wird, sollen nur diejenigen Beschäftigungsverhältnisse zurückgegeben werden, deren Austrittsdatum den Wert NULL hat.
Die beiden Parameter @EintrittVon und @EintrittBis dienen der Einschränkung der Datensätze nach dem Eintrittsdatum.
Sie enthalten jeweils einen Standardwert. Die Standardwerte kommen zum Einsatz, falls die entsprechenden Textfelder keinen Inhalt haben und damit keine Parameter übergeben werden.
Testen der Datensatzherkunft
Nachdem Sie die gespeicherte Prozedur eingegeben haben, können Sie direkt mit einem Test beginnen (vorausgesetzt, Sie haben bereits einige Daten für die beiden Tabellen tblMitarbeiter und tblBeschaeftigungen eingegeben): Starten Sie einfach einmal die Prozedur per Doppelklick auf ihren Namen im Datenbankfenster.
Daraufhin erscheinen nacheinander drei Eingabefenster, die zur Abfrage der drei Parameter dienen. Nach der Eingabe der Parameter erscheint das Ergebnis der Abfrage (siehe Bild 3).
Sub ListeAktualisieren()
Dim Parameter As String
Parameter = "@Alle = " & Me.ctlAlle
Parameter = Parameter & IIf(IsNull(Me.txtEintrittVon), "", ", @EintrittVon = ''" _ & Me.txtEintrittVon & "''")
Parameter = Parameter & IIf(IsNull(Me.txtEintrittBis), "", ", @EintrittBis = ''" _ & Me.txtEintrittBis & "''")
Me.lstMitarbeiter.RowSource = "EXEC procLstMitarbeiter " & Parameter
Me.lstMitarbeiter.Requery
End Sub
Quellcode 1
Funktionalität der Steuerelemente
In den nächsten Abschnitten erfahren Sie, wie Sie das Listenfeld mit den Ergebnissen der gespeicherten Prozedur füllen und wie Sie der gespeicherten Prozedur die erforderlichen Parameter übergeben.
Die Prozedur ListeAktualisieren
Die Prozedur aus Quellcode 1 dient der Aktualisierung des Listenfeldes. Sie setzt einen String zusammen, der den Namen der gespeicherten Prozedur procLstMitarbeiter sowie die entsprechenden Parameter enthält.
Die Parameter enthalten die Werte der Steuerelemente ctlAlle, txtEintrittVon und txtEintrittBis. Die Parameter @EintrittVon und @EintrittBis fallen weg, wenn die entsprechenden Textfelder leer sind. In dem Fall verwendet die gespeicherte Prozedur die im Deklarationsteil angegebenen Standardwerte (siehe Bild 2).
Die Prozedur legen Sie im Modul des Formulars frmMitarbeiterAuswahl an. Um das Modul zu öffnen, aktivieren Sie die Entwurfsansicht des Formulars und verwenden den Menübefehl Ansicht/Code. Daraufhin öffnet sich das entsprechende Modul.
Anschließend müssen Sie dafür sorgen, dass diese Prozedur auch ausgelöst wird. Das soll beim öffnen des Formulars sowie nach der Aktualisierung der drei Steuerelemente ctlAlle, txtEintrittVon und txtEintrittBis passieren.
Dazu legen Sie jeweils eine neue Prozedur für das Ereignis Beim öffnen des Formulars sowie für das Ereignis Nach Aktualisierung der drei anderen Steuerelemente an und geben als einzigen Befehl den folgenden Ausdruck an:
ListeAktualisieren
Weitere Informationen zum Anlegen einer Ereignisprozedur finden Sie im Beitrag Anlegen von Ereignisprozeduren im vorliegenden Heft.
Geben Sie außerdem für die Eigenschaft Standardwert des Kontrollkästchens den Wert 0 an.
Anschließend können Sie direkt die neuen Funktionen ausprobieren, indem Sie das Formular speichern und anschließend in der Formularansicht öffnen. Das Ergebnis sieht nun wie in Bild 4 aus. Leider entspricht dies aber nicht dem gewünschten Ergebnis.
Sie können jedoch mit drei kleinen änderungen am Listenfeld nachhelfen:
Ein erneuter Wechsel in die Formularansicht lässt das Ergebnis direkt freundlicher aussehen (siehe Bild 5).
Funktionalität der Schaltflächen
Bild 4: Das Listenfeld zeigt nicht die erwarteten Daten an.
Bild 5: Das Listenfeld mit funktionierender Anzeige
Nun widmen Sie sich der Funktionalität der Schaltflächen. Dazu legen Sie für jede Schaltfläche eine eigene Ereignisprozedur an, die in den folgenden Abschnitten beschrieben wird.
Private Sub cmdNeuerMitarbeiter_Click()
DoCmd.OpenForm "frmMitarbeiterDetail", , , , acFormAdd, acDialog
Me.lstMitarbeiter.Requery
End Sub
Quellcode 2
Neuer Mitarbeiter
Die Schaltfläche cmdNeuerMitarbeiter soll das Formular frmMitarbeiterDetail öffnen, das weiter unten beschrieben wird. In Quellcode 2 finden Sie die dazu notwendige Ereignisprozedur.
Sie verwenden in der Prozedur die Konstante acFormAdd für den Parameter DataMode, damit das aufgerufene Formular nur zur Eingabe verwendet werden kann.
Der Wert acDialog für den Parameter WindowMode bewirkt, dass die Ausführung aller anderen Aktivitäten unterbrochen wird, bis der Anwender das aufgerufene Formular wieder schließt.
Um die Schaltfläche zu testen, können Sie auf die Schnelle ein leeres Formular namens frmMitarbeiterDetail erstellen.
Durch Betätigen der Schaltfläche können Sie das neue Formular öffnen. Nach dem Schließen des Formulars kehren Sie automatisch wieder zum aufrufenden Formular zurück.
Mitarbeiter löschen
Die Schaltfläche cmdMitarbeiterLoeschen soll das Löschen des aktuell im Listenfeld markierten Mitarbeiters bewirken. Dazu legen Sie für die Ereigniseigenschaft Beim Klicken die Prozedur aus Quellcode 3 an.
Private Sub cmdMitarbeiterLoeschen_Click()
If MsgBox("Mitarbeiter und verknüpfte Beschäftigungen löschen", _
vbExclamation + vbOKCancel, "Mitarbeiter löschen") = vbOK Then
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.Execute "procMitarbeiterLoeschen " & Me.lstMitarbeiter
Set con = Nothing
Me.lstMitarbeiter.Requery
Else
Exit Sub
End If
End Sub
Quellcode 3
Die Prozedur fragt zunächst ab, ob der ausgewählte Datensatz auch tatsächlich gelöscht werden soll. Falls ja, führt die Abfrage eine gespeicherte Prozedur zum Löschen des Datensatzes durch. Der Text der gespeicherten Prozedur zum Löschen des gewünschten Datensatzes ist in Quellcode 4 enthalten.
Alter Procedure procMitarbeiterLoeschen (@Mitarbeiternummer INT)
As
set nocount on
DELETE FROM dbo.tblMitarbeiter
WHERE MitarbeiterID = @Mitarbeiternummer
return
Quellcode 4
Die VBA-Prozedur übergibt dabei die Datensatznummer des entsprechenden Mitarbeiters an die gespeicherte Prozedur.
Möglicherweise fragen Sie sich, was mit den Einträgen der Tabelle tblBeschaeftigungen passiert, die mit dem gelöschten Mitarbeiter verknüpft sind. Diese können ja eigentlich ebenfalls entfernt werden, da keine passenden Mitarbeiterdaten mehr vorhanden sind.
Im ersten Teil des vorliegenden Beitrags in Ausgabe 7/2001 haben Sie bereits einen Trigger erstellt, der dieses Problem löst. Dieser Trigger löscht alle Datensätze der Tabelle tblBeschaeftigungen, die zum gelöschten Mitarbeiter gehören.
Beschäftigung löschen
Möglicherweise möchten Sie einmal nicht die kompletten Mitarbeiterdaten, sondern nur die Daten zu einem Beschäftigungsverhältnis löschen. In dem Fall verwenden Sie die Schaltfläche mit der Beschriftung Beschäftigung löschen.
Die Vorgehensweise ist prinzipiell mit der beim Löschen eines Mitarbeiters vergleichbar. Allerdings kann es vorkommen, dass Sie auf diese Weise alle Beschäftigungsverhältnisse eines Mitarbeiters löschen und dieser dann nicht mehr im Listenfeld angezeigt wird.
Daher soll die Anwendung – falls nur ein Beschäftigungsverhältnis für den Mitarbeiter vorhanden ist – den Benutzer fragen, ob der Mitarbeiter direkt mitgelöscht werden soll.
Der Nebeneffekt dieser Mehrarbeit ist, dass Sie direkt lernen, eine gespeicherte Prozedur mit Rückgabeparameter zu erstellen.
Der Ablauf beim Löschen der Beschäftigung sieht folgendermaßen aus: Zunächst wählt der Benutzer das zu löschende Beschäftigungsverhältnis aus und betätigt die entsprechende Schaltfläche.
Die dadurch ausgelöste Prozedur überprüft, ob es noch weitere Beschäftigungsverhältnisse zu dem entsprechenden Mitarbeiter gibt. Falls ja, fragt sie den Benutzer, ob direkt der komplette Mitarbeiterdatensatz gelöscht werden soll.
Gespeicherte Prozedur mit Rückgabeparameter
Alter Procedure procBeschaeftigungenZaehlen (@BeschaeftigungID INT, @AnzahlBeschaeftigungen INT OUTPUT) As DECLARE @MitarbeiterID INT SELECT @MitarbeiterID = MitarbeiterID FROM tblBeschaeftigungen WHERE BeschaeftigungID = @BeschaeftigungID SELECT * FROM tblBeschaeftigungen WHERE MitarbeiterID = @MitarbeiterID SELECT @AnzahlBeschaeftigungen = @@ROWCOUNT return
Quellcode 5
Public Function AnzahlBeschaeftigungen _ (BeschaeftigungID As Integer)
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.CommandText = "procBeschaeftigungenZaehlen"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cnn
Set prm = cmd.CreateParameter("@BeschaeftigungID", _ adInteger, adParamInput)
prm.Value = BeschaeftigungID
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter _ ("@AnzahlBeschaeftigungen", adInteger, _ adParamOutput)
cmd.Parameters.Append prm
cmd.Execute
AnzahlBeschaeftigungen = _ cmd.Parameters("@AnzahlBeschaeftigungen")
End Function
Quellcode 6
Sie benötigen eine gespeicherte Prozedur zur Ermittlung der Anzahl der Beschäftigungen des Mitarbeiters (s. Quellcode 5). Die Prozedur enthält die zwei Parameter @Beschaeftigung und @AnzahlBeschaeftigungen. Der zweite Parameter wird durch das angehängte Schlüsselwort OUTPUT als Rückgabeparameter deklariert.
Nach der Deklaration des Hilfsparameters @MitarbeiterID wird diesem in einer Abfrage die MitarbeiterID des Mitarbeiters zugewiesen, zu dem das zu löschende Beschäftigungsverhältnis gehört.
In einer zweiten Abfrage werden alle Datensätze der Tabelle tblBeschaeftigungen ermittelt, die zu diesem Mitarbeiter gehören. Hinter dem Ausdruck @@ROWCOUNT verbirgt sich eine der vielen eingebauten SQL-Funktionen. Diese ermittelt die Anzahl der Datensätze des aktuellen Abfrageergebnisses und weist sie dem Rückgabeparameter @AnzahlBeschaeftigungen zu.
Aufruf einer gespeicherten Prozedur mitRückgabeparametern
Die Funktion zum Aufruf der gespeicherten Prozedur und zum Auslesen des Rückgabeparameters finden Sie in Quellcode 6.
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