DLookup auf aktuellen Datensatz

[sellrich]

Hello,

ich habe folgenden IST-Zustand :

Code [Auswählen] Erweitern

Private Sub KS_AfterUpdate()
KSvar = DLookup("[Kategorie]", "KS")
KSAnzahl = DLookup("[Anzahl]", "KS")
KSvar2 = KSvar & KSAnzahl
Me.Fld_KSNummer.Value = KSvar2
KSAnzahl = KSAnzahl + 1
KSDrck = KSvar & KSAnzahl
Me.Fld_Etikettendruck.Value = KSDrck
End Sub

Private Sub ButDrckEtikett_Click()
DoCmd.RunSQL "INSERT INTO KS (Kategorie, Anzahl) VALUES ('" & KSvar & "', '" & KSAnzahl & "');"
End Sub

An sich klappt das so wie ich es haben möchte. Jetzt will ich aber bei erneuter Aktualisierung des Feldes "KS" dass sich das DLookup auf den aktuellen und nicht den Ersten Datensatz bezieht.

[sellrich]

Problem ist anders gelöst. Da es nicht notwendig ist die Daten in der Tabelle zu behalten mache ich statt INSERT einfach UPDATE und überschreibe immer den Datensatz. Nur ein Datensatz vorhanden = DLookup klappt wieder.

Aber falls jemand einen professionelleren Lösungsweg hat würde ich mich sehr drüber freuen.

[ebs17]

einen professionelleren Lösungsweg

... würde man von einer bekannten Situation in Ausgangsbasis und Zielstellung sowie von einem bekannten und stimmigen Datenmodell ableiten, nicht unbedingt von einem dahin geworfenen Bruchstück von einem schon selbsterkannt unoptimalen Weg (für alle, die nicht an Hellseherei glauben).

[sellrich]

Hmm dann werde ich das ganze mal etwas anders formulieren.
@Eberhard du bist bisher immer sehr kritisch und direkt zu mir gewesen. Das finde ich sehr gut. Nur so erkenne ich häufiger wie schlecht ich Teilweise darin bin mein Problem zu schildern. Dann nochmal ein Versuch :

Aufgabenstellung : Ich soll ein Access-Tool aufziehen das relativ einfach eine Kombination von Zeichen und einer Zahlenabfolge (immer um eins erhöht) als QR-Code ausdruckt. Zeichenfolge zb: KS1000000001.
Da diese Abfolge nicht langfristig gespeichert werden muss, speichere ich diese zunächst einmalig in eine Tabelle "KS" und "AU". Mit den Spalten Kategorie "KS" und Anzahl "1000000001".

Durch auswählen der Kategorie erscheint die zuletzt verwendete Nr. Die baut sich aus 2 DLookups auf die beiden Spalten, welche ich in eine Variable speichere. Daraus baut sich dann im VBA die neue Nr. zusammen. Per Button wird diese dann an den Etikettendrucker gejagt und in die Tabelle geschrieben (damit bei nächster Aktualisierung durch Knopfdruck oder neu Auswahl der Kategorie die neue Nr generiert wird.) Als Maßstab wird dafür das DLookup verwendet.

Ich habe hier mal ein Beispiel in den Anhang gepackt.

EDIT: Stehe sogar schon vor dem nächsten Problem. Ich würde gerne die Prozedur, die ausgeführt wird, wenn ich auf den Button "Drucken" drücke, häufiger hintereinander ausführen. Ich stelle mir das so vor, dass ich über das Feld ein Feld hinmache in dem man die Anzahl an ausdrucke eingibt. z.B 30x.
Dann müsste man einmal auf "Drucken" drücken und die entsprechende Prozedur wird 30x ausgeführt.

[markusxy]

Hast du zufällig einen Server als Backend?
Die liefern da in der Regel eine Funktion um eine Reihe zu erzeugen.
Dann geht das in einer simplen Abfrage.

[ebs17]

Bei so viel Einsicht bin ich ja quasi genötigt zu antworten.

Zum allgemeinen Verständnis: Ein DLookup ist eine vollständige Abfrage, gekapselt in einer Access-Funktion, die genau einen Wert abholt. Bei Dauerfeuer wäre das etwa so, als wenn man für eine zünftige Fete jede Flasche Wasser, Wein, Bier immer einzeln aus dem Getränkemarkt holen würde. Wer täte so etwas im vollen Bewusstsein ohne Not?

Verarbeitung beginnt mit datenbankgemäßen Tabellen. Gleiche Informationen gehören in ein Feld einer Tabelle. Denkbarer Aufbau (bis hierhin):

Code [Auswählen] Erweitern

Kategorie   Kennung     Anzahl      ErstelltAm      Zweck
AU          1           2           04.02.2019      Kuchen
AU          1           27          10.02.2019      Törtchen
KS          1           20          11.02.2019      Brote
AU          1           12          15.02.2019      Kekse
Dazu würde man sich einmalig eine Zahlenhilfstabelle erzeugen, die dann abfragetechnisch das (Weiter)Zählen übernimmt. Das ist  eine Tabelle mit einer einzigen Spalte I, die die fortlaufenden Zahlen von 0 bis X enthält. Ich verwende hier eine T999, die für sehr viele Verwendungen ausreicht.

Jetzt kann man eine Abfrage erzeugen über alles (SQL-Ansicht):

Code [Auswählen] Erweitern

PARAMETERS
   parKategorie TEXT,
   parNeueAnzahl INT
;
SELECT
   D.Kategorie & D.Kennung & Format(T.I, "000000000") AS Code
FROM
   tblDaten AS D,
   T999 AS T
WHERE
   D.Kategorie = parKategorie
      AND
   T.I BETWEEN
      (
         SELECT
            SUM(Anzahl)
         FROM
            tblDaten
         WHERE
            Kategorie = parKategorie
      )
   + 1
      AND
   (
         SELECT
            SUM(Anzahl)
         FROM
            tblDaten
         WHERE
            Kategorie = parKategorie
      )
   + 1 + parNeueAnzahl
Das ist eine Parameterabfrage, die als Eingabeparameter die zu verwendende Kategorie sowie die neue Anzahl an auszudruckenden Etiketten erwartet. Anwendung wie in Parameterabfrage per VBA öffnen
Das erzeugte Recordset kann man direkt einem Bericht als Datenherkunft zuweisen, bei eingestellten Etiketten sollten dann alle gewünschten sofort zur Verfügung stehen.

Nach erfolgtem erfolgreichen Druck wäre dann die obige tblDaten um einen Datensatz zu ergänzen mit der eben verwendeten Kategorie und der Anzahl (und einigen weiteren sinnvollen Informationen) => Anfügeabfrage.
Auch wenn das "angeblich" nicht nachhaltig gespeichert werden soll, so wage ich zu behaupten, eine solche schlanke Dokumentation wird sich noch als nützlich erweisen.

Über ein Formular würde man die Eingabe/Auswahl der Parameter erledigen und dann auch den Button für den Start der Prozedur und schlussendlich eine Endmeldung anbieten.

Das Ganze ungetestet, weil aus der Abstraktion heraus geschrieben.

[sellrich]

Hast du zufällig einen Server als Backend?
Die liefern da in der Regel eine Funktion um eine Reihe zu erzeugen.
Dann geht das in einer simplen Abfrage.

Leider nicht.

@ebs17

Zum allgemeinen Verständnis: Ein DLookup ist eine vollständige Abfrage, gekapselt in einer Access-Funktion, die genau einen Wert abholt. Bei Dauerfeuer wäre das etwa so, als wenn man für eine zünftige Fete jede Flasche Wasser, Wein, Bier immer einzeln aus dem Getränkemarkt holen würde. Wer täte so etwas im vollen Bewusstsein ohne Not?

Das erwähntest du bereits schon mal. Ich weiß das. Deswegen versuche ich seit einem Weilchen deine Vorgehensweise zu verstehen und umzusetzen! Vielen Dank vorab schon mal für die Mühe und gleichzeitig verzeih meine Unwissenheit 

Verarbeitung beginnt mit datenbankgemäßen Tabellen. Gleiche Informationen gehören in ein Feld einer Tabelle. Denkbarer Aufbau (bis hierhin):

Code [Auswählen] Erweitern

Kategorie   Kennung     Anzahl      ErstelltAm      Zweck
AU          1           2           04.02.2019      Kuchen
AU          1           27          10.02.2019      Törtchen
KS          1           20          11.02.2019      Brote
AU          1           12          15.02.2019      Kekse
Dazu würde man sich einmalig eine Zahlenhilfstabelle erzeugen, die dann abfragetechnisch das (Weiter)Zählen übernimmt. Das ist  eine Tabelle mit einer einzigen Spalte I, die die fortlaufenden Zahlen von 0 bis X enthält. Ich verwende hier eine T999, die für sehr viele Verwendungen ausreicht.

Das habe ich hinbekommen. Danke!

Das ist eine Parameterabfrage, die als Eingabeparameter die zu verwendende Kategorie sowie die neue Anzahl an auszudruckenden Etiketten erwartet. Anwendung wie in Parameterabfrage per VBA öffnen

Das bereitet mir etwas Schwierigkeiten.

[...]
'hier folgt brillanter Code, der mit dem Recordset arbeitet
Ich glaube hier bräuchte ich nochmal etwas Hilfe. Habe soweit alles umgesetzt und auch das füllen der Paramter hat geklappt.
Aber wie verfahre ich weiter? Wie kann ich das Recordset jetzt einem Bericht zuordnen? Das Recordset existiert ja nur temporär??
'am Ende so tun, als wär nix gewesen:
qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
Set db = Nothing

Nach erfolgtem erfolgreichen Druck wäre dann die obige tblDaten um einen Datensatz zu ergänzen mit der eben verwendeten Kategorie und der Anzahl (und einigen weiteren sinnvollen Informationen) => Anfügeabfrage.

Ich schätze ich nehme hier dann wieder das Recordset als Datenherkunft? Vermutlich klärt sich die Frage von selbst, wenn ich weiß wie obiges zu lösen ist.


EDIT :

Das ist eine Parameterabfrage, die als Eingabeparameter die zu verwendende Kategorie sowie die neue Anzahl an auszudruckenden Etiketten erwartet.

Das klappt ebenfalls super. Ich habe (mal zum testen) die Datensatzherkunft des Berichts auf die Abfrage gelegt und händisch die Parameter eingeben. Das funktionierte bis auf eine kleine Sache. Wenn ich bei "parNeueAnzahl" 2 eingebe druckt er 3 Etiketten. Ich habe die Abfrage schon versucht selbst anzupassen, jedoch bisher ohne Erfolg.

[ebs17]

Der Bericht braucht zur Einrichtung eine Datenherkunft. Hier könnte man ein schlankes Dummy verwenden:

Code [Auswählen] Erweitern

SELECT
   "X" AS Code
FROM
   tblDaten AS D
WHERE
   1 = 0
Beim Öffnen des Berichtes bekommt dieser das mit dem brillanten Code erzeugte Recordset zugewiesen:

Code [Auswählen] Erweitern

Set Reports.DeinReport.Recordset = rs

Für das datensatzanfügen kannst Du die gleichen Parameter verwenden. Das würde etwa so aussehen können:

Code [Auswählen] Erweitern

    Dim qd As DAO.QueryDef
    Dim sSQL As String
    sSQL = sSQL = "PARAMETERS parKategorie TEXT, parNeueAnzahl INT; " & _
           " INSERT INTO tblDaten(Kategorie, Anzahl, ErstelltAm) " & _
           " VALUES(parKategorie, parNeueAnzahl, Date())"
    Set qd = CurrentDb.CreateQueryDef("", sSQL)
    With qd
        '.Parameters ....
        '.Execute
        '.Close
    End With

[sellrich]

Der Bericht braucht zur Einrichtung eine Datenherkunft. Hier könnte man ein schlankes Dummy verwenden:

Code [Auswählen] Erweitern

SELECT
   "X" AS Code
FROM
   tblDaten AS D
WHERE
   1 = 0

Das simpel in Daten -> Datensatzquelle eingeben? 1:1?

Beim Öffnen des Berichtes bekommt dieser das mit dem brillanten Code erzeugte Recordset zugewiesen:

Code [Auswählen] Erweitern

Set Reports.DeinReport.Recordset = rs

"Objekt unterstützt diese Eigenschaft oder Methode nicht"
Für mein Verständnis - die Zeile kommt aber schon mit in den Button rein indem ich das ganze Prozedere auch durchführe? Da du ja schreibst "Beim öffnen des Berichts" dachte ich erst an ein neues Ereignis. Aber das wäre ja nicht richtig, oder?
Lerne gerade bei so nem "kleinen" Toolchen mehr als in den letzten 2 Monaten. Danke!
Aktuell sieht es so aus :

Code [Auswählen] Erweitern

Private Sub ButDrckEtikett_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Abfrage1")
qdf.Parameters!parKategorie = [Form]![Fld_Kat]
qdf.Parameters!parNeueAnzahl = [Form]![Fld_Etikettendruck]

Set rs = qdf.OpenRecordset(dbOpenDynaset)
'##hier folgt brillanter Code, der mit dem Recordset arbeitet##
Set Reports.Etiketten.Recordset = rs '-> Hier habe ich auch Reports("Etiketten").Recordset versucht. Da sagt er mir, dass der Bericht Etiketten nicht vorhanden sei.
' DoCmd.OpenReport "Etiketten", acViewNormal '-> Drucken

' ##am Ende so tun, als wär nix gewesen:
qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

Für das datensatzanfügen kannst Du die gleichen Parameter verwenden. Das würde etwa so aussehen können:

Code [Auswählen] Erweitern

    Dim qd As DAO.QueryDef
    Dim sSQL As String
    sSQL = sSQL = "PARAMETERS parKategorie TEXT, parNeueAnzahl INT; " & _
           " INSERT INTO tblDaten(Kategorie, Anzahl, ErstelltAm) " & _
           " VALUES(parKategorie, parNeueAnzahl, Date())"
    Set qd = CurrentDb.CreateQueryDef("", sSQL)
    With qd
        '.Parameters ....
        '.Execute
        '.Close
    End With

Da bin ich etwas überfragt?
Bei '.Paramters gebe ich zb.
parNeueAnzahl.Paramters
ein?

[ebs17]

Für mein Verständnis - die Zeile kommt aber schon mit in den Button rein indem ich das ganze Prozedere auch durchführe?

So würde ich das denken, und noch etwas mehr.

Überlege Dir zusammenfassend, was alles erfolgen muss oder soll, dann nach einem Buttonklick.
Der Button befindet sich hoffentlich in einem schönen übersichtlichen Formular, wo man weiß, was man tut, und ehe man klickt, hat man die Möglichkeit und Notwendigkeit, die benötigten Parameter zu erfragen und zu erfassen, gern mit Unterstützung durch vorbereitete Auswahl.

Und dann setzt Du die Einzelaktionen, nur solche hatte ich jeweils angedeutet, zu einem stimmigen Ganzen zusammen.

Das simpel in Daten -> Datensatzquelle eingeben?

Einfach eine superschlanke Tabelle/Abfrage mit den nötigen Feldern, daten muss sie keine haben, Datenmasse schon mal gar nicht.
So hat der Bericht jederzeit eine Datenherkunft, und diese würde dann an geeigneter Stelle (vor dem Druck ...?) ersetzt durch das, was man sehen will.
Folge dem gesunden Menschenverstand.

[sellrich]

Das habe ich soweit verstanden.
Die Übergabe der Parameter klappt. Das übersichtliche Formular ist vorhanden. Mit vorgegebenen Werten, sodass Fehler durch falsche Eingaben verhindert werden. Das Drucken der Etiketten mit den Dummydaten klappt auch.

Lediglich das "set Reports.MeinBericht.Recordset = rs" funktioniert nicht.
Laufzeitfehler '438':
Objekt unterstützt diese Eigenschaft oder Methode nicht

Versuche ich "set Reports("Etiketten").Recordset = rs" kommt:
Laufzeitfehler '2451':
Der Berichtsname 'Etiketten', den Sie eingegeben haben, ist entweder falsch geschrieben oder verweist auf einen Bericht, der nicht geöffnet oder nicht vorhanden ist.

Öffne ich den Bericht mal manuell rein zum testen kriege ich erneut einen Fehler:
"Diese Funktion ist nur in ADP verfügbar"

Hier mein bisheriger Code :

Code [Auswählen] Erweitern

Private Sub ButDrckEtikett_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Abfrage1")
qdf.Parameters!parKategorie = [Form]![Fld_Kat]
qdf.Parameters!parNeueAnzahl = [Form]![Fld_Etikettendruck]

Set rs = qdf.OpenRecordset(dbOpenDynaset)
set Reports.MeinBericht.Recordset = rs
DoCmd.OpenReport "Etiketten", acViewNormal '-> Drucken

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
Set db = Nothing

End Sub

Um das Anfügen der Daten würde ich mich kümmern, wenn die Daten richtig gedruckt werden.

[ebs17]

Code [Auswählen] Erweitern

set Reports.MeinBericht.Recordset = rs
DoCmd.OpenReport "Etiketten", acViewNormal
Reihenfolge: Ich würde sagen, ohne einen geöffneten Bericht gibt es nichts an ihm zu ändern, außer Du würdest in die Design-Ansicht gehen (unpraktikabel). Die Meldung, die Du bekommst, meint ja das gleiche.

Vielleicht den Bericht erst versteckt in der Vorschau öffnen, Datenherkunft ändern, dann drucken.

[sellrich]

Vielleicht den Bericht erst versteckt in der Vorschau öffnen, Datenherkunft ändern, dann drucken.

Den Gedanken hatte ich auch schon. Sorry habe gedacht , dass ich das vorhin mit rein geschrieben hätte.

Code [Auswählen] Erweitern

DoCmd.OpenReport "Etiketten", acViewPreview, , , acHidden
Set Reports.Etiketten.Recordset = rs
DoCmd.OpenReport "Etiketten", acViewNormal '-> Drucken

So habe ich es versucht. Jedoch leider wieder die gleiche Meldung :

Laufzeitfehler '32585':
Diese Funktion ist nur in ADP verfügbar.

[ebs17]

Hätte ich eine solche DB, hätte ich ja selber probieren können (meine Abstraktion ist nicht ausreichend).

Wenn es so nicht gehen will, gibt es doch aber eine Reihe von Alternativen. Eine: Die Abfrage bekommt die Parameter nicht zugewiesen, sondern sie holt sich diese. Abwandlung:

Code [Auswählen] Erweitern

SELECT
   D.Kategorie & D.Kennung & Format(T.I, "000000000") AS Code
FROM
   tblDaten AS D,
   T999 AS T
WHERE
   D.Kategorie = fctKategorie()
      AND
   T.I BETWEEN
      (
         SELECT
            SUM(Anzahl)
         FROM
            tblDaten
         WHERE
            Kategorie = fctKategorie()
      )
   + 1
      AND
   (
         SELECT
            SUM(Anzahl)
         FROM
            tblDaten
         WHERE
            Kategorie = fctKategorie()
      )
   + 1 + fctNeueAnzahl()
Diese Abfrage kannst Du dem Bericht fest als Datenherkunft zuweisen. Die Funktionen bedienen sich dann an den Formulartextfeldern, womit das startende Formular geöffnet bleiben muss. Eventuell geht man auch über globale Variablen (Variablen in Abfragen verwenden).

[sellrich]

Soweit so gut. Das habe ich hinbekommen.
Der Druck funktioniert an sich auch.

Jetzt habe ich noch folgendes Problem. Schreibe ich in das Formular "1 Etikett" druckt er mir immer 2.
Schreibe ich 4 - druckt er mir 5 usw.
Das ist mit Sicherheit nur ein kleines Ding in der Abfrage. Allerdings weiß ich nicht wo. Wenn ich an einer Stelle z.B die +1 wegmache bringt es nicht den gewünschten Erfolg.

Aber jetzt kann ich mich schon mal um das Speichern in die Tabelle kümmern.
Verwende ich dazu (?) :

Code [Auswählen] Erweitern

DoCmd.RunSQL "INSERT into tblDaten(Kategorie, Anzahl, ErstelltAm) VALUES(fctKategorie, fctNeueAnzahl, Date());


EDIT: Nächstes Mysterium für mich. Wenn ich den oben genannten SQL Befehl nehme erhalte ich danach folgendes :
Drucke ich 1 Etikett erhalte ich ja wie erwähnt 2 Etiketten. Nach dem INSERT führe ich es erneut aus mit 1 Etikett. Allerdings erhalte ich dann 6?! Vorgang ein drittes mal ausführen -> erhalte ich 8 Etiketten. Anscheinend stimmt was mit der Abfrage nicht. Leider weiß ich nicht was. Ich hoffe du kannst mir da nochmals helfen Eberhard?


Könnte auch nochmal eine Beispieldatenbank hochladen - ohne die T999 Tabelle (sonst die die accdb zu groß), wenn dir das hilft?