opbouw sql string voor update met variabele criteria

[boule]

Beste Helpers,

Ik ben weer vastgelopen.
Misschien dat toch iemand mij kan helpen

Probleem :

Afhankelijk van een clubnummer wil ik in een query een selectie maken uit de personen die
eerder voor die club hebben ingeschreven

De personen die in het verleden hebben ingeschreven staan in Deelnemers
In de database van de Deelnemers staat aangegeven van welke club zij zijn

De geselecteerde namen moeten in ClubDeelnemers komen

Bij het openen van de form worden twee clubnummers gelezen.(gaat goed)

Het reeds bestaande Clubdeelnemers wordt leeggemaakt.(gaat goed)

Daarna moet deze weer gevuld worden.

Ik wil dit doen met een SQL maar blijf Syntaxerror krijgen :

Ik heb verschillende schrijfwijzes geprobeerd maar geen positief resultaat :

Als het voor de eigen club is geldt Clubcode als het voor een andere club is geldt Voorclubnr

Op dit moment ziet het er zo uit :


' Hier het sheet clubdeelnemers leeg maken
' voordat het gevuld wordt voor de in te voeren club
'

strSQL = "DELETE Clubdeelnemers.*" & _
" FROM Clubdeelnemers;"
CurrentDb.Execute strSQL ' schoonmaken bestaande Clubdeelnemers

' hierna moet de sheetClubdeelnemers leeg zijn en
' kan deze opnieuw gevuld worden
'

If clubcode = Voorclubnr Then
strSQL = "UPDATE ClubDeelnemers.Clubcode, ClubDeelnemers.Spelers " & _
"FROM Deelnemers WHERE (((Deelnemers.Clubcode)=Clubcode));"
CurrentDb.Execute strSQL
Else
strSQL = "UPDATE ClubDeelnemers.Clubcode, ClubDeelnemers.Spelers " & _
"FROM Deelnemers.Clubcode,Deelnemers.Spelers " & _
"WHERE (((Deelnemers.Clubcode)=Voorclubnr));"
CurrentDb.Execute strSQL
End If


Beide laatste strSQL's geven een fout melding

Help !!

vast bedankt

Boule

 

[Tardis]

Je syntaxis klopt niet.
Opbouw is:

UPDATE tabel
SET kolom1 = ....
, kolom2 = ....
WHERE .......

Tardis

 

[boule]

Aanvullend : verwijderen van resultaten uit query

Mensen,

Ik kom er net achter dat de SQL met delete op de query ook de gegevens uit het bronsheet heeft verwijderd.

Was dus niet de juiste manier.

Op welke wijze moet ik een nieuwe selectie maken.
Ik had dat eerder geprobeerd , maar dan krijg ik de boodschap dat de query al bestaat.

Voor de rest van het programma moet de naam van de qury altijd hetzelfde zijn maar wel met verschillende inhoud (steeds deelnemers van andere clubs.

vast bedankt

Boule

 

[OctaFish]

Zou je eens kunnen beginnen met je 1e bericht een beetje te fatsoeneren? Er staan meer lege regels in dan bierflessen in mijn koelkast.... And that's not natural
Kijk ik ondertussen toch maar even naar je probleem.
Oh ja, code opmaken met de CODE knop...

 

[OctaFish]

Een voorbeeldje van een Update query:

UPDATE tblDebiteuren, tblTermijn SET tblDebiteuren.[Termijn:] = [tblTermijn].[Termijn], tblDebiteuren.Boete = [tblTermijn].[Boete];

Bij jou heb ik geen idee waarom je ClubDeelnemers velden haalt uit .Deelnemers. En je filter haal je (vermoed ik) uit een formulier, en dat gaat zo ook niet werken.
Je Delete query is ook fout, maar dat wist je al.

strSQL = "DELETE * FROM Clubdeelnemers;"

Als je geen idee hebt hoe je een query opbouwt met VBA, maak de query dan eerst in het Query ontwerpvenster, en kopieer dan de SQL ervan naar je VBA venster. Daar kun je 'm dan simpel aanpassen.

 

[boule]

Michel,

De wijze van eerst een query maken en dan kopieren naar VBA gebruik ik al. Het probleem zit in de variabelen waar op moet worden getest.

Enige uitleg van de functionaliteit helpt waarschijnlijk.

Het sheet Deelnemers bevat alle namen van personen die in het verleden hebben ingeschreven (hun naam en het nummer van de club)
Wanneer er van een club mensen worden opgegeven wil ik de mogelijkheid geven om te kiezen uit namen die eerder al eens hebben ingeschreven voor die club.
Om te voorkomen dat er een keuze gemaakt wordt uit alle namen wil ik met een query eerst de namen van de deelnemers van de club die inschrijving doet ophalen"(komt in ClubDeelnemers).
Deze query wordt dan gebruikt in een een combobox. Door het aanklikken van de naam wordt die persoon opgenomen als ingeschreven.

Het los aanmaken van de query gaat goed als ook dan het plaatsen in de combobox.

Het probleem komt door de variabele club invoer.
Telkens als er voor een andere club wordt ingevoerd moeten er andere namen in de query Clubdeelnemers komen.
Als ik bv UPDATE gebruikt worden de nieuwe namen erbij gezet en dat is niet de bedoeling.
Mijn voorkeur gaat uit naar telkens een nieuwe query aanmaken per Club, maar of dat mogelijk is binnen VBA weet ik niet.
Wanneer de Form opgestart wordt is bekend voor welke club dit is en zou dus de query voor die club moeten worden opgebouwd.

Dus : Hoe de inhoud van een bestaande query vervangen ?

Vast bedankt,

Boule

PS een voorbeeld van een deel van de form er bij gedaan
PPS alle suggesties zijn welkom

 

[OctaFish]

Om te voorkomen dat er een keuze gemaakt wordt uit alle namen wil ik met een query eerst de namen van de deelnemers van de club die inschrijving doet ophalen"(komt in ClubDeelnemers). Deze query wordt dan gebruikt in een een combobox. Door het aanklikken van de naam wordt die persoon opgenomen als ingeschreven.

Waarom wil je die query in een aparte tabel hebben? Die kan je toch gelijk aan de Keuzelijst hangen? Ik neem aan dat je nog ergens een keuzelijst cboClubs op je formulier hebt, die alle clubs laat zien. En die keuzelijst zou ik dan als basiscriterium gebruiken voor de keuzelijst met de personen die zich eerder hebben ingeschreven. Klik je op een club, dan ververst de 2e keuzelijst met deelnemers zich automatisch (mits je één regel VBA aan de keuzelijst cboClubs hangt).
Met Update voeg je overigens geen nieuwe deelnemers toe, je bewerkt ze alleen. Lijkt mij in dit geval nutteloos. Maar de hele constructie die je hebt bedacht is helemaal niet nodig.

 

[boule]

Michel,

In de toepassing is de bepaling van de club al in een eerdere Form gebeurd, voorlopig wil ik mijn eigen oplossing gebruiken.
Ik heb nog wat verder gezocht op het internet op amerikaanse sites en daar een groot deel van de oplossing gevonden .
Het is nog niet helemaal opgelost maar met vaste waardes werkt het nu.

'    Hier het sheet clubdeelnemers verwijderen maken
'    voordat het gevuld wordt voor de in te voeren club
'
    Dim db As Database
    Set db = CurrentDb
    Dim qry1 As QueryDef
    
    On Error Resume Next
    db.QueryDefs.Delete "Clubdeelnemers"
    On Error GoTo 0

'  hierna is de sheet Clubdeelnemers weg zijn en
'   kan een nieuwe gemaakt worden
'
 If clubcode = Voorclubnr Then
            strSQL = "SELECT Deelnemers.Clubcode, Deelnemers.Spelers" & _
                     " FROM Deelnemers WHERE (((Deelnemers.Clubcode)=108));"
            Set qry1 = db.CreateQueryDef("Clubdeelnemers", strSQL)
            DoCmd.OpenQuery qry1.Name
Else
            strSQL = "SELECT Deelnemers.Clubcode, Deelnemers.Spelers" & _
                     " FROM Deelnemers WHERE (((Deelnemers.Clubcode)=112));"
            Set qry1 = db.CreateQueryDef("Clubdeelnemers", strSQL)
            DoCmd.OpenQuery qry1.Name
End If

Wat hier nu nog speelt is de schrijfwijze waarmee ik kan aangeven dat ipv 108 de inhoud van variabele "Clubcode" en bij 112 de inhoud van Voorclubnr komt te staan. Als jij of een ander dat weet scheelt dat mij weer een hoop zoek werk.

Ik heb de codering van de oplossing tot zo ver vermeld, mogelijk hebben anderen daar ook wat aan.

In ieder geval bedankt

Boule

 

[OctaFish]

In de toepassing is de bepaling van de club al in een eerdere Form gebeurd, voorlopig wil ik mijn eigen oplossing gebruiken.

Je mag uiteraard aan je eigen oplossing de voorkeur geven, al denk ik dat de mijne (een stuk) beter is. Zeker als je de clubcode al hebt vanuit een ander formulier, want niets houdt je tegen om de gekozen clubwaarde als argument mee te geven bij het volgende formulier, zodat je 'm helemaal paraat en bij de hand hebt. En dus helemaal niet van die ingewikkelde constructies hoeft te bedenken. Maar ja, in ben dan ook geen Amerikaan

Zolang ik niet weet hoe de variabelen Clubcode en Voorclubnr beschikbaar zijn, kan ik nog geen definitieve oplossing geven, maar ik zou het dan zo doen (met een wat nettere code)

Dim qryDef As DAO.QueryDef

    If Clubcode = Voorclubnr Then
        strSQL = "SELECT [Clubcode], [Spelers] FROM Deelnemers WHERE [Clubcode]=" & Clubcode
    Else
        strSQL = "SELECT [Clubcode], [Spelers] FROM Deelnemers WHERE [Clubcode]=" & Voorclubnr
    End If
    On Error GoTo MaakClubQuery
    Set qryDef = CurrentDb.QueryDefs("Clubdeelnemers")
    qryDef.SQL = strSQL

    DoCmd.OpenQuery qryDef.Name
    Exit Function

MaakClubQuery:
    Set qryDef = db.CreateQueryDef("Clubdeelnemers", strSQL)
    On Error GoTo 0
    Resume

Je hoeft een query maar één keer aan te maken; steeds weggooien en opnieuw maken is een beetje onzinnig in mijn ogen.

 

[boule]

Michel,

Bedankt voor je reactie.
Belangrijk voor mij is dat ik uit jouw reactie de volgende zaken heb opgepikt.

1. Het is niet noodzakelijk om de tabelnaam telkens bij de velden te noemen. (dit gebeurt als je het kopieert uit de create query)
2. De wijze waarop de variabele in de strSQL wordt opgenomen

 If clubcode = Voorclubnr Then
            strSQL = "SELECT [Clubcode],[Spelers]FROM Deelnemers WHERE [Clubcode]=" & clubcode
            Set qry1 = db.CreateQueryDef("Clubdeelnemers", strSQL)
            DoCmd.OpenQuery qry1.Name
Else
            strSQL = "SELECT [Clubcode],[Spelers]FROM Deelnemers WHERE [Clubcode]=" & Voorclubnr
            Set qry1 = db.CreateQueryDef("Clubdeelnemers", strSQL)
            DoCmd.OpenQuery qry1.Name
End If

De werkwijze om eerst de query te wissen heeft te maken met de (mogelijk) grote variatie van clubs en dus verschillende Clubdeelnemers queries die nodig zijn.

Ik ben in ieder geval blij met je reactie die weer geholpen heeft om iets meer van de werking van access te weten te komen.

bedankt,

Boule

PS Nu blijft de datasheet van Clubdeelnemers staan. Welke string maakt deze weer onzichtbaar.

 

[OctaFish]

Ad 1: Ik denk dat bij Microsoft tikpoezen per letter worden betaald, en dat het dus zin heeft om als je tekst genereert vanuit het pakket (en Access doet dat dus als je queries maakt) om daarbij zoveel mogelijk tekens in de strijd te gooien. Maar het is vaak nergens voor nodig, en maakt queries slecht leesbaar. Zeker als je een query met VBA genereert, wil je de code leesbaar houden. Ik zal e.e.a. toelichten met een voorbeeldje. Te beginnen met een query zoals die door Access wordt gemaakt als ik een query bouw.

SELECT Tbl_Patient.PatientID, Tbl_Patient.PatientName, Tbl_Patient.PatientBirthDate, Tbl_Patient.ZISCode, Tbl_Patient.Gender, Tbl_Patient.StudyName, 
Tbl_Patient.Initials, Tbl_Patient.Date, Tbl_Patient.url, Tbl_Materials.SubClass, Tbl_Materials.ActiveRemission
FROM Tbl_Patient INNER JOIN Tbl_Materials ON Tbl_Patient.PatientID = Tbl_Materials.PatientID
WHERE (((Tbl_Patient.Gender)="Male")) OR (((Tbl_Patient.StudyName)="Diabetes"));

Je ziet dat ik 2 tabellen gebruik, en uit die tabellen verschillende velden ophaal. Access zet voor alle velden de veldnaam. Toch kan de code veel korter. Stap 1 is dan:

SELECT Tbl_Patient.PatientID, PatientName, PatientBirthDate, ZISCode, Gender, StudyName, 
Initials, Date, url, SubClass, ActiveRemission
FROM Tbl_Patient INNER JOIN Tbl_Materials ON Tbl_Patient.PatientID = Tbl_Materials.PatientID
WHERE (((Tbl_Patient.Gender)="Male")) OR (((Tbl_Patient.StudyName)="Diabetes"));[code]
Hier zie je dat ik het veld [PatientID] wél door met tabelnaam gebruik. De reden waarom hier wel, en bij de rest van de velden niet, is een hele simpele: zodra een [I]veldnaam[/I] uniek is, hoef je niet aan te geven waar hij vandaan komt. Access kan 'm dan altijd vinden. In deze query komt het veld [PatientID] 2 keer voor: één keer in de tabel [Tbl_Patient] en één keer in [Tbl_Materials]. De veldnaam is dus niet uniek, en daarom moet je steeds de bron aangeven als je het veld wilt gebruiken. Dat is simpel op te lossen door in een van de twee tabellen de veldnaam te veranderen. Je krijgt dan als query:
[code]SELECT PatientID, PatientName, PatientBirthDate, ZISCode, Gender, StudyName, Initials, Date, url, SubClass, ActiveRemission
FROM Tbl_Patient INNER JOIN Tbl_Materials ON Tbl_Patient.PatientID = Tbl_Materials.Patient_ID
WHERE (((Tbl_Patient.Gender)="Male")) OR (((Tbl_Patient.StudyName)="Diabetes"));

In de Inner Join moet je nog steeds de volledige tabelverwijzing gebruiken, anders doet die het niet, maar in de SELECT is PatientID nu uniek, en hoeft dus geen tabelverwijzing meer te hebben.
De code kan nog steeds netter, want zoals je ziet maakt Access van de WHERE ook een behoorlijk zooitje. Waarschijnlijk heeft iemand bij Microsoft het patent op de toetsen ( en ) en krijgt die per gebruikt teken een paar centen... Opgeschoond ziet de query er dus zo uit:

SELECT PatientID, PatientName, PatientBirthDate, ZISCode, Gender, StudyName, Initials, Date, url, SubClass, ActiveRemission
FROM Tbl_Patient INNER JOIN Tbl_Materials ON Tbl_Patient.PatientID = Tbl_Materials.Patient_ID
WHERE Gender="Male" OR StudyName="Diabetes";

Een SQL code die veel makkelijker samen te stellen is in VBA dan de Microsoft variant. En gewoon hetzelfde doet.
Daarom is het op zich wel een goed idee om een eerste (VBA) query met de Query builder te maken, maar daarna schoon ik 'm dus altijd op.

De werkwijze om eerst de query te wissen heeft te maken met de (mogelijk) grote variatie van clubs en dus verschillende Clubdeelnemers queries die nodig zijn.

En dat is dus niet zo: ik gebruik vaak een (tijdelijke) query met verschillende gegevensbronnen op de manier zoals ik in mijn code heb gedemonstreerd. Eén keer de query aanmaken, en daarna steeds de SQL code ervan vervangen. Op die manier gebruik ik bijvoorbeeld één query voor een aantal rapporten, die zijn gebaseerd op wisselende gegevensbronnen. Elke keer als het rapport wordt geopend, wordt de query aangepast. De ene keer staat tabel x er in met 6 velden, de volgende keer tabel Y+z met 23 velden. Maakt allemaal niks uit: je hoeft alleen maar de property SQL aan te passen. Door steeds objecten aan te maken en weg te gooien wordt je db alleen maar groter, en de kans dat hij corrupt raakt ook groter. Maar het aanpassen van een query heeft bar weinig effect op de db.

Wat je laatste vraag betreft:
Je opent een query met DoCmd.OpenQuery. Wat verwacht je dan wat er gebeurt als je een query opent? Zelf zou ik dat dus ook nooit doen, maar een formulier of rapport gebruiken. Dat kun je wél manipuleren.