VBA-Code Las-t Out > First-In Methode

[Will1974]

Liebe Programmierfreunde,

ich stehe vor einem für mich unlösbarem Problem, welches aber für einige von Euch (so hoffe ich) simpel sein dürfte.

Folgende Situation:
Es gibt in meiner Datenbank (Access 365) die Tabelle "tbl_Lagertabelle" und die dazugehörige Abfrage qry_Lagertabelle. Die Abfrage qry_Lagertabelle  hat die relevanten Felder "Artikel", "LagerID", "Lieferdatum", "Anzahl" und "Rest".

Über einen VBA-Code realisiere ich quasi eine "First-in - First-Out" Routine. D.h. anhand der Felder "LagerID" und "Lieferdatum" erfolgt eine Entnahme von Artikeln immer vom Zugang des ältesten Datensatzes. Die Übergabe der Artikel erfolgt über ein Formular via Listenfeld mit folgendem Code:

Code [Auswählen] Erweitern

For i = 0 To lst_lagerausgang.ListCount - 1
Call ausbuchen(lst_lagerausgang.Column(0, i), lst_lagerausgang.Column(1, i), txt_ausgangsnummer)
Next i

In folgendem Beispiel sind 3 Zugänge mit insgesamt 30 Stück in der Tabelle tbl_Lagertabelle vorhanden:

LagerID  | Lieferdatum | Anzahl | Rest
120        | 01.02.2023   | 15         | 15
121        | 12.02.2023   | 10         | 10
122        | 15.02.2023   | 5           | 5

Entnehme ich nun 27 Stück, werden die Werte über meinen VBA-Code wie folgt geändert:

LagerID | Lieferdatum | Anzahl | Rest
120       | 01.02.2023    | 15        | 0
121       | 12.02.2023    | 10        | 0
122       | 15.02.2023    | 5          | 3

Mein Code dazu sieht wie folgt aus und funktioniert wunderbar:

Code [Auswählen] Erweitern

Public Sub ausbuchen(ByVal Artikel As String, ByVal Anzahl As Integer, ByVal ausgangnr As Integer)

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim sql As String

sql = "Select * FROM qry_Lagertabelle WHERE Artikel = '" & Artikel & "' AND Rest > 0 ORDER BY Lieferdatum, LagerID;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

Dim stueck() As Integer, zugangnr() As Integer
Dim i As Integer
    i = -1

Do While Anzahl > 0
    i = i + 1
ReDim Preserve stueck(i)
ReDim Preserve zugangnr(i)
rs.Edit

If rs!Rest - Anzahl < 0 Then
    stueck(i) = rs!Rest
    zugangnr(i) = rs!LagerID
    Anzahl = Anzahl - rs!Rest
    rs!Rest = 0
    rs.Update
    rs.MoveNext
Else
    rs!Rest = rs!Rest - Anzahl
    stueck(i) = Anzahl
    zugangnr(i) = rs!LagerID
    Anzahl = 0
    rs.Update

End If
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

Ich möchte nun eine Artikelrückgabe nach dem Prinzip Last-Out - First-In realisieren, d.h. gibt jemand von den 27 entnommenen Artikel wieder 22 zurück, sollen sich die Werte vom Neuesten zum Ältesten Zugang wie folgt ändern:

LagerID | Lieferdatum | Anzahl | Rest
120       | 01.02.2023    | 15       | 8
121       | 12.02.2023    | 10       | 10
122       | 15.02.2023    | 5         | 5

Zusätzlich soll, wenn die Artikelrückgabe die verfügbaren Zugänge überschreitet, ein neuer Zugangsdatensatz angelegt werden.

Ich beiße mir seit Tagen daran die Zähne aus und bekomme es einfach nicht hin, kann mir bitte jemand dabei helfen?

LG aus Österreich!

[markusxy]

Ich beiße mir seit Tagen daran die Zähne aus

Dann zeig doch mal was du bis jetzt geschafft hast und erkläre woran dein Versuch scheitert.

Und vielleicht erklärst du auch mit ein zwei Sätzen, was der Sinn des ganzen ist.
Irgendwie ist da für mich kein Sinn erkennbar.

[ebs17]

Ich möchte nun eine Artikelrückgabe nach dem Prinzip Last-Out - First-In realisieren, d.h. gibt jemand von den 27 entnommenen Artikel wieder 22 zurück, sollen sich die Werte vom Neuesten zum Ältesten Zugang wie folgt ändern:

Aus dem Kalten heraus ist das Blödsinn.

Denkbar: Wenn Dein Abnehmer die unveränderten Packungen vom 15.02. und 12.02. zurückgibt, brauchst Du sie nur dort einordnen und die restlichen Stück zum 01.02.
Und dann wird Dir mehr zurückgegeben, als Du ursprünglich hattest?

Was sind das für Artikel? Für Dein gewünschtes Prinzip brauchst Du eigentlich eine Einzelidentifizierung pro Artikelstück. Damit kann man dann leicht einen Eingang und einen Ausgang sowie ein Mindestgebrauchsdatum verwalten. Nach letzterem würde man wohl eine Ausgabe steuern, dann per sehr leichter Abfrage (TOP X).

Dreißig Datensätze statt drei, das hört sich nicht aufregend an.

[Will1974]

@markusxy: Mein Code sieht bis jetzt so aus:

Code [Auswählen] Erweitern

Public Sub einbuchen_neu(ByVal Artikel As String, ByVal Anzahl As Integer, ByVal eingangnr As Integer)

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim sql As String

sql = "Select * FROM qry_Lagertabelle WHERE Artikel = '" & Artikel & "' ORDER BY Lieferdatum DESC, LagerID;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

Dim stueck() As Integer, zugangnr() As Integer ', Differenz As Integer
Dim i As Integer
    i = -1

Do While Anzahl > 0
    i = i + 1
   
ReDim Preserve stueck(i)
ReDim Preserve zugangnr(i)

rs.Edit

    rs!Rest = rs!Rest + rs!Differenz
    stueck(i) = Anzahl
    zugangnr(i) = rs!LagerID
    Anzahl = Anzahl - rs!Rest
    rs.Update
    rs.MoveNext

Loop

rs.Close
Set rs = Nothing

usw....

Ausgangsdaten in qry_Lagertabelle:

LagerID | Lieferdatum | Anzahl | Rest
163       | 27.02.2023    |  5         |    0
164       | 01.03.2023    |  3         |    0
345       | 22.03.2023    |  12       |    4

Zugang im Formular via Listenfeld = 13 Stück, das Ergebnis ist nach durchlaufen meines Codes wie folgt:

LagerID | Lieferdatum | Anzahl | Rest
163    | 27.02.2023       |  5         |    0
164    | 01.03.2023       |  3         |    3
345    | 22.03.2023       |  12       |  12

Wie man sieht werden statt 13 nur 11 Stück eingebucht, 2 Stück gehen also verloren 

Aussehen müsste das Ergebnis eigentlich so:

LagerID | Lieferdatum | Anzahl | Rest
163    | 27.02.2023       |  5         |    2
164    | 01.03.2023       |  3         |    3
345    | 22.03.2023       |  12       |  12

Irgendwo in der Berechnung habe ich einen Fehler und finde den seit Tagen nicht 

Der Sinn des ganzen ist etwas schwierig zu beschreiben, aber ich versuche es mal...

Es gibt ein Lager mit Baumaterialien (Sand, Kleber, Flocken, Harze usw.).
Es gibt viele gleichzeitige Baustellen, wodurch viele Mitarbeiter viele unterschiedliche Materialien aus dem Lager entnehmen.
Manchmal wird das Restmaterial am nächsten Arbeitstag zurückgebracht, manchmal erst nach Fertigstellung der Baustelle nach Monaten.
Dadurch entstehen gewisse Differenzen und Unstimmigkeiten im Lager, z.B. durch Nachlässigleit der MA, falsche Inventierung, Buhungsfehler o.ä.
Dadurch kann es, auch wenn es unwahrscheinlich erscheint, vorkommen, dass auch mal mehr Artikel zurückgebracht werden als entnommen wurden.
Ich möchte dafür jetzt nicht unbedingt eine Inventurfunktion einbauen, es sollte für die Mitarbeiter einfach bedienbar bleiben.

Im Grunde möchte ich es nicht kompliziert machen, ich würde nur gerne einen groben Überblick bewahren, wie mein aktueller Lagerbestand und der Warenwert ist. First-In > First Out wende ich an, weil meine Materialien zum Teil Ablaufdatum haben und ich annehme, dass die Mitarbeiter beim Laden zuerst das alte Material verwenden. Bei der Rückgabe unterstelle ich, dass das neueste Material mit dem längsten Ablaufdatum zurückkommt.

Ich möchte keine Wissenschaft aus meinem Programmchen machen, "Keep it simpel" lautet die Devise  .

Ich glaube, dass ich im Grunde ganz nahe an der Lösung bin, aber dieser Rechenschritt fuxt schon seit Tagen, irgendwo habe ich mich in einen Denkfehler verbissen und komm da nicht mehr raus. Daher hoffe ich auf Eure Hilfe.

@ebs17: Mein Beispiel bezieht sich nur auf einen einzigen Artikel, jede LagerID ist eine Bestellung, welche ich immer selbst erfasse. Bei einer neu eingebuchten Bestellung entspricht der Wert des Feldes "Rest" immer dem Wert des Feldes "Anzahl". Unter den Bestellungen gibt es dann die Lagerbewegungen (also mein Beispiel), welche die Artikelentnahmen- und Rückgaben der Mitarbeiter durch das Änderen des Feldwertes "Rest" darstellen. D.h. es wären mit deinem Vorschlag also sehr viele Datensätze mehr. Aber natürlich hat auch jeder Artikel eine eindeutige ArtikelID (Autowert), welche ich für meinen Code-Durchlauf aber nicht unbedingt benötige.

Ich hoffe, dass meine Beschreibung soweit schlüssig ist. Wahscheinlich gäbe es auch andere Wege, aber wie sagt man so schön...tausend Wege führen nach Rom. Vielen Dank Euch Beiden, dass ihr mir bei der Lösung dieses Debakels helfen möchtet.

[markusxy]

Code [Auswählen] Erweitern

rs!Rest = rs!Rest + rs!Differenz
...
Anzahl = Anzahl - rs!Rest[/quote]


Also der Fehler ist recht offensichtlich.
Bei Rest steht ja schon mal der Wert 4.
Den Wert ziehst du dann einfach von der Anzahl ab, obwohl die ja gar nicht zurückgegeben wurden.

Abgesehen davon prüfst du gar nicht, ob Differenz > ist als die Rückgabe.
Den Code solltest du noch mal durchdenken.

[Will1974]

@markusxy:

Hmmm...ich seh den Wald vor lauter Bäumen nicht  .
Stimmt, in rs!Rest steht bei LagerID 345 der Wert 4...im berechneten Tabellenfeld rs!Differenz steht 8 und im Feld rs!Anzahl steht 12.
rs!Rest = 4 + 8 ergibt also 12...das ist doch richtig oder nicht?

Dann soll der Datensatz mit der LagerID 164 durchlaufen werden...hier steht bei rs!Anzahl 3, bei rs!Rest der Wert 0 und im Feld rs!Differenz 3
rs!Rest = 0 + 3 ergibt also 3...Das stimmt auch noch.

Und dann der Datensatz mit der LagerID 163...der bleibt null weil nix mehr übrig bleibtist...Der Hund ist also hier begraben: Anzahl = Anzahl - rs!Rest

Ich habe aber schon alle möglichen Variant mit Anzahl durchprobiert und alle liefern ein falsches Ergebnis .
Anzahl entspricht doch dem Wert Rückgabe (nicht zu verwechseln mit rs!Anzahl, das ist das Feld "Anzahl" in qry_Lagertabelle)?

Kennst du die Lösung und wie meinst du das mit der Prüfung Differenz > Rückgabe?

[MzKlMu]

Hallo,

Im Grunde möchte ich es nicht kompliziert machen, ich würde nur gerne einen groben Überblick bewahren, wie mein aktueller Lagerbestand und der Warenwert ist.

Unter diesen Bedingungen halte ich das Vorhaben für viel zu umständlich und kompliziert.
Ich würde alle Materialbewegungen in einer Tabelle erfassen.

Code [Auswählen] Erweitern

MaterialID   Menge    Ablaufdatum      BuchDatum        VgArt
     1        120     12.07.2023       23.02.2023          1
     1         50     12.07.2023       25.02.2023          2
     1         20     12.07.2023       25.02.2023          3
     2 ......
     3 ......
VgArt = Vorgangsart 1=Einkauf, 2=Ausgabe  3=Rückgabe
Über die Vorgangsart lässt sich ein Multiplikator einbauen der die Menge multipliziert, für Ausgang mit -1 und die andern mit +1.
Wenn man die mit dem Multiplikator multiplizierte Menge summiert, hat man automatisch den Bestand.
Für die Summierung kann nach ArtikelID oder nach ArtikelID und Ablaufdatum gruppiert werden.
Immer automatisch aktuell. Und wenn Du in der Artikeltabelle ein Feld hast für den aktuellen Preis des Artikels, kannst Du mit dieser gruppierten Abfrage auch gelich den aktuellen Warenwert ermitteln.
Das alles ohne auch nur einen Buchstaben zu programmieren, nur die einfachsten Bordmittel von Access. Die Differenz und Resteberechnung entfällt auch ersatzlos.

[Will1974]

@MzKlMu:

Danke für deinen Beitrag.

Wie ich schon sagte 1000 Wege führen nach Rom und mir ist durchaus bewusst, dass ich die Datenbank auch nach deinem Schema hätte aufbauen können.

Ich habe Variante gewählt, weil ich zum einen nicht jeden Tag hunderte neue Datensätze erzeugen möchte und zum anderen auch neben dem Lagerbestannd so auch immer den richtigen Waenwert bekomme. Schließlich varriieren die Einkaufspreise in Zeiten wie diesen.

Deine Anleitung ist zwar gut und absolut richtig, dennoch möchte ich auf meinem Weg bleiben. Kannst du mir mit der Berechnung weiterhelfen?

[Will1974]

@markusxy:

Code [Auswählen] Erweitern

rs!Rest = rs!Rest + rs!Differenz
...
Anzahl = Anzahl - rs!Rest[/quote]


Also der Fehler ist recht offensichtlich.
Bei Rest steht ja schon mal der Wert 4.
Den Wert ziehst du dann einfach von der Anzahl ab, obwohl die ja gar nicht zurückgegeben wurden.

Abgesehen davon prüfst du gar nicht, ob Differenz > ist als die Rückgabe.
Den Code solltest du noch mal durchdenken.

Ich habe den Code überarbeitet und habe folgendes (diesmal ohne rs!Differenz).

Code [Auswählen] Erweitern

...

If rs!Rest + Anzahl < 0 Then
    stueck(i) = rs!Rest
    zugangnr(i) = rs!LagerID
    Anzahl = Anzahl + rs!Rest
    rs!Rest = 0
    rs.Update
    rs.MoveNext
Else
    rs!Rest = rs!Rest + Anzahl
    stueck(i) = Anzahl
    zugangnr(i) = rs!LagerID
    Anzahl = 0
    rs.Update
...


Ausgangssituation:

LagerID | Lieferdatum | Anzahl | Rest
163        | 27.02.2023   |    5       |     0
164        | 01.03.2023   |    3       |     0
345        | 22.03.2023   |  12       |     4


Ergebnis bei einer Zubuchung von 13:

LagerID | Lieferdatum | Anzahl | Rest
163        | 27.02.2023   |    5       |     0
164        | 01.03.2023   |    3       |     0
345        | 22.03.2023   |  12       |   17

Die zugebuchte Stückzahl stimmt jetzt (4 + 13 = 17), allerdings wird diese jetzt im Gesamten nur bei letzten Datensatz (LagerID 345) zugebucht :-(

Das Ergebnis sollte aber eigentlich so aussehen:

LagerID | Lieferdatum | Anzahl | Rest
163        | 27.02.2023   |    5       |     2
164        | 01.03.2023   |    3       |     3
345        | 22.03.2023   |  12       |   12

Irgendwas stimmt noch nicht, hast du eine Idee was jetzt noch falsch ist?

[ebs17]

Irgendwas stimmt noch nicht

Wenn ich nur die Situation betrachte: Einem Lagerort sollte nur die Differenz Anzahl - Rest zubuchbar sein. Also müsste die Gesamtzubuchung sequentiell aufgeteilt werden (mit offenen Ausgang für den am frühesten belegten Lagerort).

[Will1974]

@ebs17 :
Danke für deinen Tipp, die Theorie würde ich verstehen, aber wie kann ich das im Code umsetzen?
Die Ausbuchung funktioniert ja fast auf selben Weg, ganz ohne sequentielle Splittung der Gesamtausbuchung.

Dafür fehlt mir leider die notwendige VBA-Erfahrung.

[markusxy]

Hier mal ein simpler Ansatz, der dein Modell fortführt.
Trotzdem würde  ich das generell überdenken.

Code [Auswählen] Erweitern

Do While Anzahl > rs!Differenz
    rs.Edit
    rs!Rest = rs!Anzahl
    Anzahl = Anzahl - rs!Differenz
    rs.Update
    rs.MoveNext
Loop
If Anzahl then
    rs.Edit
    rs!Rest = rs!Rest + Anzahl
    rs.Update
end if


[MzKlMu]

Hallo,

Ich habe Variante gewählt, weil ich zum einen nicht jeden Tag hunderte neue Datensätze erzeugen möchte

Meine Version erzeugt nicht mehr DS warum auch ?

und zum anderen auch neben dem Lagerbestannd so auch immer den richtigen Waenwert bekomme. Schließlich varriieren die Einkaufspreise in Zeiten wie diesen.

Für den Warenwert spielen die Einkaufspreise eigentlich keine Rolle. Der Warenwert ergibt sich aus dem aktuellen Preis. Wenn Du 10 Aktien zu 100.-€ kaufst und die Aktie dann auf 110.-€ steigt, sind Deine Aktien 1100.-€ wert und keine 1000.-€.

Wenn Du wirklich die Einkaufspreise verwenden willst, muss aber eine Rückgabe auch wieder dem Einkaufsvorgang zugeordnet werden, damit der richtige Preis berücksichtigt wird.
Ich halte das für nicht umsetzbar, besonders wegen der Eingabe der Rückgaben durch die Mitarbeiter.

Ich bin nach wie vor der Meinung eine Umgestaltung der DB wäre besser, zumal da eigentlich kein VBA benötigt wird.
Ist aber Deine Sache. Ich habe nichts weiter zu sagen.

[Will1974]

@markusxy:

Hier mal ein simpler Ansatz, der dein Modell fortführt.
Trotzdem würde  ich das generell überdenken.

Code [Auswählen] Erweitern

Do While Anzahl > rs!Differenz
    rs.Edit
    rs!Rest = rs!Anzahl
    Anzahl = Anzahl - rs!Differenz
    rs.Update
    rs.MoveNext
Loop
If Anzahl then
    rs.Edit
    rs!Rest = rs!Rest + Anzahl
    rs.Update
end if


Vielen Dank, aber dein Code funktioniert leider nicht, ich bekomme "Kein aktueller Datensatz".

[Will1974]

@MzKlMu:

Wie sollten denn die Datenbank Beziehungen deiner Meinung nach aussehen? Momentan habe ich 3 Tabellen.

tbl_Lagertabelle enthält alle Bestellungen, tbl_Artikel enthält alle Artikel und tbl_Lagerbewegung alle Entnahmen und Rückgaben der Mitarbeiter.

Die Tabelle tbl_Lagertabelle steht über den Fremdschlüssel ArtikelID in einer 1:n Beziehung mit dem Primärschlüssel ArtikelID der Tabelle tbl_Artikel. Die Tabelle tbl_Lagerbewegung steht über den Fremdschlüssel Aus_Zugangnr in einer 1:n Beziehung mit dem Primärschlüssel LagerID der Tabelle tbl_Lagertabelle.