Office: (Office 2016) VBA: Automatisches Hinzufügen von Datensätze auf Basis einer Abfrage

Hallo zusammen,

ich baue gerade eine Datenbank zur Erstellung von Reisekostenrechnungen. Reisekosten setzen sich zusammen aus Fahrt-, Unterkunfts- und Reisenebenkosten sowie Verpflegungsmehraufwand. Es gibt eine Tabelle tblTrips, die nur aus zwei Feldern ID und Reisegrund besteht. Für die oben genannten Kosten gibt es jeweils eine eigene Tabelle, die die TripID als Fremdschlüssel beinhaltet, sodass eine bestimmte Fahrt einer bestimmten Reise/Trip zugeordnet werden kann. Für die Eingabe der Daten benutze ich ein gebundenes Formular.

Probleme bereitet mir nun der Verpflegungsmehraufwand. Warum? Für die Berechnung des Verpflegungsmehraufwandes ist es wichtig zu wissen, ob an gewissen Tagen unentgeltlich Mahlzeiten eingenommen worden. Die Tabelle für den Verpflegungsmehraufwand tblFoodAllowance beinhaltet also die ID, TripID, Datum, Frühstück (ja/nein), Mittag (ja/nein) und Abendbrot (ja/nein). Der Reisezeitraum ist aber nun bereits durch die Fahrten definiert (erste und letzte Fahrt unter einer bestimmten TripID). Angenommen ich füge eine Hinfahrt am 10.07.2020 und eine Rückfahrt am 12.07.2020 zu meiner Fahrtkostentabelle hinzu, dann sollen automatisch in der Tabelle tblFoodAllowance drei Datensätze (10.07.2020, 11.07.2020 und 12.07.2020) erzeugt werden.

Ich schätze, dass dafür nur VBA infrage kommt?

Wie ich einen neuen Datensatz hinzufüge, habe ich schon herausgefunden:

Code:

Zwei Dinge müssten allerdings noch hinzugefügt werden:

1. Zunächst müssten alle Daten mit einer bestimmten TripID in tblFoodAllowance gelöscht werden. Wie geht das?
2. Arrival und Departure müssten per VBA als eine Art Abfrage aus der Tabelle für die Fahrtkosten ermittelt werden. Wie bewerkstellige ich das?
   
   Welches Ereignis nehme ich am besten, um den Code auszulösen? Am besten sollte der Code immer dann ausgeführt werden, wenn sich etwas in der Tabelle für die Fahrtkosten ändert.
   
   Eventuell gibt es ja auch einen Weg ohne VBA?
   
   Danke für eure Hilfe

:)

 

Hallo,
nach "Option Compare Database" immer "Option explicit" einfügen.
Ich hoffe, ich habe Dich in allem richtig verstanden. Das Löschen kannst Du durch ein SQL-Statement erreichen, etwa so:
Code:

Departure und Arrival kannst Du über Domänen-Aggregat-Funktionen herausfinden, in VBA etwa so:
Code:

Gruß Thomas

 

Hallo officeNutzer89,

dein ganzes Problem basiert auf einer falschen Vorgehensweise. Es besteht keine Notwendigkeit dazu, in die Tabelle tblFoodAllowance pro forma neue DS hinzuzufügen und damit entfällt auch die Aktion für das Löschen sowie nach dem Suchen eines passenden Events. DS werden nicht auf "Vorrart" angelegt, sondern nur, wenn sie benötigt werden.

Du solltest also nochmal die Vorgehensweise überdenken und entsprechend ändern.

Ulrich

PS:
... und das Anlegen und spätere Löschen führt in Access zum Aufblähen der DB!

 

Hallo,
und die Kosten gehören in eine Tabelle mit einem Kombifeld zur Auswahl der Kostenart.
Daher ist auch für die Kostenart eine Tabelle notwendig.

 

Das stimmt nicht. Folgendes Beispiel: Ich unternehme eine geschäftliche Reise von Hamburg nach Köln nach Berlin und zurück nach Hamburg. Daraus ergeben sich folgende Fahrten:

01.07.20: Hamburg - Köln, 120€
03.07.20: Köln - Berlin, 100€
06.07.20: Berlin - Hamburg, 80€

und Unterkunftskosten:
01.07. - 03.07.20: Köln, 200€
03.07. - 06.07.20: Berlin, 250€

Der Reisezeitraum ergibt sich aus den Fahrten (01.07. - 06.07.20). Für jeden Tag einer Reise ist der Verpflegungsmehraufwand anzuwenden, also:

01.07.20: 14€
02.07.20: 28€
03.07.20: 28€
04.07.20: 28€
05.07.20: 28€
06.07.20: 14€

Für die ganze Reise ergeben sich also Kosten in Höhe von 890€. Diese Berechnung möchte ich in Access abbilden.

Man sieht, dass die Berechnung des Verpflegungsmehraufwandes direkt vom Reisezeitraum abhängt, welcher über die Fahrten definiert ist. Ändert der Nutzer etwas an den gemachten Fahrten, müssen sich auch die Einträge für den Verpflegungsmehraufwand direkt ändern.
Es ist leider nicht so einfach, dass man sagen könnte, dann lässt man einfach die Tabelle für den Verpflegungsmehraufwand weg und berechnet das anhand der Fahrten. Stellen wir uns vor, dass in dem Hotel in Köln Frühstück im Preis inkludiert war, wohingegen dies in Berlin nicht der Fall war. In diesem Fall reduziert sich der Verpflegungsmehraufwand um 20% für die Nächte in Köln. All diese Informationen muss der Nutzer bereitstellen. Am Ende soll eine Reisekostenrechnung in Form eines Berichtes stehen.

 

Das verstehe ich nicht. Könntest du das näher ausführen?

 

Hallo officeNutzer89,

nochmal:
es werden keine DS pro forma erfaßt, daß ist grundsätzlich falsch, sondern nur DS mit entsprechenden Kostenarten. Diese DS werden gegen den Reisezeitraum und den Belegen verifiziert.

Hier schreibst du selber, daß das abhängige Werte sind und dann können sie auch berechnet werden. Also sollte das so auch entsprechend gemacht werden.
Wenn du diese automatische Berechnung nicht haben möchtest, könnte man auch die entsprechenden DS mit den Kostenarten per "Ausfüllhilfe" eintragen lassen, aber dann schon mit den richtigen Werten und nicht "pro forma" und anschließend löschen.

Ulrich

 

Hallo Knobbi,

danke für deine Nachricht, aber ich verstehe nicht, auf was du hinaus willst. Könntest du es etwas konkreter machen, vielleicht mit Beispielen?

Ich verstehe, dass das automatische Erstellen von Datensätzen problematisch ist. Den Gedanken teile ich sogar, aber ich weiß eben nicht wie ich es besser machen kann.
Mir fallen jetzt zwei Lösungsansätze ein. Bin mir aber nicht sicher, ob es das ist, was du meinst.
Man könnte den Nutzer irgendwas ins Formular eintragen lassen und beim Schließen des Formulars prüfen, ob seine Eingaben Sinn ergeben. Falls nicht, gibt es eine Fehlermeldung. Diese Lösung finde ich insofern unschön, dass das Programm ja die Tage, an denen Verpflegungsmehraufwand berechnet werden muss, bereits kennt und nur darauf wartet, dass der Nutzer dies korrekt einträgt.
Eine zweite Möglichkeit wäre, es über ungebundene Steuerelemente zu regeln, sodass die Anzeigen des Verpflegungsmehraufwandes im Formular nicht mit tatsächliche gespeicherten Daten in der Datenbank korrespondieren, sondern erst mit dem Schließen des Formulars übernommen werden. Ich stelle mir das aber reichlich komplex vor, da dann dynamisch mit Steuerelementen umgegangen werden muss, schließlich weiß ich ja vorher nicht, an wie vielen Tagen Verpflegungsmehraufwand berechnet werden muss.

Danke nochmal für die Unterstützung.

 

Ich habe mal die Access-Datei angehangen.

 

Sind die Pauschalen des Mehraufwands , erhöht worden ?
Das waren doch bisher 24,- bzw. 12, - für An- u. Abreise, bzw. 0 macht keinen Sinn. Standardwert 0 bei einem FK ist ebenso kontraproduktiv.

Warum das Löschen der DS ?

 

Hallo officeNutzer89,

ich versuche das nochmal zu erklären. Die DS werden nicht automatisch angelegt, sondern jeder DS muß individuell mit der entsprechenden Kostenart angelegt und verifiziert werden, nur so ist sichergestellt, daß die DS mit den Belegen übereinstimmen.
Beispiel, deine Reise Hamburg-Köln-Berlin-Hamburg:
Bei der automatischen Generierung würden alle Verpflegungspauschalen eingetragen und wenn keine manuelle Nachbearbeitung mehr erfolgt, würden die 20% Reduzierung nicht beachtet und die Berechnung wäre nicht korrekt.
Das Problem ist, wie kann ich den Anwender zwingen, eine manuelle Nachbearbeitung zu machen. Mit den Flags wäre es möglich, aber für die Fälle, wo keine Reduzierung erfolgen muß, ein zusätzlicher Aufwand und fehlerträchtig. Der Anwendung muß ja praktisch gezwungen werden, jeden automatisch angelegten DS zu bestätigen.

Die Erfassung würde ich so gestalten, wie sie am "natürlichsten" empfunden wird: einfach die originalen Belege in einer Erfassungsmaske direkt erfassen, also z.B. bei einer Hotelrechnung Anreisetag, Anzahl Übernachtungen, mit/ohne Frühstück. Das ist die bequemste Form. Die Programmierung hinter dem Formular sorgt dann für die entsprechenden Buchungssätze und ja, das bedeutet u.U. auch ungebundene Formulare Hilfreich wäre dabei auch ein entsprechendes Objektmodell, mit dem dann die Logik und Regeln umgesetzt und durchgesetzt würden.

Dabei stellt sich mir noch eine andere Frage:
ließe sich der Verpflegungsmehraufwand nicht auch wie die Unterkunftskosten buchen, also Summe Verpflegungsmehraufwand Köln, Summe Verpflegungsmehraufwand Berlin? Das An-/Abreisetage gesondert behandelt werden müssten, weiß dabei das Objektmodell und könnte das auch automatisch berücksichtigen.

Gruß Ulrich

 

Es kann ja sein, dass der Nutzer eine Fahrt vergessen hat oder irgendetwas ändert.

Mein Gedanke war der: Der Nutzer fügt zu einer Reise Fahrten hinzu oder ändert z.B. die Daten bereits bestehender Fahrten. War es eine Änderung, dann sind die Daten zum Verpflegungsmehraufwand nicht mehr gültig, ansonsten werden sie neu erstellt. Daher kommt der Gedanke erstmal alles zu löschen und dann neuzuerstellen. Es wäre aber genauso möglich vorhandene Daten zu überschreiben. Ich kenne mich in SQL zu wenig aus.
Prinzipiell wäre die Logik: Füge für jeden Tag der Reise einen Standard-Datensatz ein. Falls für diesen Tag bereits ein Datensatz existiert, dann überschreibe diesen. Wie würde man das in SQL lösen?

Allerdings gibt es bei der Überschreibe-Lösung ein Problem, zum Beispiel wenn der Nutzer sich beim Eintrag der Fahrten um einen Tag vertut und das dann korrigiert. Dann bleiben alte, ungültige Datensätze bestehen. Das wäre problematisch.

 

Hast du dir meine Lösung angeschaut ?
Die Anfüge Anfrage schreibt nur Datensätze, die noch nicht vorhanden sind.

Ok, wenn das Löschen Reise bezogen ist.

 

Hallo,
ich würde das wie in #4 (ist auch von mir, mit anderem Namen) angedeutet völlig anders lösen.

Lege Dir eine Tabelle an in der alle Kostenarten die es geben kann eingetragen werden. Auch wenn was abgezogen wird ist das dann eine Kostenart. In die Tabelle kommen daher auch Kostenarten die ggf. abgezogen werden.
Weiterhin noch eine Tabelle für Kostenkategorien (Fahrt-, Unterkunfts- und Reisenebenkosten sowie Verpflegungsmehraufwand).
In die Tabelle mit den Kostenarten kommt noch ein Feld für die Kostenkategorie als Fremdschlüssel.
Dann brauchst Du eine Tabelle zum Sammeln der Belege mit einem FS zur Reise und einem FS zur Kostenart. Weiterhin ein Feld für den Betrag und ein Feld für das Datum.
Und dann wird einfach fortlaufend jede Kostenart erfasst.

- Essen
- Einladung (es wird der entsprechende Abzug als Kosten eingetragen)
- Parkgebühren
- Taxikosten
- Gepäckaufbewahrung
usw. usw.
Alles was abgezogen werden muss, wird mit einem negativen Betrag erfasst.
Die Kostenart beschreibt was es ist. Ein Bemerkungsfeld kann weitere Erläuterunegen anzeigen.

Einfach fortlaufend alle angefallen Kosten erfassen. Das kann völlig durcheinander sein, wie es Dir gerade einfällt.
Über die Kategorie und die ID der Reise lässt sich ein Bericht erstellen der die Reisekosten sauber geordnet auflistet.

So habe ich vor langen Jahren mit Access97 schon Reisekostenabrechnungen gemacht.
Habe das Programm aber leider nicht mehr.

Wenn Du ernsthaft Interesse hast, mache ich Dir morgen mal ein Grundgerüst.

 

Ja, habe ich mir angeschaut. Funktioniert auch ganz gut. Nur besteht genau das Problem, was in meiner letzten Nachricht beschrieben habe. Wenn ich feststelle, dass ich mich bei den Daten der Übernachtung im Hotel Adler vertan habe, z.B. Anreise am 11.07. statt 10.07., dann bleibt der Eintrag für den 10.07. bei den Verpflegungsmehraufwendungen bestehen, was nicht korrekt wäre und ungültig ist.

Da hätte ich sehr großes Interesse dran.