UPDATE qry - Werte aus ungebundenem Textfeld

[Domm]

Sehr geehrte Access- Gemeinde, ich bitte um Hilfe für folgende Aufgabe:
In einem UForm werden nach Abfrage gefilterte DS angezeigt.
Die Variable eines ungebundenen Textfeldes <txtKorrekturPosition> im HForm soll mit Klick in das Feld <Position> aller DS übergeben werden.
Lösung1: Das ungeb Textfeld weglassen, ersten DS korrigieren und mit strg+' die anderen DS angleichen.
Lösung2: Aktualisierungsabfrage. Für mich die elegantere Maßnahme.
Die gefundenen Beiträge verweisen auf eine SQL, in der davon ausgegangen wird, dass der zu ändernde Wert feststeht.
In meinem Fall aber sollte der Anwender diesen Wert festlegen und per VBA auf die gefilterten DS übertragen.
Leider bekomme ich das nicht hin!
Die SQL der Abfrage:

Code [Auswählen] Erweitern

SELECT tblKandKurs.KandKursID, IIf([WocheA]=[AuswahlWoche],[WocheA],[WocheB]) AS AnzWoche, IIf([WocheA]=[AuswahlWoche],[TagA],[TagB]) AS AnzTag, IIf([WocheA]=[AuswahlWoche],[TageszeitA],[TageszeitB]) AS AnzTZ, tblKandKurs.Kandidat, tblKandKurs.Position
FROM (((tblKandKurs LEFT JOIN tblZimmer ON tblKandKurs.ZimmerA = tblZimmer.ZimmerID) LEFT JOIN tblZimmer AS tblZimmer_1 ON tblKandKurs.ZimmerB = tblZimmer_1.ZimmerID) LEFT JOIN tblZeiten ON tblKandKurs.StundeA = tblZeiten.ZeitID) LEFT JOIN tblZeiten AS tblZeiten_1 ON tblKandKurs.StundeB = tblZeiten_1.ZeitID
WHERE (((IIf([WocheA]=[AuswahlWoche],[WocheA],[WocheB]))=[AuswahlWoche]) AND ((IIf([WocheA]=[AuswahlWoche],[TagA],[TagB]))=[AuswahlTag]) AND ((IIf([WocheA]=[AuswahlWoche],[TageszeitA],[TageszeitB]))=[AuswahlTageszeit]))
ORDER BY tblKandKurs.Kandidat;

Zu erwähnen ist noch, dass diese Abfrage die Basis für mehrere UF ist und im Formular auf den Wert <Position> gefiltert wird.
Zum Verständnis: Der Anwender entscheidet mit der Festlegung der Position die Aufteilung der DS nebeneinander.
Nach Fertigstellung kann das Ergebnis eine Korrektur dieser Anordnung verlangen. Also z. B. Gruppe Kandidaten soll von Spalte (Position) 4 auf 7 verschoben werden. Gruppe Spalte 7 vorher auf die Ausweichposition 10 (nicht belegte Reserve), danach auf 4.
Klingt unelegant, aber hat sich erst bei den Testläufen als Eventualität heraus gestellt.
Der von Eberhard im MS Office Forum gepostete Code scheint meinem Problem nahe zu kommen, aber ich hab keine Ahnung, wie ich die Zeilen interpretieren kann:

Code [Auswählen] Erweitern

' Kundennummer = String?
CurrentDb.Execute "UPDATE tblASPCATKundenUndPartner " _
   & "SET Kundennummer = '" & Me!Kundennummer & "' WHERE FirmenID = " & Me!FirmenID
Kann mir jemand seine Zeit schenken?
Liebe Grüße

[Lachtaube]

Zusammenhänge beider Aussagen sind für mich leider nicht zu erkennen. Wie wäre es mit einem Upload eines Minimalbeispiels, das die Pronlematik darstellt?

[Domm]

Sorry fürs späte Reagieren, war unterwegs.
Ich bastle eine kleine Geschichte, welche das Geschehen umreißt.
Danke fürs Erste!

[Domm]

So, hab schnell was gestrickt...

[Lachtaube]

Mein Schnellstrickkurs ist auch gerade zu Ende gegangen.

[Domm]

Also, wenn das mal nicht geil ist...
Das kommt der Idee noch weit näher, als ich es mir wünschen konnte.
Hab zwar immer noch nix Peilung, wie Du die Aktualisierungsabfrage gebaut hast, noch kenne ich die Tabelle k. Die Datensatzquelle der UF ist ebenso geheimnisvoll. Statt 10 UF mit einer Datensatzherkunft qry hast Du 10 qury in ein UF gepackt.
Ich merke schon- der Weg ist sehr steinig!
Vielen herzlichen Dank für Deine Mühe!
Ich werd mich bemühen, das Zauberwerk in mein Konstrukt einzubauen. Wenns denn mal funzt, melde ich den Thread als abgeschlossen.
Liebe Grüße!

[Lachtaube]

Kurze Erläuterung:

Um zwei Werte zu tauschen, braucht es einen Zwischenspeicher. Denn wenn z. Bsp. 1 gegen 2 getauscht wird, würde ja beim Austausch von 1 nach 2 im nächsten Schritt nicht mehr ersichtlich sein, welcher 2er-Wert in eine 1 gewandelt werden muss. Ich setze deshalb zuerst den ersten 1. Wert auf Null, setze dann den 2. Wert auf den 1. Wert und danach die Null-Werte auf den 2. Wert.

Auf Einzelwerte A und B im VBA-Direktbereich beschränkt, braucht man eine temporäre Variable für den Austausch - nichts anderes machen die drei Aktualisierungsabfragen im Prinzip auch.

Code [Auswählen] Erweitern

A = 3
B = 5

? "A = "; CStr(A); " : B = "; Cstr(B)
A = 3 : B = 5

Temp = A
A = B
B = Temp

? "A = "; CStr(A); " : B = "; Cstr(B)
A = 5 : B = 3

Die SQL-Texte der Auswahlabfragen hätte ich auch jeweils für die Spalte modifiziert der Datenherkunft (RecordSource) der Formular-Instanz im Unterformularsteuerelement zuweisen können - ich vermeide aber lieber das Zusammenkleben von SQL-Ausdrücken, wenn es auch ohne geht.

Beim Klicken auf den Knopf, werden die Abfragen nacheinander in der im Array UpdateQueries aufgeführten Reihenfolge ausgeführt. Weil die Execute-Methode keine Formular-Parameter direkt verarbeiten kann, lese ich diese Parameter über die Variable p zunächst in einer Schleife ein, die sich danach die Werte über die gleichnamigen Steuerelemente des Formulars holt.

[Domm]

Das war eine ausführliche Erläuterung!
Wenn man sich durch die Foren liest, tauchen eigentlich immer neue Namen auf- bei der Eröffnung eines Themas. Und immer die gleichen Namen, wenn es um die Hilfestellung geht. Erst bei näherem Hinsehen wird Einem bewusst, welcher Zeitaufwand da bereit gestellt wird; bei immer wieder kehrenden Fragen unterschiedlichster Qualität.
Vielen Dank, dass Ihr nicht müde werdet, den Greenhorns auf den Weg zu helfen!
Ich selbst bin immer wieder Nutznießer Eurer selbstlosen Bemühungen und finde es durchaus passend, mal eben hier ein Denkmal zu setzen.
Liebe Grüße.

[Domm]

Sorry, ich habs nicht hinbekommen 
Ist einfach nicht meine Liga. 10 Ufos mit eigener qry sind mir einfach verständlicher als 10 qry, die ein Ufo ansteuern.
Na, nicht so dramatisch.
Aber ohne Einschränkung- Deine Lösung ist fantastisch!

[ebs17]

10 Ufos mit eigener qry sind mir einfach verständlicher als 10 qry, die ein Ufo ansteuern

Es könnte (und wird sein), dass Du das UFo mal überarbeiten und pflegen musst. Da bemerkst Du, dass es einen Unterschied zwischen einfachen und zehnfachen Aufwand gibt.

[Domm]

Eins kannst mir glauben- diese Ufos hab ich schon 10 mal überarbeitet!
Bei größeren Maßnahmen hab ich alle bis auf eins gelöscht, wieder vervielfältigt und nur die Kriterien angepasst.
Wie gerne würde ich Deine Strategie anwenden, aber das passt einfach nicht bei mir auf Diskette 
Zum Verständnis- mich als Quereinsteiger zu bezeichnen, seh ich selbst als vermessen.
Dann muss es halt umständlich gehn...
Um sehr mehr Respekt vor Eurem Wissen!!!

[Domm]

Nochmal ich.
Habs jetzt mit der Holzhammermethode gelöst.
Bei Klick auf die Spalte wird ein PopUp geöffnet, in dem neben dem Namen die Spalten- Nr. steht.
Die kann jetzt der Anwender händisch korrigieren.
Und eins weiß auch ich: Das riecht nicht nur so, das ist auch Kacke.
Deshalb nochmal ein Vorstoß: Lässt sich die Lösung von Lachtaube auch auf die Version "eine qry, viele Ufos" umstricken?
Dann hab ich vielleicht ne Chance, dass ohne Fehlermeldung umzusetzen.
Ich bewundere Eure Geduld

[Lachtaube]

Ja, das kann man - ich mache das aber nicht.

Alle Abfragen sind gleich aufgebaut und unterscheiden sich nur durch den fest vorgegebenen Parameterwert für ZuordnSpalte, der Werte von 1..10 annimmt. Hier exemplarisch der SQL-Text der Abfrage qselCol05

Code [Auswählen] Erweitern

SELECT k.KandID,
       k.Kandidat,
       k.ZuordnWoche,
       k.ZuordnTag
FROM   tblKandidaten AS k
WHERE  ( ( ( k.ZuordnWoche ) = [AuswWoche] ) AND
         ( ( k.ZuordnTag ) = [AuswTag] ) AND
         ( ( k.ZuordnSpalte ) = 5 ) );

Weil ich dem Formular frmZuordnKand seine Datensatzquelle geraubt habe, weise ich in einer Schleife beim Laden des Hauptformulars jeder Instanz in einer der 10 Unterformularsteuerelemente die entsprechende Datensatzquelle zu.

Code [Auswählen] Erweitern

Private Sub Form_Load()
   Dim i As Long
   
   For i = 1 To 10
      Me("UF" & Format$(i, "00")).Form.RecordSource = "qselCol" & Format$(i, "00")
   Next
   
End Sub
Die Namen der Unterformularsteuerelemente reichen von UF01..UF10. Me steht für die Formularklasseninstanz (das Hauptformular). Ein Formular besitzt als Standard-Eigenschaft die Auflistung Controls. Standardeigenschaften muss man nicht explizit benennen. Ein Textfeld-Steuerelement besitzt Value als Standardeigenschaft, die deshalb bei der Zuweisung eines Werts auch nicht explizit genannt werden muss. Im Objekt-Katalog (Funktionstaste F2 im VBA-Editor drücken) kann man sich das auch ansehen. Der türkisfarbene Punkt links über dem Namen weist darauf hin. Außerdem wird die Standardeigenschaft in der Textanzeige darunter noch einmal als Default member (in deutschem Office vermutlich Standardeigenschaft) erwähnt. Siehe Bildanhang.

Die Controls-Auflistung hat auch wiederun eine Standardeigenschaft Item, die auch hier unterschlagen wird. Item selbst erwartet entweder eine Zahl (die kennt man aber meistens nicht) oder einen Namen, um auf das Objekt (in diesem Fall auf das Unterformular-Steuerelement) zu verweisen.

Den Namen des Steuerelements bastele ich nun für jeden Schleifendurchgang zusammen, indem der feste Präfix UF mit dem zweistelligen Ziffernwert von 01..10 verknüpft wird.

Ohne Schleife, könnte man auch die Steuerelemente nacheinander in dieser Form adressieren.

Code [Auswählen] Erweitern

   Me.Controls.Item("UF01").Form.RecordSource = ...
   Me.Controls.Item("UF02").Form.RecordSource = ...
   '...
   Me.Controls.Item("UF10").Form.RecordSource = ...
   'oder gleichwertig (in bekannter Schreibweise)
   Me!UF01.Form.RecordSource = ...
   Me!UF02.Form.RecordSource = ...
   '...
   Me!UF10.Form.RecordSource = ...
Die im Code aufgeführte bekannte Schreibweise lässt es leider nicht zu, ihren Namen durch Code zu manipulieren - deshalb die Codierung über die Namen der Controls-Auflistung.

Weil ein Unterformular ein Container für beliebige Formulare sein kann, muss man natürlich die darin enthaltene Formular-Instanz (Form) und dessen Datensatzquelle (RecordSource) ansprechen.

Auf der rechten Seite der Zuweisung passiert nichts anderes, als dass der Name der Abfrage zusammengebaut wird. Alle Abfragenamen haben den selben Präfix und werden durch Verketten mit dem zweistelligen Ziffernwert von i zu qselCol01..qselCol10.

Wegen der von Dir verwendeten Formularparameter habe ich auf eine einzelne Parameterabfrage verzichtet, was die Geschichte noch einmal schlanker werden ließe, Dich aber vermutlich noch mehr verwirrt.

So, kommen wir zu dem Knopf. Was die Abfragen verrichten, habe ich ja bereits hier in einem Beitrag erklärt. Der wesentliche Code schaut so aus (ohne die Prüfung am Anfang, die wohl keiner Erklärung bedarf).

Code [Auswählen] Erweitern

   Dim UpdateQueries As Variant, q As Variant
   Dim p As DAO.Parameter
   UpdateQueries = Array("qupdCol1ToNull", "qupdCol2ToCol1", "qupdColNullToCol2")

   With CurrentDb
      For Each q In UpdateQueries
         With .QueryDefs(q)
            For Each p In .Parameters
               p = Me(p.Name)
            Next
            .Execute dbFailOnError
         End With
      Next
   End With

   Me.Refresh
Jeder Parameter bezieht sich auf ein gleichnamiges Steuerelement (wie das bei Formular-Parametern üblich ist). Col1 und Col2 sind die Namen der Textfeld-Steuerelemente, die die beiden zu tauschenden Spalten repräsentieren. In der Schlefe nimmt die Variable q nacheinander den Abfragenamen als Text an, wie er in UpdateQueries definiert ist. Es wird dann die Abfrage mit dem Namen, der durch die Variable q repräsentiert wird, geladen With .QueryDefs(q).

Weil die Abfrage Parameter enthält, wird jeder Parameter For Each p In .Parameters mit dem Wert des gleichnamigen Steuerelements im Formular versorgt p = Me(p.Name) - sonst würde der Code husten. Parameters ist eine Auflistungseigenschaft eines Querydef-Objekts, die ähnlich wie die Controls-Auflistung des Formulars, statt auf Steuerelemente auf Parameter-Objekte verweist. Die Standardeigenschaft von Parameter ist auch Value. In der Langform sähe die gleiche Zuweisung so aus: p.Value = Me.Controls.Item(p.Name).Value - also: Parameterwert aus dem Steuerelement mit dem selben Namen wie dem Parameter holen und an die Parameterwert-Eigenschaft zuweisen.

Zuguterletzt, wenn die Abfrage mit Parameterwerten versorgt ist, wird sie durch die Execute-Methode zur Ausführung gebracht.

Weil das Formular vom getätigten Ablauf nichts weiß, wird durch Me.Refresh veranlasst, dass die aktualisierten Daten in den Unterformularsteuerelementen darstellt werden.

[Domm]

Vielen Dank für die umfangreichen Ausführungen!
Bis jetzt bin ich noch beim Lesen und Verinnerlichen.
Die Erklärung lässt nun zumindest das Verständnis für die Herangehensweise zu.
Wenn ich das mal begriffen und umgesetzt habe, bin ich einen gewaltigen Schritt weiter.
Es gibt mir fast ein schlechtes Gewissen, Dir den Samstag mit meiner "Hobbydatenbank" zu rauben.
Sei versichert- ich weiß das sehr zu schätzen!
Einen ruhigen Sonntag wünscht Domm