som vanuit query in variabele ophalen

[so10070]

Ik probeer een som (TotaalDagenAPS) uit een query op te halen en deze te plaatsen in een variabele (TotaalDagenAPScholen), maar slaag er niet in! De laatste lijn geeft de foutmelding: "Kan het element niet vinden in deze collectie". Wat doe ik verkeerd?

Private Sub Form_Load()
    Dim sqlLijstAPScholen As String 'recordsource
    Dim TotaalDagenAPScholen As Single
    Dim sqlTotaalDagen As String 'totaal tellen
    Dim qdf As QueryDef
    
    sqlTotaalDagen = "SELECT DISTINCT Sum(tblAandachtspuntenScholenKoppelen.KAAantalDagen) AS TotaalDagenAPS, tblProducten.WerkgroepCGSID, tblProducten.PKalenderjaar " & _
        "FROM tblProducten INNER JOIN (tblAandachtspunten INNER JOIN tblAandachtspuntenScholenKoppelen ON " & _
        "tblAandachtspunten.AandachtspuntenID = tblAandachtspuntenScholenKoppelen.AandachtspuntID) ON tblProducten.ProductenID = tblAandachtspunten.ProductenID " & _
        "GROUP BY tblProducten.WerkgroepCGSID, tblProducten.PKalenderjaar " & _
        "HAVING (tblProducten.WerkgroepCGSID=[Formulieren]![frmWerkgroepCGS]![IDWerkgroepCGSID]) AND (tblProducten.PKalenderjaar = " & [TempVars].Item("PubKalenderjaar") & ");"

    Set qdf = CurrentDb.CreateQueryDef("TotaalDagen", sqlTotaalDagen)
    DoCmd.OpenQuery qdf.Name
    
    TotaalDagenAPScholen = qdf!TotaalDagenAPS

 

[OctaFish]

Je doet zóveel verkeerd, dat ik daar even de tijd voor moet nemen. Laten we beginnen met de foutmelding die je krijgt.
1. Als je een variabele wilt vullen, doe je dat door één waarde uit een veld uit een tabel of query uit te lezen. Dat doe jij dus niet: jij zet alle records uit de complete query in de variabele. Kan dus niet.
2. Volgende punt: je opent de query. Mag je doen, maar dat doet helemaal niks in je procedure, hooguit schermvervuiling.
3. Je zet de SQL in een QueryDef, maar dat is ook volkomen zinloos. Je moet de query openen in een Recordset.
4. Wil je waarden uit een Recordset halen, dan moet je door die Recordset lussen. Jij wilt zo te zien maar één waarde als resultaat, dus ik hoop voor je dat je query dat ook levert. Maar dan nog: de Recordset moet worden doorlopen.

Kortom: even terug naar de tekentafel! Ellenlange variabelenamen zijn in mijn ogen ook niet echt handig; hou ze kort en logisch. Logisch is om het type variabele in de naam te gebruiken, dus iAantal voor een integer, en strSQL voor een string.

 

[so10070]

Heb het nu zo gedaan en het werkt! :d. Je suggestie voor kortere en meer logische variabelnamen zal ik zeker meenemen.

Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim rssql As ADODB.Recordset
    Dim sqlTotaalDagen As String 'totaal tellen
    Dim TotaalDagenAPScholen As Single
    Dim sqlLijstAPScholen As String 'recordsource
    
    TotaalDagenAPScholen = 0
    
    sqlTotaalDagen = "SELECT DISTINCT tblAandachtspuntenScholenKoppelen.KAAantalDagen, tblProducten.WerkgroepCGSID, tblProducten.PKalenderjaar " & _
        "FROM tblProducten INNER JOIN (tblAandachtspunten INNER JOIN tblAandachtspuntenScholenKoppelen ON " & _
        "tblAandachtspunten.AandachtspuntenID = tblAandachtspuntenScholenKoppelen.AandachtspuntID) ON tblProducten.ProductenID = tblAandachtspunten.ProductenID " & _
        "GROUP BY tblAandachtspuntenScholenKoppelen.KAAantalDagen, tblProducten.WerkgroepCGSID, tblProducten.PKalenderjaar " & _
        "HAVING (tblProducten.WerkgroepCGSID = " & [Forms]![frmWerkgroepCGS]![IDWerkgroepCGSID] & ") AND ((tblProducten.PKalenderjaar) = " & [TempVars].Item("PubKalenderjaar") & ");"

    Set cnn = CurrentProject.Connection 'open de connectie
    Set rssql = New ADODB.Recordset 'wijs aan de variabele rssql een recordset toe
    rssql.Open sqlTotaalDagen, cnn 'waarden voor één of meer parameters ontbreken
    
    Do While Not rssql.EOF
        TotaalDagenAPScholen = TotaalDagenAPScholen + rssql![KAAantalDagen]
        rssql.MoveNext
    Loop
    
    rssql.Close
    Set rssql = Nothing
    Set cnn = Nothing

 

[OctaFish]

Ik snap niet waarom je een ADO connectie maakt; nu ben je (je gebruikt hem ook nog eens met Early Binding) verplicht om een ADO bibliotheek te laden. De hele handel kan makkelijk zonder extra bibliotheken met DAO:

Private Sub Form_Load()
Dim sqlTotaalDagen As String
Dim TotaalDagenAPScholen As Long
Dim rs As DAO.Recordset
    
    sqlTotaalDagen = "SELECT DISTINCT tblAandachtspuntenScholenKoppelen.KAAantalDagen " & _
        "FROM tblProducten INNER JOIN (tblAandachtspunten INNER JOIN tblAandachtspuntenScholenKoppelen ON " & _
        "tblAandachtspunten.AandachtspuntenID = tblAandachtspuntenScholenKoppelen.AandachtspuntID) ON " & _
        "tblProducten.ProductenID = tblAandachtspunten.ProductenID " & _
        "GROUP BY tblAandachtspuntenScholenKoppelen.KAAantalDagen" & _
        "HAVING (tblProducten.WerkgroepCGSID = " & Me.IDWerkgroepCGSID & ") " & _
        "AND (tblProducten.PKalenderjaar = " & [TempVars].Item("PubKalenderjaar") & ");"
    Set rs = CurrentDb.OpenRecordset(sqlTotaalDagen)
    With rs
        Do While Not .EOF
            TotaalDagenAPScholen = TotaalDagenAPScholen + .Fields(0)
            .MoveNext
        Loop
    End With
End Sub

Overigens gebruik je maar één veld, dus je hebt ook maar één veld nodig volgens mij.

 

[so10070]

Dat is me nog niet helemaal duidelijk: Late en Early Binding en met en zonder bibliotheek en wanneer ADO en wanneer DAO. Moet hier misschien toch eens een bijscholing over volgen. Maar in ieder geval ben ik door jouw uitleg al een stuk vooruit geholpen. :thumb:

 

[OctaFish]

Het verschil tussen Late Binding en Early Binding is niet zo moeilijk: bij Early Binding selecteer je in <Extra>, <Verwijzingen> een bibliotheek die altijd geladen wordt bij het openen van de db. Zo'n bibliotheek kun je dan te allen tijde aanroepen en gebruiken. Voordeel hiervan is, dat je gebruik kunt maken van IntelliSense; je begint met het typen van een commando en Access toont het volledige commando in de lijst. Op die manier maak je minder snel typfouten. Nadeel: bibliotheken zijn versie gebonden, dus als je een Excel bibliotheek van Office 2010 laadt (Excel 14.0) dan kun je de db niet gebruiken in Office 2007, want die gebruikt (geloof ik) Excel 12.0.
Bij Late Binding heb je dat nadeel niet, want je laadt de bibliotheek pas als je hem gebruikt. Dat heeft wel nadelen voor het gebruik, want je moet de objecten altijd als Object laden, en (bijvoorbeeld bij Excel) niet meer als Excel object (Worksheet, Workbook etc). Je hebt dus geen IntelliSense. Maar het grote voordeel is: de db werkt altijd en overal; de gebruiker hoeft de bibliotheken dus niet zelf te hebben.
Er zit ook een klein snelheidsverschil tussen Late en Early Binding (Early is iets sneller) maar dat merk je tegenwoordig nauwelijks.

ADO en DAO zijn 2 technieken om recordsets te manipuleren. DAO zit ingebakken in Access, ADO niet. ADO moet je dus laden als bibliotheek. Ook hier gelden de voor/nadelen van Early en Late Binding, en versieproblemen. Als je geen ADO specifieke opdrachten nodig hebt, zou ik DAO adviseren want de snelheid is ongeveer gelijk. Ik merk althans geen verschillen.

 

[so10070]

Bedankt voor de les! :thumb: