<\/p>\n
Martin Hoffmann, Düsseldorf<\/b><\/p>\n
Access bietet sowohl dem Einsteiger als auch dem Profi zahlreiche Hilfsmittel und Assistenten, um komplexe Berichte sehr leicht gestalten zu können. Es gibt Assistenten für fast jeden Zweck. Aber in Access gibt es keine Berichte, bei denen die Spaltenanzahl von den Daten abhängt, wie es bei Kreuztabellen vorkommt. Sie können zwar einen Bericht auf Basis einer Kreuztabelle in Access anlegen, wenn aber in der Kreuztabelle neue Spalten hinzukommen, fehlen diese anschließend im Bericht. In diesem Beitrag lernen Sie, wie Sie solche Berichte entwerfen – ohne Datenverlust.<\/b><\/p>\n
Damit wir uns ganz auf die Programmierung des Berichts konzentrieren können, ist das Datenbankmodell für die Beispieldatenbank sehr einfach gehalten. Das Modell besteht lediglich aus der Tabelle tblUmsätze (siehe Bild 1), die als Basis für die Kreuztabelle dient.<\/p>\n
![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic001.png\")
<\/p>\n
<\/b><\/p>\n
Bild 1: Aufbau der Tabelle tblUmsätze<\/span><\/b><\/p>\n In der Tabelle werden die Umsätze mit Eingangsdatum gespeichert. Jeder Umsatz kann dabei einer Kategorie zugeordnet werden. Der Einfachheit halber wurde das Feld Kategorie nicht als Nachschlagefeld mit einer gesonderten Tabelle tblKategorien angelegt, sondern lediglich als Textfeld.<\/p>\n Ebenso einfach wie die Datenbasis ist auch die Eingabe der Umsätze gehalten.<\/p>\n Die Eingabe erfolgt über das Formular frmUmsätzeErfassen. Die Umsätze werden direkt in der Datenblattansicht eingegeben.<\/p>\n <\/b><\/p>\n Bild 2: Eingabe der Umsätze<\/span><\/b><\/p>\n Dabei wird ein Kombinationsfeld verwendet, durch das sich die doppelte Eingabe der Kategorien verhindern lässt.<\/p>\n Das Kombinationsfeld zeigt alle bereits in der Tabelle tblUmsätze vorhandenen Kategorien über die folgende Abfrage an:<\/p>\n <\/b><\/p>\n Bild 3: Die Auswahlabfrage für die Kreuztabelle<\/span><\/b><\/p>\n <\/b><\/p>\n Bild 4: Gruppierungsabfrage für die Kreuztabelle<\/span><\/b><\/p>\n <\/b><\/p>\n Bild 5: Die Kreuztabelle in der Entwurfsansicht …<\/span><\/b><\/p>\n Damit eine neu eingegebene Kategorie direkt im Kombinationsfeld zur Auswahl steht, wird die Datensatzherkunft des Kombinationsfeldes nach Speicherung des Datensatzes aktualisiert. Dazu wird im Formularereignis Nach Aktualisierung der folgende Programmcode eingetragen:<\/p>\n Bild 2 zeigt das Formular mit einigen Beispieldatensätzen.<\/p>\n Die Daten für den Bericht werden in einer Kreuztabelle aufbereitet. Dabei sollen die Umsätze pro Kategorie monatsweise und in Summe aufgeführt werden. Gehen Sie dabei in den folgenden Schritten vor:<\/p>\n Die Daten sind nun bereits richtig in der Abfrage zusammengestellt. Jetzt fehlt nur noch die Darstellung der Daten in Form einer Kreuztabelle, bei der die Monate als Spalten- und die Kategorien als Zeilenüberschriften angezeigt werden.<\/p>\n ändern Sie dazu den Abfragetyp über den Menüpunkt Abfrage ( Kreuztabellenabfrage von Auswahlabfrage in Kreuztabellenabfrage. In der Zeile Kreuztabelle wählen Sie jetzt die Funktion der einzelnen Felder aus.<\/p>\n Setzen Sie bei den Spalten Kategorie und UmsatzProKategorie den Eintrag Zeilenüberschrift. Für das Feld Monat verwenden Sie die Funktion Spaltenüberschrift und beim Feld SummeVonUmsatz die Funktion Wert (siehe Bild 5).<\/p>\n Bild 6 zeigt das Ergebnis der Abfrage in der Datenblattansicht.<\/p>\n <\/b><\/p>\n Bild 6: … und in der Datenblattansicht<\/span><\/b><\/p>\n Im Beispiel sind die Monate Januar, Februar und März mit Umsätzen belegt. Hier wird noch einmal deutlich, dass beim Hinzufügen von neuen Datensätzen in der Tabelle tblUmsätze zum Beispiel für die Monate April und Mai im Ergebnis der Kreuztabellenabfrage automatisch neue Spalten hinzukommen.<\/p>\n <\/b><\/p>\n Bild 7: Starrer Auto-Bericht mit fehlenden Daten<\/span><\/b><\/p>\n Wenn Sie jetzt einen Bericht auf Basis der vorhandenen Daten erstellen, würde Access die Spalten fest anlegen. Bild 7 zeigt als Beispiel einen Bericht, der mit dem Datenbestand aus Bild 2 entworfen wurde.<\/p>\n Allerdings wurden seitdem neue Umsätze im September in einer neuen Kategorie eingegeben.<\/p>\n Dabei fällt auf, dass die Kategorie Administration zwar mit der Kategoriesumme aufgeführt ist, aber der Monat mit dem Umsatz fehlt.<\/p>\n Auch die Summe bei der Kategorie fzg-boerse stimmt nicht, weil die Umsätze aus September zwar zur Gesamtsumme addiert wurden, die Spalte für den Monat September aber auch hier nicht angezeigt wird.<\/p>\n Vorgehensweise<\/b><\/p>\n Um den Bericht so flexibel zu programmieren, dass immer alle Daten der Tabelle tblUmsätze berücksichtigt werden und der Bericht zur Laufzeit dynamisch angepasst wird, sind ein paar Vorüberlegungen nötig:<\/p>\n <\/p>\n Quellcode 1<\/b><\/p>\n <\/b><\/p>\n Bild 8: Die Berichtsvorlage in der Entwurfsansicht<\/span><\/b><\/p>\n Die Berichtsvorlage<\/b><\/p>\n Im ersten Schritt erstellen Sie die Berichtsvorlage. Am einfachsten legen Sie dazu einen neuen leeren Bericht auf Basis der Kreuztabellenabfrage qryUmsätze an. Platzieren Sie dann die fixen Steuerelemente, die unabhängig von der Spaltenanzahl immer gleich bleiben. In unserem Beispiel sind das die Textfelder Kategorie und UmsatzProKategorie im Detailbereich, die mit den gleichnamigen Datenfeldern der Abfrage verknüpft sind. Zusätzlich wurden zwei Bezeichnungsfelder als überschriften im Seitenkopf eingefügt (siehe Bild 8).<\/p>\n Die Felder können Sie noch nach Ihren Vorstellungen formatieren.<\/p>\n Wahlweise ergänzen Sie den Bericht wie zuvor beschrieben noch um weitere Elemente wie einen Titel im Berichtskopf. Speichern Sie den fertigen Bericht unter dem Namen repUmsätze_Vorlage ab.<\/p>\n Die Funktion GenerateReport<\/b><\/p>\n Nachdem Sie die Vorlage gespeichert haben, benötigen Sie eine Funktion, mit der Sie den neuen Bericht erstellen. Hierzu dient die öffentliche Funktion GenerateReport:<\/p>\n Die Funktion ist in der Beispieldatenbank im Modul modReports gespeichert.<\/p>\n Die Funktion löscht zunächst den Bericht repUmsätze. Das ist der Bericht, der durch die Funktion bei einem vorherigen Aufruf erzeugt wurde und nun nicht mehr benötigt wird.<\/p>\n Anschließend kopiert die Funktion mittels der Methode CopyObject die Berichtsvorlage und speichert sie unter dem Namen repUmsätze ab. Zum Schluss öffnet die Funktion den Bericht in der Entwurfsansicht (s. Quellcode 1).<\/p>\n Die neuen Steuerelemente können nun in den Bericht eingefügt werden. Die Steuerelementnamen und -inhalte ermittelt die Funktion dabei aus der Kreuztabellenabfrage. Die Funktion öffnet dazu das Recordset recFields auf Basis der Abfrage und durchläuft danach alle Datenfelder (beziehungsweise Spalten) des Recordsets respektive der Kreuztabellenabfrage. <\/p>\n Die Datenfelder eines Recordsets sind in der Fields-Auflistung des Recordset-Objekts gespeichert. Am einfachsten durchlaufen Sie eine solche Auflistung mit einer For Each-Schleife (s. Quellcode 2). Hierzu deklarieren Sie eine Variable vom Typ Field (im Beispiel MyField):<\/p>\n Quellcode 2<\/b><\/p>\n Quellcode 3<\/b><\/p>\n über diese Variable können Sie dann bei jedem Schleifendurchlauf auf ein anderes Datenfeld und dessen Eigenschaften zugreifen. über die Eigenschaft Name ermitteln Sie beispielsweise den Namen des Datenfeldes. Der Zugriff erfolgt wie beim Objektmodell von Access üblich über die Syntax <\/p>\n Die Funktion GenerateReport fragt die Eigenschaft Name innerhalb der For Each-Schleife ab, um zu vermeiden, dass für die Datenfelder Kategorie und Summe pro Kategorie erneut Steuerelemente angelegt werden, da diese bereits in der Vorlage enthalten sind.<\/p>\n Für alle Datenfelder mit einem anderen Namen werden die Steuerelemente angelegt.<\/p>\n Ein neues Steuerelement erzeugen Sie per VBA durch die Methode CreateReportControl, über die Sie bereits zahlreiche Eigenschaften des Steuerelements festlegen können:<\/p>\n Quellcode 4<\/b><\/p>\n <\/b><\/p>\n Bild 9: Eigenschaftsnamen über die Hilfe ermitteln<\/span><\/b><\/p>\n Zuerst wird das Textfeld im Detailbereich für die Monatssummen mit dem Programmcode aus Quellcode 3 angelegt.<\/p>\n Für die Größe und Position des Steuerelements wurden in der Funktion Konstanten definiert. So können Sie die Steuerelemente an einer zentralen Stelle Ihren eigenen Anforderungen anpassen. Als Spaltenname (Steuerelementinhalt) wird der Name des aktuellen Datenfeldes verwendet.<\/p>\n Nach Anlage des Steuerelements verändert die Funktion einige Eigenschaften, um den Namen und die Formatierung dem Berichtslayout anzupassen (s. Quellcode 4). Auf diese Weise stellen Sie zum Beispiel die Hintergrundfarbe, die Schriftgröße oder die Rahmenart ein.<\/p>\n Die Eigenschaften können dabei nicht direkt gesetzt werden, weil alle Eigenschaften des Steuerelements in der Auflistung Properties definiert sind. Der Zugriff erfolgt deshalb über die Syntax:<\/p>\n Die Eigenschaften werden in VBA in Englisch angegeben. Wenn Sie den Namen einer Eigenschaft benötigen, können Sie in einem beliebigen Bericht das entsprechende Steuerelement anlegen, im Eigenschaften-Fenster die gesuchte Eigenschaft anklicken und die Funktionstaste F1 drücken. Die Hilfe zeigt dann immer den englischen und deutschen Namen der Eigenschaft an (siehe Bild 9). <\/p>\n Quellcode 5<\/b><\/p>\n Quellcode 6<\/b><\/p>\n Wenn Sie einen überblick über alle Eigenschaften des Steuerelements erhalten möchten, können Sie mit der folgenden For Each-Schleife alle englischen Eigenschaftsnamen im Befehlsfenster ausgeben:<\/p>\n Nach Anlage des Textfeldes wird im Seitenkopf das passende Bezeichnungsfeld eingefügt (s. Quellcode 5).<\/p>\n Dem Steuerelementnamen wird dabei das Präfix lbl vorangestellt, damit der Name eindeutig und der Steuerelementtyp im Namen klassifiziert ist. Der Steuerelementinhalt bleibt leer.<\/p>\n Durch das letzte Steuerelement können Sie sich die Monatssumme im Berichtsfuß anzeigen lassen. Als Steuerelementinhalt verwendet die Funktion den Ausdruck<\/p>\n Dem Namen wird das Präfix txtSum vorangestellt (s. Quellcode 6).<\/p>\n Die restlichen Eigenschaften werden äquivalent zu den anderen Steuerelementen gesetzt und können dem Quellcode der Beispieldatenbank entnommen werden.<\/p>\n Nach Anlage des Summenfeldes wird in der Schleife noch die horizontale Startposition der nächsten Spalte über den folgenden Ausdruck festgelegt:<\/p>\n Nachdem über die Schleife für alle Datenfelder der Abfrage die zugehörigen Steuerelemente angelegt sind, wird der Bericht gespeichert und in der Vorschau geöffnet (s. Quellcode 6).<\/p>\n Wie bereits erwähnt, können Sie den Bericht nicht mehr über das Datenbankfenster aufrufen. Sie müssen die in Abschnitt 4 beschriebene Funktion GenerateReport verwenden.<\/p>\n In der Beispieldatenbank erfolgt der Aufruf über die Symbolleiste. Legen Sie dazu ein neues Symbol an und rufen Sie über das Kontextmenü des Symbols das Eigenschaften-Fenster auf (siehe Bild 10). Bei der Eigenschaft Bei Aktion tragen Sie den Funktionsaufruf ein:<\/p>\n Sie können die Funktion natürlich auch über eine Befehlsschaltfläche in einem Formular aufrufen.<\/p>\n <\/b><\/p>\n![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic002.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic003.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic004.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic005.png\")
<\/p>\nSELECT DISTINCT [tblUmsätze].[Kategorie] FROM tblUmsätze ORDER BY [tblUmsätze].[Kategorie]; <\/pre>\n
Private Sub Form_AfterUpdate()\r\n Me!Kategorie.Requery\r\nEnd Sub<\/pre>\n
Monat: Format([Eingangsdatum];\"jjjj\/mm\")<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic006.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic007.png\")
<\/p>\n''Alten Bericht löschen\r\nDoCmd.DeleteObject acReport, \"repUmsätze\"\r\n''Bericht von der Vorlage kopieren\r\nDoCmd.CopyObject CurrentDb().Name, \"repUmsätze\", _ acReport, \"repUmsätze_Vorlage\"\r\n''Bericht versteckt in der Entwurfsansicht öffnen\r\nDoCmd.OpenReport \"repUmsätze\", acViewDesign<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic008.png\")
<\/p>\nPublic Function GenerateReport()<\/pre>\n
Erzeugen des neuen Berichts<\/h3>\n
Hinzufügen der Datenfelder<\/h3>\n
''Datenbank und Recordset öffnen\r\nSet db = CurrentDb()\r\nSet recFields = db.OpenRecordset(\"qryUmsätze\")\r\nFor Each MyField In recFields.Fields\r\n If MyField.Name <> \"Kategorie\" And MyField.Name <> \"UmsatzProKategorie\" Then\r\n ...\r\n ''hier werden die Steuerelemente angelegt\r\n ...\r\n End If\r\nNext<\/pre>\n
Dim MyControl As Control\r\n''Konstanten für die Größe der Steuerelemente und der oberen Position\r\nConst ControlWidth = 1250\r\nConst ControlHeight = 226\r\nConst ControlTop = 0\r\n''Position des ersten Steuerelements\r\nCurrentLeft = 3911\r\nSet MyControl = CreateReportControl(\"repUmsätze\", acTextBox, acDetail, , _ MyField.Name, currentLeft, ControlTop, ControlWidth, ControlHeight)<\/pre>\n
Dim MyField As Field<\/pre>\n
<Objektname>.<Eigenschaft><\/pre>\n
Steuerelemente anlegen<\/h3>\n
MyControl.Properties(\"Name\") = MyField.Name\r\nMyControl.Properties(\"Format\") = \"Euro\"\r\nMyControl.Properties(\"FontItalic\") = False\r\nMyControl.Properties(\"FontSize\") = 8\r\nMyControl.Properties(\"FontName\") = \"Arial\"\r\nMyControl.Properties(\"TextAlign\") = 3 ''rechtsbündig\r\nMyControl.Properties(\"FontBold\") = False\r\nMyControl.Properties(\"BorderColor\") = 9868950\r\nMyControl.Properties(\"BorderStyle\") = 1<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic009.png\")
<\/p>\n<Objektname>.Properties(\"<Eigenschaftsname>\")<\/pre>\n
Praxis-Tipp<\/h3>\n
Set MyControl = CreateReportControl(\"repUmsätze\", _ acLabel, acPageHeader, , , currentLeft, _ ControlTop, ControlWidth, ControlHeight)\r\nMyControl.Properties(\"Name\") = \"lbl\" & MyField.Name\r\nMyControl.Properties(\"Caption\") = MyField.Name\r\nMyControl.Properties(\"FontItalic\") = False\r\nMyControl.Properties(\"FontSize\") = 8\r\nMyControl.Properties(\"FontName\") = \"Arial\"\r\nMyControl.Properties(\"TextAlign\") = 3 ''rechtsbündig\r\nMyControl.Properties(\"FontBold\") = True\r\nMyControl.Properties(\"ForeColor\") = \"16777215\"\r\nMyControl.Properties(\"BackColor\") = \"8388608\"\r\nMyControl.Properties(\"BackStyle\") = 1<\/pre>\n
Set MyControl = CreateReportControl(\"repUmsätze\", acTextBox, acFooter, , \"=sum([\" & MyField.Name & \"])\", currentLeft, ControlTop, ControlWidth, ControlHeight)\r\nMyControl.Properties(\"Name\") = \"txtSum\" & MyField.Name\r\n''Bericht speichern\r\nDoCmd.Save acReport, \"repUmsätze\"\r\n''Bericht anzeigen\r\nDoCmd.OpenReport \"repUmsätze\", acViewPreview<\/pre>\n
Dim MyProp As Property\r\nFor Each MyProp In MyControl.Properties\r\n Debug.Print MyProp.Name & \": \" _ & MyProp.Value\r\nNext<\/pre>\n
=sum([\" & MyField.Name & \"])<\/pre>\n
currentLeft = currentLeft + 1250<\/pre>\n
Speichern und Anzeigen des Berichts<\/h3>\n
=GenerateReport()<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic010.png\")
<\/p>\n
![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic011.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{8334402D-0672-4449-BC70-5ADC81902717}\/pic012.png\")
<\/p>\n