B3-cursus. Ontwikkelen met MS Access (vervolg)

B3: Systematisch ontwikkelen van eenvoudige informatiesystemen

Practicumopdracht 4

B3-cursus Practicumopdracht 4

Ontwikkelen met MS Access (vervolg) Inhoud: • Groepsvakken [met radio buttons en andersoortige buttons] al dan niet in combinatie met subforms. • Het relationele aspect: koppelen van gegevenstabellen met ook: relationship-overzicht • Recordsets en hun gebruik (een begin) • QueryDefs en hun gebruik • Debuggen van je code (kennismaking)

1

Groepsvakken: voor samenwerkende radiobuttons e.d.

We bespreken hier de opbouw van een eenvoudig formulier met daarop een aantal (via een ‘groepsvak’ gemaakt stel) samenwerkende radio buttons (Nederlands: ‘keuzerondjes’), die na het aangeven van de gewenste keuze en vervolgens een klik op een push button telkens een ander soort MsgBox laten verschijnen. We moeten uit de Toolbox (‘werkset’) het icoontje tweede van linksboven kiezen, dat aanklikken en vervolgens op het Form-blad plaatsen. Er verschijnt dan een ‘Wizard groepsvak’ waarbij we door verschillende dialoogschermen kunnen gaan (hieronder worden de belangrijkste drie ervan + het resultaatscherm getoond).

In het eerste scherm de teksten moeten geven die bij de verschillende radio buttons moeten komen te staan. Vervolgens komt de vraag, of bij verschijnen van het scherm een van de radio buttons al een standaardoptie geselecteerd moet zijn, of dat ze net alle ‘grijs’ moeten verschijnen. Vervolgens krijgen we de kans om gesuggereerde ‘keuze’-waarden voor elk van de radio buttons aan te geven; bij selectie van één van de radio buttons zal het groepsvak als geheel die hier opgegeven waarde als resultaat opleveren. Vervolgens krijgen we de vraag of we in dat groepsvak met radio buttons, met keuzevakjes of met buttons willen werken en of we speciale opmaak wensen. Tot slot kunnen we het bijschrift bij het totale groepsvak aangeven.

We tonen hierbij een mogelijk resultaat [waarbij we ook nog een titel en twee buttons aanbrachten]. Duidelijk is te zien dat bij ‘opstarten’ van het form alle radio buttons ‘grijs’ zijn.

1


2

Practicumopdracht 4

De MsgBox-procedure/functie

Zoals wel vaker gebeurt met procedures/functies in MS Access kan een niet doordachte aanroep van ‘MsgBox’ gemakkelijk voor onverwachte foutmeldingen zorgen. Dat komt omdat we MsgBox zowel als (’Sub’-) procedure als als functie kunnen gebruiken. We geven hierna de code zoals die gebruikt is om in het hiervoor opgebouwde scherm met een groep samenwerkende radio buttons de gewenste functionaliteit in te bouwen. Let op het (sterk overdreven) verschillende gebruik van de MsgBox-procedure/functie. Als je hem als procedure gebruikt, kun je de bij oproep meegegeven parameters naar keuze al dan niet tussen haakjes zetten. Roep je deze procedure op door gebruik te maken van de ‘Call’-aanroep-constructie, dan moet je de parameter(s) tussen haakjes plaatsen. Als je MsgBox wilt gebruiken als functie om in een messagebox met méérdere buttons zo de waarde van de aangeklikte button te kunnen bepalen, dan zul je ‘iets’ met het teruggegeven functieresultaat moeten doen. Meestal zul je dat resultaat eerst toekennen aan een variabele (of parameter). Private Sub ToonMsgBoxbutton_Click ( ) Dim Resultaat As Integer Select Case Keuzevak Case 1 MsgBox "Je hebt de eerste knop geselecteerd.", vbOKOnly ' met aangeven gewenst knopsoort Case 2 Resultaat = MsgBox ( "Je hebt de tweede knop geselecteerd.", vbYesNo) ' parameters moeten tussen haakjes; ook: 2 knoppen If Resultaat = vbYes Then MsgBox "Je hebt nu op 'Ja' geklikt." ' bij géén knopsoort-aanduiding verschijnt een OK-knop Else MsgBox ("Je hebt nu op 'Nee' geklikt.") ' parameters mogen hier tussen haakjes End If Case 3 Resultaat = MsgBox ( "Je hebt de derde knop geselecteerd.", vbYesNoCancel ) If Resultaat = vbYes Then Call MsgBox ("Je hebt nu op 'Ja' geklikt.") ' Na 'Call' moet je haakjes om de parameters zetten ElseIf Resultaat = vbNo Then MsgBox "Je hebt nu op 'Nee' geklikt." Else: MsgBox "Je hebt nu op 'Annuleren' geklikt." , , "Mijn eigen titel!" ' aanpassen formtitel End If End Select End Sub

Let ook op de getoonde laatste aanroep van MsgBox, waar géén tweede parameter is weergegeven [die is blijkbaar Optional], maar waarna als scheidingsteken toch een komma (‘,’) is geplaatst, alvorens de derde parameter aangeven (een nieuwe formtitel-tekst). Het resultaat is hierbij getoond.

3 3.1

Meer over het werken met Forms en Subforms Afhankelijke, niet-afhankelijke en berekende ‘besturingselementen’

Waarschijnlijk is je in de vorige opdracht bij het werken in Forms al opgevallen, dat in zo’n Form geplaatste ‘besturingslementen’ soms als kenmerk ‘niet-afhankelijk’ tonen. Wat betekent dat? Formulieren, gemaakt met een wizard en gebaseerd op een gegevenstabel hebben in eerste instantie alleen velden waarmee gegevens uit zo’n gegevenstabel getoond, ingevoerd en/of bijgewerkt kunnen worden. Zulke formuliervelden zijn afhankelijk van velden in een gegevenstabel en worden daarom terecht ‘afhankelijke besturingselementen’ genoemd. Een formulier-element zonder gegevensbron (dus zonder binding met een tabel of via een expressie uit andere waarden afgeleid) noemen we ‘niet-afhankelijke besturingselementen’. Een ‘besturingslementen’ dat geen tabelveld, maar een expressie [als = Prijs*Aantal] als gegevensbron heeft, noemen we een ‘berekend besturingselement’ . Zo’n expressie kun je in Design-modus (bij een

2


Practicumopdracht 4

tekstveld) zowel direct inbrengen in ‘het rechthoekje’ van het betreffende tekstveld, alsook via het Eigenschappen/Properties-dialoogscherm via ‘Gegevens/Besturingselementbron’ inbrengen. We tonen het voorafgaande in bijgaande figuur met een form-in-opbouw in Designmodus en het erbij behorende ‘Properties’/Gegevens. N.B. De in dit voorbeeld geschetste aanpak is alleen bedoeld ter illustratie van het besprokene. Vaak/meestal zul je zo’n waarde [met mogelijkheid van foutafhandeling e.d.] laten berekenen/opvullen via een aangeroepen procedure of functie. Als je in een Form zowel afhankelijke als niet-afhankelijke velden wilt plaatsen, dan kun je het best direct na het aanklikken van Form/New aangeven, dat je het Form op een tabel of query wilt baseren. Daarna kun je in de MS Access-werkbalk naast het Toolbox-icoontje een icoontje aantreffen voor het activeren van een ‘Velden’-window. Vanuit dit Velden-window kun je het gewenste veld slepen naar je Form-in-aanbouw. Dat afhankelijke veld in je Form krijgt dan niet alleen ook dezelfde naam als de geassocieerde tabelkolom, maar ook eigenschappen als waarderegels en/of opgelegd invoermasker.

3.2

Koppeling van een subform via een ‘Groepsvak’ (‘Option Group’) met radio buttons, toggle buttons of gecombineerde check boxes

We vervolgen hier met het voorbeeld uit de vorige practicumopdracht, waarbij we ter aansturing een koppeling tussen een besturingselement (tekstvak, combo-box en listbox) met een subform maakten. Ingeval van een ‘Option Group’ b.v. met radio buttons e.d. moet er (helaas) altijd met numerieke (integer) waarden worden gewerkt. Zoals eerder besproken meldt de ‘Option Group Wizard’ zich op een gegeven moment met het hiernaast gegeven scherm, waarbij je de keuze krijgt om desgewenst de reeds ingevulde default-waarden te vervangen door andere. Tenzij je in MS Access-taal (min of meer een subset van Visual Basic) moeizaam wilt gaan programmeren, kun je koppelingen tussen zo’n option group van radio buttons en een ander schermobject daardoor alleen maken door ergens een identificatie van de (hier:) muziekgenre-waarden via getallen op te nemen. In dit geval is dat gemakkelijk te regelen door als volgt een aparte gegevenstabel ‘Genres’ in de database op te nemen. In die tabel nemen we van alle mogelijke/gebruikte genres een identificerende ‘genrenaam’ èn een eveneens identificerend ‘genrenummer’ op (dan wel graag met dezelfde numerieke waarden als hiervoor bij die Option Group Wizard opgegeven zijn). We zien in de figuur, dat de ‘Genrenaam’kolom als sleutelkolom (primary key) is aangegeven. Hieronder tonen we de ingevoerde waarden van deze Genre-tabel. Genrenaam

Genrenummer

Classical

1

Jazz

2

New Age

3

Rock

4

Door nu een SQL-join-query te maken: SELECT [Recording ID], [Recording Title], [Recording Artist ID], Genres.Genrenaam, Genres.Genrenummer FROM Genres , Opnames WHERE Genres.Genrenaam = Opnames.[Music Category ID]

3


Practicumopdracht 4

en daar een subform op te baseren, krijgen we bij elke verschijnende genrenaam automatisch ook een genrenummer; de resultaat-uitvoer van het gedefinieerde subform laten we hieronder zien. (duidelijk is daarin al te zien, dat we de kolom voor dat genrenummer al heel smal hebben gemaakt; als je de waarden in zo’n kolom wel nodig hebt, maar ze niet per se wilt laten zien in je overzicht, dan kun je zo’n kolom bijvoorbeeld helemaal ‘plat schuiven’). Recording Title

Recording Artist ID

Genrenaam Genrenummer

6

Opus 65

The Popular Mechanics

Classical

1

4

Short Circuit

Dog House Rules

Jazz

2

5

Meditations

Mary Saveley

New Age

3

1

Noise in the Garage

Crawdad Stare

Rock

4

2

Look Both Ways

Crawdad Stare

Rock

4

3

Outback

Crawdad Stare

Rock

4

7

Sounds Better Louder

Crawdad Stare

Rock

4

Recording ID

Na al deze voorbereidingen moeten we in staat zijn om de gewenste user interface met een samengestelde groep van radio buttons te maken, waarbij we de numerieke identificatie van de aangeklikte radio button koppelen met de numerieke identificatie van een muziekgenre. Het gewenste scherm ziet er als volgt uit en heeft de gewenste functionaliteit. Door de kolombreedte van ‘Genrenummer’ heel smal te maken, komt hij niet/nauwelijks in het scherm voor. Toch bestaat die genrenummerinformatie, zodat we de koppeling tussen de groep radio buttons en het subform als getoond in de volgende figuur kunnen laten verlopen.

Duidelijk is te zien, dat er een koppeling is gelegd tussen het (niet getoonde) ‘genrenummer’ in het subform en de numerieke waarde van de groep radio buttons (die groep is de objectnaam ‘Groepframe’ gegeven). Het werken met een andersoortige ‘Option Group’ waarin ‘toggle buttons’ of (uiteraard: gecombineerde) ‘check boxes’ zitten, gebeurt op een soortgelijke manier (met een koppeling via numerieke waarden).

3.3

Het relationele aspect : de samenhang tussen gegevenstabellen

In een relationele database zijn in het algemeen gegevensrecords in de ene tabel verbonden met gegevensrecords in een of meer andere tabellen. Zo kan een registratie van tentamencijfers [van bijvoorbeeld een student met een bepaald studentnummer voor een tentamen met een bepaalde tentamencode] slechts mogelijk zijn voor een student wiens gegevens bekend [in bijvoorbeeld een ‘student’-tabel] en voor een bestaand/gepland tentamen [waarvan de tentamengegevens met tentamencode in een andere tabel zijn opgeslagen]. Voor de ‘Studentenadministratie’-database kunnen we in MS Access de relaties tussen de gegevens in de verschillende tabellen via de menu-optie ‘Extra/Relaties’ zoals hiernaast getoond zichtbaar krijgen. Zo’n ‘plaatje’ is alleen mogelijk indien we vanuit een veld van een tabel verwijzen naar een ‘sleutelveld’ van een andere tabel. We kunnen dan bijvoorbeeld in de Exresult-tabel met de muis op het

4


Practicumopdracht 4

‘Studnr’-veld klikken, de linker muistoets ingedrukt houden en de cursor bewegen naar het ‘Studnr’veld van de Student-tabel. Er verschijnt vervolgens een dialoogvenster (zie figuur), waarin gevraagd wordt via welke velden de tabellen aan elkaar gerelateerd moeten zijn (meestal wordt daarvoor reeds [zoals getoond] een suggestie gedaan). In het onderste deel van dat dialoogvenster kunnen we o.a. aan geven, dat ‘Referentiële integriteit’ afgedwongen moet worden. Dat betekent dat het systeem zèlf zal bewaken dat we bijvoorbeeld geen tentamenresultaat kunnen invoeren voor een niet bestaande student en/of voor een nooit afgenomen tentamen. We hebben dan te maken met zogenaamde ‘vreemde sleutel’-verwijzingen (foreign key): verwijzingen vanuit de ene tabel naar een (eventueel meer) primary-key-veld(en) in een andere tabel. In het eerder besproken voorbeeld over muzieknummers en het [ten behoeve van het kunnen werken met een groep gerelateerde radio buttons] geïntroduceerde hulptabelletje ‘Genres’ waarin slechts genrenamen (strings) geassocieerd werden met numerieke waarden (genrenummer). We kunnen daardoor de ‘Music Category ID’-gegevenswaarden in de ‘Opnames’-tabel via een foreign keyverwijzing beveiligen tegen invoer van niet [in de Genres-tabel] bestaande waarden (bijvoorbeeld als je een tikfout zou maken bij het invoeren van een nieuwe nummer). Je kunt dan komen tot de in de bijgaande figuur getoonde relatie tussen de gegevenstabellen ‘Opnames’ en ‘Genres’. Vanuit de tabel ‘Opnames’ verwijst een N:1-relatie vanuit de kolom ‘Music Category ID’ naar de kolom ‘Genrenaam’ van de Genres-tabel. Omdat die ‘Genrenaam’ daar een primary key is, is die verwijzing dus een foreign key. Ook heeft zo’n extra Genres-gegevenstabel als voordeel, dat het ‘vullen’ van de inhoud van een combo box of een list box kan gebeuren via een SQL-query: SELECT Genrenaam FROM Genres op zo’n relatief kleine tabel; dit gaat natuurlijk veel sneller dan via een SELECT DISTINCT [Music Category ID] FROM Opnames ORDER BY …

Practicumopdracht 4.a

(Ledenoverzicht via een set gecombineerde radio buttons)

Maak een variant op je Ledenoverzicht-form van vorige practicumopdracht, waarbij je het selecteren via de combo-box vervangt door een selectie via een viertal gecombineerde radio buttons, waarbij de selectie-mogelijkheden ‘Arnhem’, ‘Beuningen’ ‘Nijmegen’ en ‘Ubbergen’ zijn. Ga zelf na, wat de voordelen en nadelen van deze uitwerking zijn ten opzichte van de aanpak via een combo box. Denk hierbij ook aan het afdwingen van ‘referentiële integriteit’. Lever deze evaluatie met voor- en nadelen ook in!

4

Het werken met Recordsets

Tot nu toe hebben we bij het toegrijpen op gegevens die in een tabel opgeslagen waren, gebruik gemaakt van een op zo’n tabel [of op een query] gebaseerd formulier. Als je echter ‘wat meer wilt’ en gegevens op een veel meer zélf bepaalde wijze uit een tabel of query wilt aanroepen onder VBA, dan moet je daarvoor [expliciet] een zogenaamde recordset gebruiken. Een recordset kan gemaakt worden op een tabel, een query of met een zogenaamde ‘QueryDef’. N.B. Als je met een formulier, gebaseerd op een tabel of query, werkt, dan lijkt dat intern ook impliciet te zijn gebaseerd op een recordset (daar staan in het Properties/Gegevens-tabblad bij ‘Type recordset’ de mogelijkheden: ‘dynaset’ . . of ‘snapshot’).

Uit de MS Access-Help halen we (aangevuld met wat eigen opmerkingen): U kunt met de eigenschap Type recordset (RecordsetType) opgeven wat voor recordset beschikbaar wordt gemaakt aan een formulier. Als u bijvoorbeeld niet wilt dat gegevens in afhankelijke besturingselementen worden bewerkt wanneer een formulier in formulierweergave of gegevensbladweergave is, kunt u de eigenschap Type recordset (RecordsetType) instellen op Momentopname [Snapshot].

5


Practicumopdracht 4

Instelling De eigenschap Type recordset (RecordsetType) kent de volgende instellingen. Instelling Beschrijving Dynaset

Dynaset (Inconsistent bijwerken) Momentopname (Snapshot)

(Standaard) U kunt afhankelijke besturingselementen bewerken die gebaseerd zijn op één tabel of op tabellen met een een-op-een-relatie. Bij besturingselementen die gekoppeld zijn aan velden die gebaseerd zijn op tabellen met een een-op-veel-relatie, kunt u geen gegevens bewerken uit het koppelveld aan de 'een-kant' van de relatie, tenzij trapsgewijs bijwerken is ingeschakeld tussen de tabellen. Raadpleeg het onderwerp waarin wordt uitgelegd wanneer u records uit een query kunt bijwerken voor meer informatie over dit onderwerp. Alle tabellen en besturingselementen die afhankelijk zijn van hun velden, kunnen worden bewerkt. Tabellen, of besturingselementen die afhankelijk zijn van velden in die tabellen, kunnen niet worden bewerkt.

Visual Basic 0

1 2

U kunt deze eigenschap instellen met het eigenschappenvenster van een formulier, met een macro of met Visual Basic. Aanvullende informatie U kunt formulieren maken op basis van meerdere onderliggende tabellen met velden die afhankelijk zijn van besturingselementen op de formulieren. Afhankelijk van de instelling van de eigenschap Type recordset (RecordsetType) kunt u bepalen welke van deze afhankelijke besturingselementen kunnen worden bewerkt. Behalve dat bewerken kan worden geregeld met Type recordset (RecordsetType), heeft elk besturingselement op een formulier de eigenschap Vergrendeld (Locked), waarmee u kunt opgeven of het besturingselement en de onderliggende gegevens bewerkt kunnen worden. Als de eigenschap Vergrendeld (Locked) op Ja is ingesteld, kunt u de gegevens niet bewerken.

Kortom: we kunnen vanuit VBA een Recordset definiëren om daarmee gegevens alleen te kunnen bekijken en/of er bijvoorbeeld een rapport mee te genereren (je gebruikt dan i.h.a. een zogenaamde Snapshot-recordset) of om ze [behalve bekijken] ook te kunnen veranderen (via een Dynaset-variant). Voor het ‘openen’ van een Recordset bestaan o.a. de volgende, als tweede parameter mee te geven constanten’: dbOpenDynaset dbOpenSnapshot dbOpenTable

Recordset van het type dynaset openen Recordset van het type momentopname openen Pas op: deze constante bestaat wel, maar het gebruik ervan wordt afgeraden!

We zullen voorbeelden van beide (de eerste twee) typen recordsets bespreken aan de hand van een licht aangepaste versie van de ‘Opnames’-gegevenstabel, waaraan een aparte kolom ‘Keren_gespeeld’ is toegevoegd, zodat de tabelinhoud als volgt is:

4.1

Recordsets van het ‘Momentopname’ (Snapshot) -type

Over het Momentopname (Snapshot)-type halen we verder uit de MS Access-Help: Een Recordset-object van het type momentopname is een statische set records waarmee u gegevens in een of meer onderliggende tabellen kunt bekijken. In een ODBC-Direct-database correspondeert een Recordsetobject van het type momentopname met een statische cursor. Toelichting U maakt een Recordset-object van het type momentopname door de methode OpenRecordset toe te passen op een open database, op een ander Recordset-object van het type dynaset of momentopname, of op een QueryDef-object.

6


Practicumopdracht 4

Een Recordset-object van het type momentopname kan velden uit een of meer tabellen in een database bevatten. Een momentopname in een Microsoft Jet-werkruimte kan niet worden bijgewerkt. In een ODBCDirect-werkruimte kan een momentopname soms wel worden bijgewerkt, afhankelijk van het ODBCstuurprogramma. Als u een Recordset-object van het type momentopname maakt, worden de gegevenswaarden van alle velden (behalve velden met gegevenstype Memo en OLE Object (Long Binary) in .mdb-bestanden) in het geheugen geladen. Wanneer de veldwaarden eenmaal in het geheugen zijn geladen, worden wijzigingen in de gegevens van de basistabel niet doorgevoerd in de gegevens van het Recordset-object van het type momentopname. U kunt de bijgewerkte gegevens in het Recordset-object laden met de methode Requery of door de methode OpenRecordset opnieuw uit te voeren. De gegevens in een Recordset-object van het type momentopname staan niet in een bepaalde volgorde. Als u de gegevens wilt sorteren, maakt u het Recordset-object met een SQL-instructie en de component ORDER BY. U kunt hiermee ook records filteren, zodat alleen bepaalde records aan het Recordset-object worden toegevoegd. Met deze methode krijgt u sneller toegang tot uw gegevens dan met de eigenschappen Filter of Sort of wanneer u elke record afzonderlijk test. U kunt Recordset-objecten van het type momentopname meestal sneller maken en openen dan Recordsetobjecten van het type dynaset, omdat de records van een momentopname in het geheugen worden geladen of in tijdelijke schijfruimte (TEMP) worden opgeslagen. In dit geval hoeven namelijk geen pagina's te worden vergrendeld of bewerkingen voor meerdere gebruikers te worden beheerd door de Microsoft Jet Database Engine. Recordset-objecten van het type momentopname gebruiken echter meer systeembronnen dan Recordset-objecten van het type dynaset, omdat de volledige record in het lokale geheugen wordt geladen.

We willen als zeer eenvoudig voorbeeldje van de afzonderlijke records van de Opnames-tabel tonen, hoe vaak elk nummer (titel+groep) gespeeld is. Bekijk daarvoor de volgende VBA-code: Public Sub AlleOpnamesBekijken ( ) Dim OnzeDBS As Database Dim OpnamenSet As Recordset ' Initialiseer recordset Set OnzeDBS = CurrentDb ' "CurrentDb" levert de huidige, geopende database op Set OpnamenSet = OnzeDBS.OpenRecordset ( "Opnames", dbOpenSnapshot ) ' Herhalingsconstructie om de hele set te doorlopen Do While Not OpnamenSet.EOF ' zolang je niet aangekomen bent op het einde van de recordset MsgBox OpnamenSet![Recording Title] & " van " & _ OpnamenSet![Recording Artist ID] & Chr(13) & Chr(13) & "is " & _ OpnamenSet!Keren_gespeeld & " keren gespeeld." ‘ Let op de wijze waarop met een ‘_’ een voortzetting op de volgende regel aangegeven is OpnamenSet.MoveNext ' ga naar het volgende element in de recordset Loop ' Recordset sluiten OpnamenSet.Close End Sub

' sluit de recordset (het is een lokale variabele; wordt dus ook opgeruimd

N.B. Intermezzo: in geval van foutmelding over 'Dim db AS Database' Als er bij het compileren van de .bas-file aangegeven wordt dat "Dim db As Database" onbekend is, dan ontbreekt er een verwijzing naar een bepaalde ActiveX-component. Dit kan als volgt worden opgelost (althans in de Nederlandse versie; bij de Engelstalige versie moet via de sneltoetsen echter snel te achterhalen zijn wat je daarin precies moet doen om het probleem te verhelpen): Open de module (of importeer deze). Als het niet lukt om de module te compileren, stop dan eerst moet het compileren door op de 'stopknop' te drukken of over te schakelen naar de ontwerpweergave. Kies daarna uit het menu 'Beeld' het venster 'Objectenoverzicht' (F2). Klik vervolgens met de rechtermuisknop in de lijst 'Klassen'. Kies hier 'Verwijzingen...' Hier moet 'Microsoft DAO 3.6 Object library' worden aangeklikt (of een andere versie van deze bibliotheek) en moet 'Microsoft ActiveX Data Object 2.1 Library' (of een andere versie) worden uitgeschakeld, omdat deze conflicteert met de DAO library. Nogmaals: voor dit alles moet het compileren overigens wel gestopt zijn door op de stopknop te drukken of over te schakelen naar de ontwerpweergave."

Bij het laten uitvoeren van voorgaande programmacode verschijnt de informatie van alle records uit de recordset na elkaar (na klikken op de OK-button) op de hiernaast getoonde wijze op het scherm. Let op het gebruik van de Database-methode ‘OpenRecordset’ (hier gebaseerd op een totale tabel [de tabel “Opnames”]) en op de Recordsetmethodes ‘EOF’, ‘MoveNext’ en ‘Close’. Let ook op de wijze waarop ‘veld’-gegevens uit de recordset-elementen worden geadresseerd.

7


Practicumopdracht 4

Als je in de module-editor de naam van een recordset intikt en vervolgens een punt (‘.’), dan komt zoals verwacht een hulpmenuutje te voorschijn van mogelijke methoden voor dit objecttype. Verdere voor ons interessante methoden zijn o.a. FindFirst, FindNext, MoveFirst, MoveNext en RecordCount. Pas op: de methode RecordCount werkt goed bij gebruik met gegevenstabellen. Als je RecordCount echter wilt gebruiken bij Snapshots of Dynasets, dan moet je er op letten, dat MS Access uit performance-overwegingen bij het maken van een Recordset niet gelijk ‘alle records’ die er toe behoren ook in die recordset-geheugenvariabele plaatst; pas als je ‘een volgend record’ uit de set nodig hebt, dan wordt dat ingelezen. Om dus RecordCount een correct antwoord te laten geven over het (totale) aantal records in de recordset, moet je eerst een [recordsetvariabele].MoveLast-opdracht geven. Aan een Snapshot-Recordset kunnen uiteraard géén records worden toegevoegd of records worden verwijderd of veranderd. N.B. Let in voorgaande VBA-code op het gebruik van toekenningen via ‘Set . . = . . .’. Deze Setconstructie moet je gebruiken als je aan ‘objectvariabelen’ een waarde wilt toekennen. Tot die ‘objectvariabelen’ horen variabelen van types als Database, Recordset, Control, etc. Een tweede voorbeeld gaat over het baseren van een Snapshot-Recordset op de volgende zelf gemaakte query (die onder de naam ‘Categorie_overzicht’ is opgeslagen): SELECT [Music Category ID], COUNT(*) AS Aantal_Nummers, SUM ( Keren_gespeeld) AS Aantal_per_Categorie FROM Opnames GROUP BY [Music Category ID]

Op deze ‘Categorie_overzicht’-query definiëren en doorlopen we als volgt een RecordSet: Public Sub CategorienBekijken() Dim OnzeDBS As Database Dim CategorieSet As Recordset ' Initialiseer recordset Set OnzeDBS = CurrentDb Set CategorieSet = OnzeDBS.OpenRecordset( "Categorie_overzicht", dbOpenSnapshot ) Do While Not CategorieSet.EOF MsgBox "In de categorie '" & CategorieSet![Music Category ID] & "'" & Chr(13) & _ "zijn " & CategorieSet![Aantal_Nummers] & _ " nummers voorradig en die zijn " & Chr(13) & "bijelkaar " & _ CategorieSet!Aantal_per_Categorie & " keren gespeeld." , , "Per categorie" CategorieSet.MoveNext Loop ' Recordset sluiten CategorieSet.Close End Sub

Ga na dat het voor elke categorie verschijnend scherm er als hierbij weergegeven uitziet. Het is ook mogelijk om bij het openen van een Recordset niet de naam van een tabel of een opgeslagen query mee te geven, maar om direct een ‘echte query’ mee te geven, zoals in: Set OnzeSet = OnzeDBS.OpenRecordset ( "SELECT * FROM Categorie_overzicht" _ & " WHERE [Music Category ID] = 'Rock' ", dbOpenSnapshot )

en (als de rest van de code in vorige procedure vergelijkbaar is, dan wordt bij aanroep van de procedure alleen voorgaand scherm getoond (dus alleen van de ‘Rock’-categorie). N.B. We komen er later nog op terug hoe je in plaats van keihard dat ‘Rock’ in je code te plaatsen dit ook met gebruikersinvoer via bijvoorbeeld een tekstvak of een combo box e.d. kunt laten plaatsvinden.

8


4.2

Practicumopdracht 4

Recordsets van het ‘Dynaset’-type

Over het Recordset-Dynaset-type halen we verder uit de MS Access-Help: Een Recordset-object van het type dynaset is een dynamische set records met velden uit een of meer tabellen of query's in een database die al dan niet kunnen worden bijgewerkt. In een ODBCDirect-database correspondeert een Recordset-object van het type dynaset met een ODBC-sleutelsetcursor. Toelichting Een Recordset-object van het type dynaset is een type Recordset-object waarmee u gegevens in een of meer onderliggende databasetabellen kunt bewerken. Het verschil met een Recordset-object van het type momentopname is dat in een dynaset alleen de primaire sleutels van alle records en geen werkelijke gegevens worden opgeslagen. Een dynaset wordt daarom bijgewerkt met wijzigingen in de brongegevens, terwijl een momentopname niet wordt bijgewerkt. Net als een Recordset-object van het type tabel ontvangt een dynaset de volledige record pas wanneer de record moet worden bijgewerkt of weergegeven. U maakt een Recordset-object van het type dynaset door de methode OpenRecordset toe te passen op een open database, een ander Recordset-object van het type dynaset of momentopname, een QueryDef-object of een TableDef-object. (U kunt alleen in een Microsoft Jet-werkruimte Recordset-objecten openen in andere Recordset-objecten of TableDef-objecten). Als u een Recordset-object van het type dynaset wilt maken en de Microsoft Jet Database Engine kan geen lees-/schrijftoegang tot de records krijgen, kan er een Recordset-object van het type dynaset worden gemaakt dat alleen kan worden gelezen. Zodra gebruikers gegevens bijwerken, worden deze wijzigingen doorgevoerd in de basistabellen. Er zijn dus altijd bijgewerkte gegevens in uw toepassing beschikbaar zodra u de positie van de huidige record opnieuw instelt. In een database voor meerdere gebruikers kunnen diverse gebruikers Recordset-objecten van het type dynaset openen die naar dezelfde records verwijzen. Een Recordset-object van het type dynaset is dynamisch, zodat een wijziging in een record door een gebruiker onmiddellijk beschikbaar is voor de andere gebruikers. Als een gebruiker echter een record toevoegt, krijgen andere gebruikers de nieuwe record pas te zien wanneer ze de methode Requery op het Recordset-object uitvoeren. Als een gebruiker een record verwijdert, worden de andere gebruikers gewaarschuwd wanneer ze deze record proberen te openen. Records die aan de database worden toegevoegd, worden pas in het Recordset-object van het type dynaset opgenomen wanneer u de methoden AddNew en Update gebruikt. Als u bijvoorbeeld een actiequery met de SQL-instructie INSERT INTO gebruikt om records toe te voegen, worden de nieuwe records pas in het Recordset-object van het type dynaset geplaatst wanneer u de methode Requery gebruikt of het Recordsetobject opnieuw opbouwt met de methode OpenRecordset. De integriteit van de gegevens wordt gewaarborgd doordat Recordset-objecten van het type dynaset en tabel worden vergrendeld door de Microsoft Jet Database Engine tijdens Edit-bewerkingen (volledig vergrendelen) of Update-bewerkingen (beperkt vergrendelen). Op deze manier kan slechts één gebruiker een bepaalde record op een bepaald moment bijwerken. Wanneer een record wordt vergrendeld, wordt de volledige pagina (2 kB) met de record vergrendeld. U kunt ook niet-ODBC-tabellen beperkt en volledig vergrendelen. Als u externe tabellen in een Microsoft Jet-werkruimte vergrendelt met ODBC, moet u de tabellen altijd beperkt vergrendelen. Met de eigenschap LockEdits en de parameter lockedits van de methode OpenRecordset worden de vergrendelingsvoorwaarden tijdens bewerkingen bepaald. U kunt niet alle velden in Recordset-objecten van het type dynaset bijwerken. Als u wilt nagaan of u een bepaald veld kunt bijwerken, controleert u de instelling van de eigenschap DataUpdatable van het Field-object. In de volgende gevallen kan een Recordset-object van het type dynaset misschien niet worden bijgewerkt: • Er bestaat geen unieke index in de ODBC- of Paradox-tabel(len). • De gegevenspagina is vergrendeld door een andere gebruiker. • De record is gewijzigd nadat u deze voor de laatste keer hebt gelezen. • De gebruiker heeft geen machtiging. • Een of meer tabellen of velden kunnen alleen worden gelezen. • De database is geopend als alleen-lezen. • Het Recordset-object is gemaakt op basis van meerdere tabellen zonder JOIN-instructie of de query is te complex. De records of gegevens in een Recordset-object van het type dynaset staan niet in een bepaalde volgorde. Als u de gegevens wilt sorteren, maakt u het Recordset-object met een SQL-instructie en de component ORDER BY. U kunt de records ook filteren met de component WHERE, zodat alleen bepaalde records aan het Recordset-object worden toegevoegd. Als u SQL-instructies gebruikt om op deze manier een subset met records te selecteren en sorteren, krijgt u sneller toegang tot uw gegevens dan met de eigenschappen Filter en Sort.

9


Practicumopdracht 4

We geven als voorbeeld van het gebruik van een Dynaset een toepassinkje, waarbij we het aantal keren dat een van de eerder genoemde ‘muziekopnames’ afgespeeld werd, met één verhoogd kan worden. We lopen opname-voor-opname door de Recordset heen en vragen bij elk nummer of dat aantal keren wel of niet met 1 verhoogd moet worden. De bij die code behorende MsgBox is hiernaast getoond. Let er in de volgende code vooral op, dat indien we de waarde van een record(veld) willen veranderen, we eerst de [OnzeRecordset.] Edit-methode moeten aanroepen, vervolgens in de recordset de waarde wijzigen en als derde noodzakelijke operatie de Updatemethode aanroepen. Pas door de aanroep van die laatste methode wordt de gewijzigde waarde ook in de onderliggende gegevenstabel opgeslagen. En dan nu de code: Public Dim Dim Dim Dim

Sub AantalKerenGespeeldVerhogen ( ) OnzeDBS As Database OpnamenSet As Recordset Informatie As String Antwoord As Integer

'Initialiseer recordset Set OnzeDBS = CurrentDb Set OpnamenSet = OnzeDBS.OpenRecordset ( "Opnames", dbOpenDynaset ) Do While Not OpnamenSet.EOF

' Herhalingsconstructie om de set te doorlopen

Informatie = OpnamenSet![Recording Title] & " van " & _ OpnamenSet![Recording Artist ID] & Chr(13) & "is " & _ OpnamenSet!Keren_gespeeld & " keren gespeeld." & Chr(13) Informatie = Informatie & Chr(13) & "Moet dit aantal keren met 1 verhoogd worden?" Antwoord = MsgBox ( Informatie, vbYesNo, "Registratie spelen opnames" ) If Antwoord = vbYes Then

OpnamenSet.Edit

' gereed maken voor wijzigen

OpnamenSet!Keren_gespeeld = OpnamenSet!Keren_gespeeld + 1

OpnamenSet.Update

' wijziging terugschrijven naar gegevenstabel

End If OpnamenSet.MoveNext Loop OpnamenSet.Close End Sub

' Recordset sluiten

Bij het gebruik van Dynasets zijn behalve de reeds getoonde methodes Edit en Update ook alle al bij de Snapshots besproken methodes te gebruiken. Ook zijn bij Dynasets mogelijk: Delete en AddNew en Requery (om eventuele na Update gewijzigde gegevens ook in een actief scherm getoond te krijgen).

Practicumopdracht 4.b

(Wijzigen bondslidmaatschap via gebruik van Dynaset)

Maak een variant op je Ledenoverzicht-form van vorige practicumopdracht, waarop je een extra button plaatst waardoor het mogelijk wordt om de gehele ledenpopulatie een voor een af te lopen en via een MsgBox te laten zien of dat lid (geef de volledige naam) wel/niet bondslid is en met de vraag of dat bondslidmaatschap gewijzigd moet worden. Een klik op de ‘Ja’-button moet (via een Dynaset) de wijziging doorvoeren, via ‘Nee’ ga je door naar het volgende record en via ‘Annuleren’ wordt het gehele proces afgebroken. Hiernaast is de invulling van zo’n MsgBox getoond. N.B. herinner je in geval van een foutmelding over 'Dim db AS Database' het eerdere ‘intermezzo’.

10


4.3

Practicumopdracht 4

Het gebruik van QueryDef-variabelen bij het maken van Recordsets

In de hiervoor besproken voorbeelden hebben we Recordsets òf gebaseerd op een tabel, òf op een onder een bepaalde naam opgeslagen SQL-query, òf we hebben in de OpenRecordSet-uitdrukking de tekst (als String) van de SQL-query ingevoerd. Een krachtigere variant op die laatste mogelijkheid is het in je programma-code eerst onder een bepaalde naam definiëren van een zogenaamde QueryDef (inderdaad: lees dit maar als afkorting van query-definitie) en die QueryDef daarna te gebruiken om (bijvoorbeeld) een Recordset op te baseren. De grote kracht van deze constructie bestaat er uit, dat we in staat zijn om een of meer parameterwaarden mee te geven bij aanroep van zo’n QueryDef. Een erop gebaseerde Recordset kan daardoor veel flexibeler opgebouwd en gebruikt worden. Uit de MS Access-Help halen we het volgende stukje toelichting: U kunt met het QueryDef-object een query definiëren. U kunt bijvoorbeeld het volgende doen: • U kunt met de eigenschap SQL de querydefinitie instellen of opvragen. • U kunt met de Parameters-collectie van het QueryDef-object queryparameters instellen of opvragen. • U kunt met de eigenschap Type een waarde opvragen die aangeeft of er records uit een bestaande tabel worden geselecteerd, of er een nieuwe tabel wordt gemaakt, of er records uit de ene tabel in een andere tabel worden ingevoegd, of er records worden verwijderd en of er records worden bijgewerkt door de query. U maakt een nieuw QueryDef-object met de methode CreateQueryDef. In een Microsoft Jet-werkruimte geeft u een tekenreeks voor het argument naam op of stelt u expliciet de eigenschap Name van het nieuwe QueryDef-object op een andere tekenreeks dan een tekenreeks met lengte nul in. Op deze manier maakt u een permanent QueryDef-object dat automatisch aan de QueryDefs-collectie wordt toegevoegd en op schijf wordt opgeslagen. Als u een tekenreeks met lengte nul voor het argument naam opgeeft of de eigenschap Name expliciet instelt op een tekenreeks met lengte nul, maakt u een tijdelijk QueryDef-object. Als het object dat met naam wordt aangegeven al een lid is van de QueryDefs-collectie, treedt er een runtimefout op. U kunt een tijdelijke QueryDef maken door een tekenreeks van nul tekens te gebruiken voor het argument naam bij het uitvoeren van de methode CreateQueryDef. Hetzelfde effect bereikt u door de eigenschap Name van een zojuist gemaakte QueryDef de instelling van een tekenreeks met nul tekens ("") te geven. Tijdelijke QueryDef-objecten zijn handig als u regelmatig dynamische SQL-instructies wilt gebruiken zonder dat u nieuwe, permanente objecten in de QueryDefs-collectie hoeft te maken. U kunt geen tijdelijke QueryDef aan een willekeurige collectie toevoegen omdat een tekenreeks van nul tekens geen geldige naam vormt voor een permanent QueryDef-object. U kunt te allen tijde de eigenschappen Name en SQL van het zojuist gemaakte QueryDef-object instellen en vervolgens de QueryDef aan de QueryDefs-collectie toevoegen. Als u de SQL-instructie in een QueryDef-object wilt uitvoeren, gebruikt u de Execute of methode OpenRecordset.

We geven hieronder een voorbeeld met een uitwerking van zo’n QueryDef. Het voorbeeld behelst het via een meegegeven (Integer) parameterwaarde voor de ‘Recording ID’ van een opname kunnen laten opzoeken of zo’n nummer wel bestaat en zo ja, welke titel+artiest erbij behoort. In dit voorbeeld stoppen we eerst de gehele parameterformulering+bijbehorende SQL-tekst in een aparte string; die string gebruiken we daarna om de query-definitie te kunnen maken. Dit kan ook in één stap (dus zonder die aparte hulpstring te gebruiken). Sub QueryVoorbeeldMetParameter ( Opnummer As Integer ) Dim OnzeDBS As Database, OnzeQuerydef As QueryDef Dim OnzeRecordset As Recordset, SQLString As String Set OnzeDBS = CurrentDb ' Bouw SQL-string op: SQLString = "PARAMETERS Nummer Integer ; SELECT * FROM Opnames " & _ "WHERE [Recording ID] = Nummer ;" ' Let op declaratie+gebruik parameternaam ' Maak nieuw, tijdelijk(!) QueryDef object. Set OnzeQuerydef = OnzeDBS.CreateQueryDef ( "", SQLString ) OnzeQuerydef.Parameters!Nummer = Opnummer

‘ tijdelijk, dus: “” ' Let op koppelingswijze

Set OnzeRecordset = OnzeQuerydef.OpenRecordset ( dbOpenSnapshot )

11


Practicumopdracht 4

If OnzeRecordset.RecordCount > 0 Then ' er zal in dit geval maar één record in de set zitten MsgBox "Bij het opgegeven ID nummer " & Opnummer & " aangetroffen:" & Chr(13) & Chr(13) & _ OnzeRecordset![Recording Title] & " van " & OnzeRecordset![Recording Artist ID] Else MsgBox "Bij het opgegeven ID nummer " & Opnummer & Chr(13) & Chr(13) _ & "werd geen opname aangetroffen!" End If OnzeRecordset.Close OnzeQuerydef.Close End Sub

Bij aanroepen van deze procedure vanuit het Debug-window met: QueryVoorbeeldMetParameter(6) verschijnt de getoonde MsgBox.

Als je géén tijdelijke QueryDef, maar een ‘permanente’ QueryDef hebt gemaakt (door tussen de “” wél een naam te plaatsen), dan vinden we via het Queries-tab-blad van het database-venster met die opgegeven naam de volgende SQL-query: PARAMETERS Nummer Long ; SELECT * FROM Opnames WHERE ((( Opnames.[Recording ID] ) = [ Nummer ] ) ) ;

Duidelijk is daarin het woord ‘parameters’ terug te vinden. Als je deze parameterquery zonder meer opstart (via ‘Openen’), dan wordt via een InputBox gevraagd een parameterwaarde in te voeren. Daarna wordt de query met die ingetikte waarde alsnog uitgevoerd. Let erop dat als je een ‘permanente’ QueryDef onder een bepaalde naam wilt aanmaken en er al een query onder die naam opgeslagen is in de database, je dan een foutmelding krijgt. Diezelfde foutmelding krijg je uiteraard ook als je in een procedure een permanente QueryDef maakt [en die niet opruimt] en die procedure daarna nog een keer aanroept . .

Practicumopdracht 4.c

(Gegevens opzoeken via QueryDef-met-parameter)

Maak een procedure ToonAantalVoorkomensPlaats die je met een plaatsnaam als parameter kunt oproepen en die via een MsgBox toont hoe vaak die opgegeven plaatsnaam in het ledenbestand voorkomt en als die plaatsnaam niet voorkomt, daar een melding overgeeft. De gevraagde MsgBox moet er zoals hier getoond uitzien.

5

Debuggen / wandelen door je eigen VBA-code

Als je gebruik maakt van een zelf geschreven VBA-procedure (Sub of Function), dan kan het gebeuren dat het systeem een fout aantreft bij het doorlopen van de code van die procedure. Hoe kun je nu te werk gaan om je code te ‘debuggen’? Hier volgen een aantal aanwijzingen daarvoor (met dank aan ex-student-assistent Linus Wiggers): • Het rode bolletje is een onderbrekingspunt, aan of uit te zetten door in de grijze kantlijn te klikken. Je kunt dit toevoegen om te stoppen met het uitvoren van code. Onderbrekingspunten kunnen ook worden ingevoegd met het handje bovenaan de knoppenbalk. • Het gele pijltje is de plek waar het programma in de uitvoering is. Dit pijltje is te verslepen zodat je stukken programma code kunt overslaan of opnieuw kunt uitvoeren. Tijdens het uitvoeren kan code worden gewijzigd en door het gele pijltje terug te zetten kun je dan direct de code testen.

12


Practicumopdracht 4

• Om de actuele waarde in een variabele te zien, kun je de cursor op de naam van de variabele zetten. Vervolgens verschijnt als tooltip de naam en de waarde die de variabele heeft. • Om de code verder uit te voeren kun je gebruik maken van de drie knoppen, rechtsboven in het plaatje. Van links naar rechts: 1 stap zetten, een procedure overslaan, de betreffende procedure uitvoeren en daarna weer stoppen. • Het stapeltje met het pijltje erboven dient om handmatig te compileren. Kan bij voorbaat al een hoop fouten voorkomen • De cascade witte vierkantjes met de diagonale pijl geeft de opgeroepen stack met procedures. Handig als er meerdere procedures worden aangeroepen. Verder zijn die knoppen niet echt interessant, ik (=Linus; ex-studentassistent) gebruik ze tenminste niet.

Lever je in de database opgeslagen uitwerkingen uiterlijk ten tijde van het volgende B3-werkcollege in per email naar <[email protected]> N.B. Vergeet de gevraagde‘evaluatie’ van bij opdracht 4.a niet (stuur die ook per email op)!!

‘Compacteer’ (via Tools/Database Utilities/Compact Database) je database voordat je hem inlevert !!!

13

B3-cursus. Ontwikkelen met MS Access (vervolg)

Recommend Documents