VBA SQL Update auf mehrere Datensätze

[Purzel862]

Moin, ich hoffe ihr könnt mir weiterhelfen.

Ich habe eine Access DB mit der Klienten, Kliniken in Verbindung mit Rechnungen pflegen möchte. Das Rechnung erstellen ist auch kein Problem, das was mir schon seit Tagen Kopfschmerzen bereitet ist folgendes:

Code (vb) [Auswählen] Erweitern

Private Sub Update()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

Set dbs = CurrentDb

sql = "SELECT * FROM qryTem ;"

Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)

With rst
    Do Until rst.EOF
             sql = "UPDATE [tblBestellpositionen] SET [RechNr] = '" & Me!RechNr & "' WHERE [KlinikID] = " & Me!txtRechnungsdatumVon & " ;"
            dbs.Execute (sql)
        .MoveNext
    Loop
End With
rst.Close
dbs.Close

End Sub


Mit diesem Code aktualisiert er bloß eine Zeile obwohl mehrere Zeilen die gleichen Voraussetzungen erfüllen.
Hat jeman eine Idee???

[PhilS]

Mit diesem Code aktualisiert er bloß eine Zeile obwohl mehrere Zeilen die gleichen Voraussetzungen erfüllen.

Diese konkrete Frage lässt sich anhand des gezeigten Codes nicht beantworten. Ich würde mal den erzeugten SQL String in der Variable sql anschauen (Debug.Print) und evtl. mal in die SQL-Ansicht einer Access-Abfrage kopieren und dort ausführen lassen.

Unabhängig vom konkreten Problem werde ich ziemlich skeptisch, wenn ich deinen Code sehe.

Code [Auswählen] Erweitern

...WHERE [KlinikID] = " & Me!txtRechnungsdatumVon & " ;"

Ist das wirklich so beabsichtigt?

Weiterhin führst du das Update-Sql für jeden Datensatz aus qryTem aus, aber  immer mit den Werten aus dem Formular. Wenn das wirklich so beabsichtigt ist, würde es reichen das SQL einmalig auszuführen.

[Beaker s.a.]

@Purzel862
Zunächst fallen folgende Ungereimtheiten auf.
1. dass die RechNr ein Text ist mag ja noch sein, wenn es dann auch
eine ID dazu gibt
2.

Code [Auswählen] Erweitern

..."' WHERE [KlinikID] = " & Me!txtRechnungsdatumVon
macht aber stutzig. Was hat ein Datum in einem ID-Feld zu suchen?
gruss ekkehard


P.S. da OT

Code [Auswählen] Erweitern

dbs.Close
Auch wenn VBA das geflissentlich "übersieht", kannst du dbs nicht schliessen,
da es nur eine Referenz auf CurrentDb ist und keine per Code (OpenDatabase)
geöffnete DB. Du kannst also nur die Referenz killen.

Code [Auswählen] Erweitern

rst.Close
Set rst = Nothing
Set dbs = Nothing

edit: ups, hat sich überschnitten

[Purzel862]

Vielen Dank für die Antworten:
Nochmal zur Erklärung: Ich habe ein Formular mit dem ich Rechnungen erstelle aus einer Abfrage diese wird gefiltert durch ein Kombinationsfeld = Me!txtRechnungsdatumVon in dem ist die KlinikID und der Name der Klinik, desweiteren habe ich ein Textfeld = Me!RechNr da trage ich händisch die Rechnungsnummer ein z.B. 2019-25.

Ablauf: Ich wähle eine Klinik aus, dann wird das Formular gefiltert und zeigt mir nur noch die abrechenbaren Klienten an.

Code (vb) [Auswählen] Erweitern

Private Sub cmdRechnung_Click()
Dim strDateipfad As String
Dim strBericht As String
Dim strRechnung As String
Dim strFilter As String
Dim sqlcmd1 As String
Dim strSQL As String
Dim strSQL1 As String
Dim strUpdate As String
Dim strUpdate1 As String


If IsNull(Me!RechNr) Then
MsgBox "Fehlende Rechnungsnummer!!!", vbOKOnly, "Fehlende Daten"
Else

strBericht = "rptRechnung"
strDateipfad = Me.Pfad & "Rechnung_" & Me.RechNr & ".pdf"
If Not IsNull(Me!txtBestelldatumVon) Then
        strFilter = strFilter & " AND Start >= " & ISODatum(Me!txtBestelldatumVon)
    End If
    If Not IsNull(Me!txtBestelldatumBis) Then
        strFilter = strFilter & " AND Start <= " & ISODatum(Me!txtBestelldatumBis)
    End If
    If Not Me!txtRechnungsdatumVon = True Then
        strFilter = strFilter & " AND bezeichnung LIKE '*" & Me!txtRechnungsdatumVon.Column(1) & "*'"
    End If
    If Len(strFilter) > 0 Then
        strFilter = Mid(strFilter, 5)
        Me!sfmRechnungfilter.Form.Filter = strFilter
        Me!sfmRechnungfilter.Form.FilterOn = True
    Else
        Me!sfmRechnungfilter.Form.Filter = ""
        Me!sfmRechnungfilter.Form.FilterOn = False
    End If


das klappt auch alles  weiterhin wird auch der Bericht erstellt der Code geht dann weiter mit:

Code (vb) [Auswählen] Erweitern

DoCmd.OpenReport strBericht, View:=acViewPreview, WhereCondition:=strFilter
    sqlcmd1 = "INSERT INTO tblBestellungen ([KlinikID], [RechNr], [Rechnung_am], [Rechnungsdatei], [Zahlungsziel], [Betrag]) VALUES ('" & [Reports]![rptRechnung]![KlinikID] & "', '" & Me.RechNr & "', Date(), '" & Me.Pfad & "Rechnung_" & Me.RechNr & ".pdf" & "', '" & Date + 14 & "', '" & [Reports]![rptRechnung]![Text88] & "');"
    strUpdate = "UPDATE tblRechNr SET RechNr = '" & Me.RechNr & "';"
    strSQL = "INSERT INTO tblBestellpositionen SELECT KlinikID, KundeID, vorgangid, vorname, Nachname, Personen, Kunde, Familie, TagundPersonen, Sommer, Winter, Beginn, Anzahl FROM qryTemp WHERE KlinikID = " & Me.txtRechnungsdatumVon & "; "
    strUpdate1 = "UPDATE tblBestellpositionen SET Rechnung_AM = " & ISODatum(Now) & " WHERE KlinikID = " & Me.txtRechnungsdatumVon & ""
DoCmd.SetWarnings False
    DoCmd.RunSQL sqlcmd1
    DoCmd.RunSQL strSQL
    DoCmd.RunSQL strUpdate
    DoCmd.RunSQL strUpdate1
    DoCmd.SetWarnings True
    DoCmd.OutputTo acOutputReport, strBericht, acFormatPDF, strDateipfad
    DoCmd.Close acReport, strBericht
End If
Call Update
End Sub


Auch das macht er alles,  das folgenden steht im Call Update drin nur das wird auf einen Datensatz ausgeführt obwohl es mehrere Klienten zu einer Klinik gibt deshalb ist die Klinik auch öfter als Zahl vertreten:

Code (vb) [Auswählen] Erweitern

sql = "UPDATE [tblBestellpositionen] SET [RechNr] = '" & Me!RechNr & "' WHERE [KlinikID] = " & Me!Me!txtRechnungsdatumVon & " ;"

das kommt dann dabei raus in der Tabelle

BestellpositionID   RechNr   Rechnung_AM   KlinikID   VorgangID   KundeID 1651   2019-200   29.08.2019 17:02:42   40   6617   5414 1652      29.08.2019 17:02:42   40   7019   5763 1653      29.08.2019 17:02:42   40   7020   5764 1654      29.08.2019 17:02:42   40   7021   5765 1655      29.08.2019 17:02:42   40   7240   5965 1656   2019-5   28.08.2019 21:11:40   25   2712   2458 1657      28.08.2019 21:11:40   25   6801   5577 1658      28.08.2019 21:11:40   25   7597   6244 1659      28.08.2019 21:11:40   25   7490   6147 1660   2019-18   28.08.2019 21:12:23   19   6817   5592 1661      28.08.2019 21:12:23   19   7380   6073 1662      28.08.2019 21:12:23   19   7511   6160

[MzKlMu]

Hallo,

WHERE [KlinikID] = " & Me!Me!txtRechnungsdatumVon & " ;"

Stimmt DS wirklich ?
Was steht denn in dem Feld "txtRechnungsdatumVon" ?
Da muss der gleiche Wert vorhanden sein wie in KlinikID.

[Purzel862]

Hallo,

WHERE [KlinikID] = " & Me!Me!txtRechnungsdatumVon & " ;"

Stimmt DS wirklich ?
Was steht denn in dem Feld "txtRechnungsdatumVon" ?
Da muss der gleiche Wert vorhanden sein wie in KlinikID.

Ja, da steht in der spalte 1 die KlinikId in Spalte 2 die Klinikbezeichnung

[MzKlMu]

Hallo,
ich hatte 2 Fragen.
Und nach Spalten hatte ich nicht gefragt.

[PhilS]

... durch ein Kombinationsfeld = Me!txtRechnungsdatumVon in dem ist die KlinikID und der Name der Klinik, ...

Technisch zwar nicht relevant, aber dennoch ist es mir unverständlich, warum du das Steuerelement txtRechnungsdatumVon nicht zu cboKlinik umbenennst.

Auch das macht er alles,  das folgenden steht im Call Update drin nur das wird auf einen Datensatz ausgeführt obwohl es mehrere Klienten zu einer Klinik gibt deshalb ist die Klinik auch öfter als Zahl vertreten:

Das ist für mich mit den bisherigen Informationen nicht erklärbar. Ich empfehle erneut, das zusammengesetzte SQL-Statement zu überprüfen und ggfls. probeweise einzeln in einer Abfrage auszuführen.

[Beaker s.a.]

@Purzel862
Mir fallen folgende Ungereimtheiten im Code auf.

Code [Auswählen] Erweitern

If Not Me!txtRechnungsdatumVon = True Then
        strFilter = strFilter & " AND bezeichnung LIKE '*" & Me!txtRechnungsdatumVon.Column(1) & "*'"
    End If
1. der komplett irreführende Name des Kombis; - "txt" als Prefix statt
"cbo", "Rechnungsdatum" für eine ID 
2. der Vergleich mit "True" für eben eine Zahl; - wenn du wissen willst
ob überhaupt ein Eintrag ausgewählt wurde, lautet der Vergleich

Code [Auswählen] Erweitern

If Nz(Me.cboKlinikID, 0) = 0 Then
'oder
If Me.cboKlinikID.ItemsSelected.Count = 0 Then
3. eine Kombispalte gibt immer den kompletten Inhalt der Tabellenspalte
zurück; - LIKE und Joker machen da keinen Sinn

Code [Auswählen] Erweitern

... & "Bezeichnung = " & Me!cboKlinikID.Column(1)

Code [Auswählen] Erweitern

strUpdate1 = "UPDATE tblBestellpositionen SET Rechnung_AM = " & ISODatum(Now) & " WHERE KlinikID = " & Me.txtRechnungsdatumVon & ""
.
.
Call Update
Der SQL-String wird in dieser Prozedur gar nicht weiter verwendet. Dafür
erstellst du ihn in der Prozedur "Update" noch einmal. Schreibe die Prozudur um

Code [Auswählen] Erweitern

Sub Update(sSQL As String)
    CurrentDb.Execute sSQL
End Sub
Der Aufruf erfolgt dann so

Code [Auswählen] Erweitern

Call Update strUpdate1

Ansonsten schliesse ich mich Phillip an

Ich empfehle erneut, das zusammengesetzte SQL-Statement zu überprüfen und ggfls. probeweise einzeln in einer Abfrage auszuführen.

Sehr hilfreich dabei ist dieses kleine Tool:
http://access-codelib.net/download/addins/SqlDebugPrint.zip

gruss ekkehard

[DSV]

Code (vb) [Auswählen] Erweitern


    Do Until rst.EOF
             sql = "UPDATE [tblBestellpositionen] SET [RechNr] = '" & Me!RechNr & "' WHERE [KlinikID] = " & Me!txtRechnungsdatumVon & " ;"
            dbs.Execute (sql)
        .MoveNext
    Loop


Das Hauptproblem liegt an dieser Schleife. Du läufst zwar alle Einträge im Recordset ab, jedoch wird diese Information nicht weiter verwendet. Das SQL-Statement ist immer dasselbe, denn es gibt keinen Zusammenhang zwischen dem Recordset und den Formularfeldern.

Wenn du beispielsweise ein Endlosformular hast in dem du dir Daten anzeigen lässt, so wird der Verweis auf ein Textfeld immer den Wert des ersten Eintrags zurückgeben, außer du führst den Code aus einem Steuerelement im Detailbereich aus, dann kannst du auf die Daten der jeweiligen Zeile zurückgreifen.

Daher führst du in der Schleife immer denselben Code aus. Vielmehr müsste mindestens das Argument im WHERE-Teil des SQL-Strings vom Recordset stammen, z.B.

Code (vb) [Auswählen] Erweitern


             sql = "UPDATE [tblBestellpositionen] SET [RechNr] = '" & Me!RechNr & "' WHERE [KlinikID] = " & rst!KlinikID & " ;"


[DSV]

.