Inform 5-6. Toegepaste Informatica. Boekhouden-informatica Informaticabeheer. Deel 4c: Programmeren in VBA ADO-objecten (Access )

Info-books

BI4c

Inform 5-6

Toegepaste Informatica Boekhouden-informatica Informaticabeheer

Deel 4c: Programmeren in VBA ADO-objecten (Access 2000 - 2002) J. Gils – E. Goossens

Hoofdstuk 8

8.1

Functies ontwerpen

Probleemstelling Voor heel wat probleempjes bestaat de oplossing uit een enkelvoudig resultaat, bijvoorbeeld het gemiddelde berekenen van een aantal getallen. Daarom is het handig dat er procedures bestaan die één waarde als resultaat terugsturen. Dergelijke procedures heten functies of functieprocedures. Sommige zijn ingebouwd maar je kunt er ook zelf schrijven.

8.2

Wat zijn functies? Functieprocedures (meestal ook gewoon functies genoemd) geven een waarde als resultaat, procedures doen dat niet. Visual Basic bevat veel ingebouwde functies. De functie NOW() geeft bijvoorbeeld de huidige datum en tijd als resultaat. Afgezien van deze ingebouwde functies, kun je ook je eigen, aangepaste functies maken. Deze functies kunnen ofwel in één bepaalde module (private) of in de hele database (public) gekend zijn. Omdat functies waarden als resultaat geven, kun je ze ook in expressies gebruiken. datDag = strGetal strGetal intGetal

Now() = Str(intGetal) = Str(Now()) = Val(strGetal)

Functies worden meestal opgeroepen met argumenten. Een argument geeft aanvullende informatie door aan de functie of aan de procedure. Als je een functie declareert moet je ook argumenten declareren als je aanvullende informatie aan de functie wilt doorgeven als je ze oproept. Wanneer je een functie met argumenten oproept, moet je ook de argumenten opgeven die je aan de functie wilt doorgeven. Syntaxis [Public | Private] [Static] Function Naam [(argumenten)] [As type] [instructies] ..[Naam = expressie] ..[Exit Function] ← te mijden End Function

78

Programmeren in VBA

J. Gils – E. Goossens

Voorbeeld: Function Groepjes(strTekst As String, bytAantal as byte) As String Dim bytLengte As Byte Dim intTeller As Integer Dim strHulp As String, strResultaat As String ‘Hier gebeurt de verwerking: de cijfers van het getal dat in strTekst ‘zit worden gesplitst in groepjes van bytAantal cijfers, gescheiden ‘door spaties. ‘Het resultaat wordt in strResultaat bewaard. Groepjes = strResultaat End Function

Deze functie heet Groepjes(). Ze wordt als volgt vanuit een andere procedure opgeroepen: strUitkomst= “12345” [txtUitkomst] = Groepjes(strUitkomst,3)

De waarde van strUitkomst wordt doorgegeven aan de variabele strTekst die in de kop van de functie gedeclareerd is en de waarde 3 wordt doorgegeven aan de variabele bytAantal. De functie zelf is gedeclareerd met het type String. In de functie gebeurt een hele verwerking. Het resultaat van die verwerking kan alleen maar via de functienaam naar de variabele txtUitkomst uit het hoofdprogramma teruggestuurd worden. In feite wordt hier niet de waarde van strResultaat doorgegeven, maar wel het adres waar strResultaat in het geheugen zit. Dit adres wordt doorgegeven aan strTekst. Deze manier van doorgeven heet ‘call by reference’.

8.3

Waarden aan procedures en functies meegeven Het is mogelijk om via een parameterlijst informatie mee te geven bij de aanroep van een procedure of een functie. De parametervariabelen van een procedure staan nooit tussen haakjes, die van een functie altijd! Er zijn 2 mogelijkheden:  Call by reference Niet de inhoud van de variabele wordt doorgegeven, maar wel een verwijzing naar de geheugenplaats waar de variabele zit. Binnen de functie of procedure wordt aan deze verwijzing een nieuwe naam toegekend. Als de inhoud van die nieuwe variabele gewijzigd wordt, dan wordt daardoor ook de inhoud van de oorspronkelijke variabele gewijzigd. Procedure: Declaratie: Sub Test(ByRef intGetal As Integer) → intGetal is een parametervaria-

bele

J. Gils – E. Goossens

Programmeren in VBA

79

of kortweg: Sub Test (intGetal As Integer) → ByRef hoef je niet te schrijven intGetal = intGetal * 2 End Sub

Het gegevenstype van de meegegeven expressie moet overeenstemmen met het gegevenstype van de procedure. De procedure oproepen: call Test intWaarde

→ het adres waar intWaarde naar wijst wordt doorgegeven of kortweg: Test intWaarde → call hoef je niet te schrijven

Als in de procedure Test de variabele intGetal van waarde verandert, dan zal ook de waarde van intWaarde in het hoofdprogramma veranderen, zelfs al is intWaarde geen publieke variabele. Dit komt omdat twee variabelen naar dezelfde geheugenplaats wijzen en de waarde van een variabele is niets anders dan de inhoud van die geheugenplaats. Functie: Declaratie: Function Test (intGetal As Integer) as Integer intGetal = intGetal * 2 Test = intGetal End Function

De functie oproepen: intResultaat = Test(intWaarde)

Als in de functie Test de variabele intGetal van waarde verandert, dan zal ook de waarde van intWaarde in het hoofdprogramma veranderen.  Call by value In de procedure of functie wordt een kopie van de variabele gemaakt zodat wijzigingen van de parametervariabele geen effect hebben op de aanroepende procedure. Declaratie: Function Test (ByVal intGetal As Integer) As Integer intGetal = intGetal * 2 Test = intGetal End Function

De functie oproepen: intResultaat = Test(intWaarde)→ de waarde van getal wordt doorgegeven

80

Programmeren in VBA

J. Gils – E. Goossens

De functie zal de variabele intWaarde niet kunnen wijzigen, tenzij intWaarde publiek is en er in de functie duidelijk staat dat: intWaarde = “iets”. Het is mogelijk Call by reference en Call by value te mengen: Sub Test(ByVal intGetal As Integer,ByVal x As Integer,y As Integer) ... End Sub

8.4

Functies kunnen waarden teruggeven Functies bieden de mogelijkheid om informatie aan de aanroepende procedure terug te geven. Hierbij staat de naam van de functie voor een afzonderlijke “terug te geven” waarde. Binnen de functie moet de berekende waarde aan de naam van de functie toegewezen worden. Function Test (intGetal As Integer) As Integer Test = intGetal *2 End Function … intResultaat = Test(5)

Het voordeel t.o.v. een procedure is dat je een functie ook kunt gebruiken in een bewerking: intResultaat=Test(5) * Test(3)

Merk op dat de parametervariabelen bij het oproepen van de functie altijd tussen haakjes staan. Opdrachten 1. Een functie vanuit het venster DIRECT oproepen a. Open een nieuwe module en noem ze BASTEST. b. Kopieeer hierin uit het formulier FRMKLINKERS (pagina 76) de procedure waarbij de klinkers in een woord door sterretjes vervangen worden worden. c. Verander de procedure in een functie STERRETJES die je uit het venster Direct als volgt oproept: ? Sterretjes (“Stella Matutina”) → resultaat: St*ll* M*t*t*n*. 2. Schrijf een functie EVEN die test of een geheel getal even of oneven is. ? Even(13) → resultaat: onwaar.

J. Gils – E. Goossens

Programmeren in VBA

81

Samenvatting procedures en functies

Option Compare Database Option Explicit '[txtGetal] = "123" Private Sub Test_Sub_Ref(ByRef strHulp As String) strHulp = "*" + strHulp + "*" End Sub Private Sub Test_Sub_Val(ByVal strHulp As String) strHulp = "*" + strHulp + "*" End Sub Private Function Test_Function(strHulp As String) As String strHulp = "*" + strHulp + "*" test_function = strHulp End Function Private Sub CmdRef_click()

Private Sub cmdVal_click() Private Sub cmdFunction_click()

Dim strGetal As String [txtRef] = ""

Dim strGetal As String [txtVal] = ""

Dim strGetal As String [txtFunction] = ""

strGetal = txtGetal Test_Sub_Ref strGetal

strGetal = txtGetal Test_Sub_Val strGetal

[txtRef] = strGetal

[txtVal] = strGetal

strGetal = txtGetal strGetal = Test_Function(strGetal) [txtFunction]=strGetal

End Sub

End Sub

End Sub

Kun je de waarden van txtRef, txtVal en txtFunction verklaren?

82

Programmeren in VBA

J. Gils – E. Goossens

8.5

Cijfers in groepjes groeperen

8.5.1 Probleemstelling Volgens de BIN-normen moeten getallen met een spatie als scheidingsteken voor de duizendtallen afgebeeld worden. Als je hexadecimaal rekent moeten de cijfers in groepjes van twee gegroepeerd zijn en als je binair rekent in groepjes van vier. Natuurlijk is het geen probleem om dit via een gepaste opmaak op te lossen. Maar waarom zou je het gemakkelijk maken als het moeilijk ook kan? Ontwerp een functie GROEPJES() die een willekeurig geheel getal netjes opmaakt zoals je het wenst.

Omdat we deze functie ook nodig hebben in het rekenmachine-programma, moet je er rekening mee houden dat het resultaat vóór de opmaak reeds spaties kan bevatten. 8.5.2 Probleemanalyse Het ingevoerde getal is van het type tekst of String. Je moet er van uitgaan dat in dit getal ook spaties kunnen zitten. De string met de cijfers en eventuele spaties wordt aan de functie Groepjes() doorgegeven.  Verwijder eerst alle spaties die in de string zitten. Neem één na één de karakters en plaats die in een nieuwe string. Als het karakter een spatie is moet er niets gebeuren. Dit kan in een aparte functie Blanks() gebeuren.  Groepeer de cijfers, bijvoorbeeld in groepjes van drie, en voeg spaties toe. Neem de cijfers van achter naar voor en plaats ze in een nieuwe string. Nadat er drie cijfers genomen zijn en voordat het volgende cijfer toegevoegd wordt moet je een spatie toevoegen. De functie Groepjes() zal van het type String zijn. De eerste lijn ziet er als volgt uit: Function GROEPJES(strGetal As String, intAantal As Integer) As String De argumenten:  strGetal: het getal waarin spaties gezet worden; Omdat er ook met binaire en hexadecimale cijfers gewerkt moet worden kun je het getal alleen maar als string doorgeven.  intAantal: hoeveel cijfers moeten gegroepeerd worden? Ze wordt als volgt opgeroepen: [txtResultaat] = Groepjes([txtGetal], Val([txtGroep]))

J. Gils – E. Goossens

Programmeren in VBA

83

De functie Blanks() zal ook van het type String zijn. De eerste lijn ziet er als volgt uit: Function BLANKS(strGetal As String) As String Ze wordt als volgt opgeroepen: strGetal = Blanks(strGetal) 8.5.3 Structogram met variabelenlijst 

Functie Groepjes()

Declaratie strLetter, strGetal, intLengte, strResultaat, intAantal, intTeller strLetter ← “” strGetal ← invoer strResultaat ← “” (= nieuw resultaat) intAantal ← 3 (bijvoorbeeld groepjes van drie) strGetal ← blanks verwijderen intLengte ← lengte van strGetal Voor intTeller = 1 tot intLengte strLetter ← intTeller’ste letter van rechts in strGetal (intTeller <>1) And (intTeller MOD intAantal =1) Ja Nee strResultaat ← spatie + strResultaat strResultaat ← strLetter + strResultaat Volgende intTeller Stuur strResultaat terug 

Functie Blanks()

Declaratie strGetal, strLetter, strResultaat, intLengte, intTeller strGetal ← invoer strLetter ← “” strResultaat ← “” (= nieuw resultaat) intLengte ← lengte van strGetal Voor intTeller = 1 tot intLengte strLetter ← intTeller’ste letter in strGetal strLetter<> spatie Ja strResultaat ← strResultaat + strLetter Volgende intTeller Stuur strResultaat terug

84

Programmeren in VBA

Nee

J. Gils – E. Goossens

8.5.4 Programmacode van de functie 

Objecten Formulier Besturingselementen





frmGroepjes txtGetal, txtGroep, txtResultaat lblGetal, lblGroep, txtResultaat cmdGroepeer

Eigenschappen Naam frmGroepjes

Type Formulier

IUV I/U

txtGetal lblGetal txtGroep lblGroep txtResultaat

Tekstvak Label Tekstvak Label Tekstvak

I

lblResultaat cmdGroepeer

Label Opdrachtknop V

I U

Eigenschap bijschrift … tabvolgorde bijschrift tabvolgorde bijschrift ingeschakeld vergrendeld bijschrift tabvolgorde Bij klikken

Waarde Cijfers groeperen … 0 Getal: 1 Groepjes van: nee ja Resultaat: 2 gebeurt. proced.

Gebeurtenisprocedure en functies

Private Sub cmdGroepeer_Click() [txtResultaat] = Groepjes([txtGetal], Val([txtGroep])) End Sub

Private Function Groepjes(strGetal As String, intAantal As Integer) As String Dim intTeller As Integer Dim strResultaat As String ' eventueel aanwezige spaties verwijderen strGetal = Blanks(strGetal) ' duizendtallen met spaties scheiden strResultaat = "" For intTeller = 1 To Len(strGetal) If (intTeller Mod intAantal = 1) And (intTeller > 1) Then strResultaat = " " + strResultaat End If strResultaat=Mid(strGetal,Len(strGetal)-intTeller+1,1)+strResultaat Next intTeller Groepjes = strResultaat End Function

J. Gils – E. Goossens

Programmeren in VBA

85

Private Function Blanks(strGetal As String) As String Dim strLetter As String, strResultaat As String Dim intTeller As Integer Dim intLengte As Integer strResultaat = "" intLengte = Len(strGetal) For intTeller = 1 To intLengte strLetter = Mid(strGetal, intTeller, 1) If strLetter <> " " Then strResultaat = strResultaat + strLetter End If Next intTeller Blanks = strResultaat End Function

TIP Als je aftelt in een For-lus dan moet de teller van het type Integer zijn: het type Byte kan immers niet negatief worden. 8.5.5 De functiebibliotheek Kopieer de twee functies Groepjes() en Blanks() naar een moduleblad. Noem dit blad basPubliek. Verwijder de term “Private” uit de kop van de twee functies: zo worden deze functies publiek voor de hele database en kunnen ze opgeroepen worden vanuit gelijk welk formulier of rapport. TIP

86

Via het venster DIRECT kun je alleen maar functies en procedures uit een algemene module oproepen!

Programmeren in VBA

J. Gils – E. Goossens

8.6

Talstelsels

8.6.1 Probleemstelling Er wordt een geheel getal kleiner dan 2 miljard ingevoerd. Dit getal wordt decimaal afgebeeld in groepjes van drie cijfers, hexadecimaal in groepjes van twee cijfers, binair in groepjes van vier cijfers. Het karakter dat overeenkomt met de ASCII-waarde van de restdeling van het getal door 256 wordt eveneens getoond.

8.6.2 Probleemanalyse Het is de bedoeling dat je één enkele functie schrijft die zowel een hexadecimale als een binaire waarde kan berekenen. Deze functie wordt opgeroepen met twee parameters: enerzijds het getal dat omgezet moet worden en anderzijds het grondtal. Het resultaat van deze functie zal van het type String zijn omwille van de hexadecimale cijfers en omwille van de zeer lange binaire getallen.

Zolang de gehele uitkomst van de deling door het grondtal nog groter is dan of gelijk is aan dat grondtal moet je blijven delen door het grondtal. De eerste rest die gevonden wordt is het laagste cijfer van het omgezette getal. De laatste uitkomst is het hoogste cijfer van het omgezette getal. In geval het grondtal 16 is, kan de rest ook 10, 11, 12, 13, 14 en 15 zijn. Deze waarden moeten omgezet worden naar A, B, C, D, E en F. Voor deze omzetting schrijf je een aparte functie. J. Gils – E. Goossens

Programmeren in VBA

87

Zolang de rest kleiner is dan 10 is er geen probleem. Is de rest gelijk aan 10, dan krijgt de rest de waarde “A”. De ASCII-waarde van “A” is 65. Als je bij de ASCII-code 55 de waarde van de rest bijtelt heb je automatisch de ASCII-code van de gewenste letter. Via een functie CHR() kun je de ASCII-waarde omzetten naar een karakter. Nadat het getal omgezet is moet nog het juiste scheidingsteken toegevoegd worden. Hiertoe roep je een functie Groepjes() op met als parameters het omgezette getal (als string) en een getal dat aangeeft hoeveel cijfers er gegroepeerd moeten worden. 8.6.3 Structogram met variabelenlijst 

Functie Talstelsel()

Declaratie Talstelsel(lngGetal as Long, intGrondtal as Integer) as String lngRest, strRest, strResultaat lngGetal ← waarde van de invoer in [txtGetal] intGrondtal ← 2 strResultaat ← “” Zolang lngGetal >= intGrondtal lngRest ← lngGetal Mod intGrondtal lngGetal ← (lngGetal – lngRest) / intGrondtal ( of lngGetal ← lngGetal \ intGrondtal) strRest ← HexCijfer(lngRest ) strResultaat ← strRest & strResultaat strRest ← HexCijfer(lngGetal) strResultaat ← strRest & strResultaat Ingeval intGrondtal 2 10 strResultaat = GroepstrResultaat = Groepjes(strResultaat,4) jes(strResultaat,3) Talstelsel ← strResultaat 

Functie HexCijfer()

Declaratie HexCijfer(lngGetal as Long) as String strHexCijfer lngGetal ← getal van de oproepende functie Ingeval lngGetal van 0 tot 9 strHexCijfer ← lngGetal naar String Hexcijfer ← strHexCijfer

88

16 strResultaat = Groepjes(strResultaat,2)

van 10 tot 15 strHexCijfer ← CHR( 55 + lngGetal)

Programmeren in VBA

J. Gils – E. Goossens

8.6.4 Programmacode 

Objecten Formulier Besturingselementen





frmTalstelsel txtInvoer, txtDec, txtHex, txtAsc, txtBin lblInvoer, lblDec, lblHex, lblAsc, lblBin lblTitel1, lblTitel2

Eigenschappen Naam frmTalstelsel

Type Formulier

IUV I/U

txtInvoer

Tekstvak

I

lblInvoer

Label

txtDec txtHex txtAsc txtBin lblDec lblHex lblAsc lblBin lblTitel1 lblTitel2

Tekstvak

Label

U

Eigenschap Bijschrift … Tabvolgorde Na bijwerken Bijschrift Ingeschakeld Vergrendeld Bijschrift

Waarde Getallen omzetten … 0 Gebeurtenisprocedure Geef een geheel getal kleiner dan 2 miljard nee ja txtDec txtHex txtAsc txtBin Invoer: Uitvoer:

Procedures en functies

Private Sub txtInvoer_AfterUpdate() Dim lngInvoer As Long If [txtInvoer] <> "" Then lngInvoer = Val([txtInvoer]) [txtDec] = Talstelsel(lngInvoer, 10) [txtAsc] = Chr(lngInvoer Mod 256) [txtHex] = Talstelsel(lngInvoer, 16) [txtBin] = Talstelsel(lngInvoer, 2) End If [txtInvoer] = "" End Sub

J. Gils – E. Goossens

Programmeren in VBA

89

Private Function Talstelsel(ByVal lngGetal As Long, intGrondtal As Integer) As String Dim lngRest As Long Dim strResultaat As String, strRest As String lngRest = lngGetal strResultaat = "" Do While lngGetal >= intGrondtal lngRest = lngGetal Mod intGrondtal lngGetal = lngGetal \ intGrondtal strResultaat = HexCijfer(Cbyte(lngRest)) + strResultaat Loop strResultaat = HexCijfer(Cbyte(lngGetal)) + strResultaat Select Case intGrondtal Case 2 Talstelsel = Groepjes(strResultaat, 4) Case 10 Talstelsel = Groepjes(strResultaat, 3) Case 16 Talstelsel = Groepjes(strResultaat, 2) End Select End Function

In deze functie wordt gebruik gemaakt van “Call by value. De waarde van lngInvoer mag na de oproep [txtHex]=Talstelsel(lngInvoer,16) niet wijzigen omdat je dezelfde waarde nogmaals nodig hebt bij de oproep [txtBin]=Talstelsel(lngInvoer,2). Als lngInvoer “by reference” zou doorgegeven worden, dan zou het resultaat van [txtBin] fout zijn. Private Function HexCijfer(bytGetal As Byte) As String Dim strHexCijfer As String Select Case bytGetal Case 0 To 9 strHexCijfer = Trim(str(bytGetal)) Case 10 To 15 strHexCijfer = Chr(55 + bytGetal) End Select HexCijfer = strHexCijfer End Function

Voor de grondtallen 2 en 10 is deze functie overbodig. Eventueel kun je via een keuze alleen de getallen die groter dan 9 zijn, als het grondtal 16 is, naar deze functie doorsturen. Omdat in deze functie bytGetal van het type Byte is, moet tijdens de oproep van deze functie een conversie gebeuren van het type Long naar het type Byte: HexCijfer(Cbyte(lngRest)) Breng de functie HexCijfer() en Talstelsel() ook over naar de functiebibliotheek basProcedure en verwijder het woordje “private” uit het declaratiegedeelte.

90

Programmeren in VBA

J. Gils – E. Goossens

8.7

Enkele belangrijke functies

8.7.1 Conversiefuncties Cbool(expressie)

Convert. expressie naar Boolean

Check = CBool(A = B)

Cdate(expressie)

Convert. expressie naar Date

Cdate("10 Mei, 1996")

Ccur(getal)

Convert. getal naar Currency

Ccur(125) → € 125

Cbyte(getal)

Convert. expressie Byte

CByte(125.5678) → 125

Cint(expressie)

Convert. expressie nr Integer

CLng(expressie)

Convert. expressie naar Long

CSng(expressie)

Convert. expressie naar Single

CDbl(expressie)

Convert. expressie nr Double

CStr(expressie)

Convert. expressie naar String

Cvar(expressie)

Convert. expressie naar Variant

CVErr(expressie)

Convert. error-code naar Variant

Format(expressie,,,)

geeft een bep. opmaak aan een ex- Format(5459.4, "##,##0.00") pressie → "5 459,40".

Hex(getal)

string met hexadecimale voorstelling van het getal

MyHex = Hex(459) → 1CB.

Oct(getal)

string met octale waarde van het getal

MyOct = Oct(459) → 713.

CStr(437.324) → "437.324"

8.7.2 Stringfuncties Len(string)

lengte van string

Lcase(string)

string naar kleine letters

LCase(“SINT”) → “sint”

Ucase(string)

string naar hoofdletters

Ucase (“Sint” → “SINT”

Ltrim(string)

linker spaties verwijderen

Rtrim(string)

rechter spaties verwijderen

Trim(string)

begin- en eindspaties weg

Left(string,lengte)

linkse deel van een string

Left(“Sint”,1) → “S”

Right(string,lengte)

rechtse deel van een string

Right(“Sint”,2) → “nt”

Mid(string,start,lengte)

deel uit een string

Mid(“Sint”,2,2) → “in”

J. Gils – E. Goossens

Programmeren in VBA

91

InStr(start,doelstring, zoekstring)

zoekt naar de plaats van een lettercombinatie

InStr(1,”iets”,”t”) → 3

String(aantal,teken)

repeterende string opbouwen

String(5, "*") → "*****"

Asc(string)

geeft de ascii-waarde van de eerste letter van de string

ASC(“Appel”) → 65

Chr(getal)

geeft het teken dat bij de code CHR(65) → “A” hoort

Str(getal)

getal omzetten naar string

Str(-459.65) → ”-459.65”

Val(string)

string omzetten naar getal

val("10 okt”) → 10.

Een aantal functies heeft twee versies:  de ene versie geeft het gegevenstype Variant als resultaat, bijvoorbeeld Mid();  de andere het gegevenstype String, bijvoorbeeld Mid$(). De Variant-versies krijgen de voorkeur omdat varianten conversies tussen verschillende typen gegevens automatisch afhandelen. In de volgende gevallen is het wel handig om de String-versie te gebruiken:  het programma is erg groot en er worden veel variabelen in gebruikt;  je wilt controleren wanneer gegevens van het ene type worden geconverteerd naar een ander type;  je schrijft gegevens rechtstreeks naar bestanden met directe toegang. De volgende functies geven een variabele van het type String als resultaat wanneer je een dollarteken ($) achter de functienaam plaatst. Deze functies hebben dezelfde syntaxis als de Variant-versies zonder het dollarteken en worden op dezelfde manier gebruikt. Chr$ Dir$ Input$ LeftB$ Right$ Str$ UCase$

ChrB$ Error$ InputB$ Mid$ RightB$ String$

CurDir$ Format$ LCase$ MidB$ RTrim$ Time$

Date$ Hex$ Left$ Oct$ Space$ Trim$

8.7.3 Rekenkundige functies Fix(-8,4)= -8

Het gedeelte voor de komma van een getal.

Int (-8,4)= -9

Het gedeelte voor de komma van een getal.

Rnd()

Rnd geeft een willekeurig getal < 1 als resultaat. Gebruik de volgende formule als je willekeurige integers in een gegeven reeks wilt maken: Int((bovengrens – benedengrens + 1) * Rnd + benedengrens)

92

Programmeren in VBA

J. Gils – E. Goossens

Randomize

Om de generator van willekeurige getallen te starten met een begingetal op basis van de systeemtimer.

Sgn(getal)

Bepaalt of een getal positief (1), nul (0) of negatief (-1) is.

Sqr(getal)

Geeft de vierkantswortel van een getal als resultaat.

Cos (functie) Sin (functie) Tan (functie) Abs(getal)

Geeft de absolute waarde van een getal als resultaat.

Exp (getal)

Het getal e (de basis van natuurlijke logaritmen) verheven tot een bepaalde macht.

Ln/log(getal)

Geeft als resultaat de natuurlijke logaritme van een getal.

Boogtan/Atn(getal) 8.7.4 Datum- en tijdfuncties Date()

systeemdatum

Date() → “29/07/96”

Time()

systeemtijd

Time() → “21:25:17”

Now()

systeemdatum en -tijd

Now() → “29/07/96 21:26:27”

Time(datum)

tijd in uur:minuten:seconden

Time( Now() ) → “15:34:12”

Year(datum)

jaartal uit de datum

Year( Date() ) → 1996

Month(datum)

nummer van de maand

Month( Now() ) → 7

Weekday(datum)

nummer van de weekdag (zondag = 1)

Weekday( Now() ) → 2 (maandag)

Day(datum)

nummer van de dag

Day( Now() ) → 29

Hour(datum)

uur van de dag (0 -23)

Hour(“15:34:12”) → 15

Minute(datum)

minuten van het uur (0 –59)

Second(datum)

seconden (0 - 59)

Timer()

het aantal seconden sinds middernacht

J. Gils – E. Goossens

Programmeren in VBA

Timer() → 77499,65

93

Besluit Functies geven een waarde als resultaat terug: daarom kunnen ze rechtstreeks in expressies gebruikt worden. Procedures geven na oproep geen waarde terug. Tijdens de declaratie van de functie leg je het type vast en bepaal je met welke parameters, en van welk type, de functie opgeroepen wordt. Deze declaratie gebeurt in de eerste lijn van de functie. De variabelen waarmee je de functie oproept moeten van hetzelfde type zijn als de parameters die in het functiehoofd gedeclareerd zijn. Functies kunnen zowel private als public gedeclareerd worden. In het eerste geval zijn ze alleen maar op te roepen binnen de module waarin ze gedeclareerd zijn, in het andere geval zijn ze op te roepen vanuit de hele database. Wil je een bibliotheek van functies aanleggen die bereikbaar is voor de hele database, dan verzamel je die functies in een aparte module, een standaard module, en je verwijdert het woordje ‘private’ uit de declaratie van elke functie. Waarden kunnen aan een functie doorgegeven worden by reference of by value. “Call by Reference” geeft alleen het adres van de variabele door. In dat geval kan de inhoud van de variabele ook binnen de functie gewijzigd worden. “Call by value” kopieert de variabele die wordt doorgegeven naar een nieuwe variabele. In dat geval zal de oorspronkelijke variabele nooit gewijzigd kunnen worden binnen die functie. De parameters waarmee je een functie oproept staan altijd tussen ronde haken. Bij een procedure is dat juist niet het geval.

Wat je moet kennen en kunnen:      

zelf functies definiëren, implementeren en gebruiken; uitleggen wat ‘Private’ en ‘Public’ is bij de declaratie van functies; het verschil tussen ‘Call by reference’ en ‘Call by value’ uitleggen; procedures en functies zodanig schrijven dat ze herbruikbaar zijn; gebruikmaken van bestaande procedures en functies en deze eventueel aanpassen; herbruikbare procedures en functies groeperen in bibliotheken.

Opdrachten 3. Schrijf de functie Positie(strWoord As String, strLetter As String) As Byte die als resultaat de plaats aangeeft waar een bepaalde letter de eerste maal in een woord voorkomt. Gebruik deze functie om de postcode van de woonplaats te scheiden. 4. Schrijf de functie Splits(strWoord As String, bytDeel As Byte) As String die van een invoer die uit twee woorden bestaat die gescheiden zijn door een spatie, zoals naam en voornaam, ofwel het eerste (bytDeel=1) ofwel het tweede (bytDeel=2) woord als resultaat geeft. Schrijf een analoge functie SplitsChar() die splitst vanaf een opgegeven karakter.

94

Programmeren in VBA

J. Gils – E. Goossens

5. Schrijf de functie Invoegen(strWoord As String, strExtra As String, bytPositie As Byte) As String die een bepaalde tekenreeks invoegt op een bepaalde plaats in een woord. Gebruik deze functie om een spatie te zetten tussen de postcode en de woonplaats, in de veronderstelling dat de postcode en de woonplaats vast aan mekaar geschreven zijn. 6. Schrijf de functie Rest(lngGetal As Long,lngDeeltal As Long) As Long die de rest van de deling van het getal door de deler weergeeft. Gebruik deze functie in het programma talstelsel van FRMTALSTELSEL. 7. Schrijf de functie Afronden(dblGetal As Double, intHoever As Integer) As Double die een getal tot op intHoever aantal cijfers na de komma afrondt. 8. Ontwerp een formulier FRMKLEUR om de functie RGB() te testen. Via een opdrachtknop kies je een kleur, met de pijlknoppen (cmdOmhoog, cmdOmlaag) kun je de getalwaarde (intRood, intGroen of intBlauw) van die kleur vermeerderen of verminderen. De kleurknoppen zet je in een groepsvak zodat er maar één knop geselecteerd kan zijn. TIPS  IntRood, intGroen en intBlauw moeten globale variabelen zijn.  maak zoveel mogelijk gebruik van procedures.

De achtergrond van het tekstvak txtRood, … toont je alleen maar de kleur van intRood: de getalwaarde van die kleur druk je af in txtRoodWaarde. In een groot tekstvak, txtKleur toon je het resultaat van de drie kleuren samen. Bij het openen van het formulier is er geen kleur geactiveerd en zijn de pijlknoppen verborgen. De functie RGB(intRood,intGroen,intBlauw) geeft als resultaat een Long-waarde met een geheel getal dat een RGB-kleurwaarde aangeeft. intRood intGroen intBlauw TIP

Een getal in het bereik van 0 tot en met 255 dat de rode component van de kleur aangeeft. Een getal in het bereik van 0 tot en met 255 dat de groene component van de kleur aangeeft Een getal in het bereik van 0 tot en met 255 dat de blauwe component van de kleur aangeeft.

De functie RGB() kan als volgt gebruikt worden: txtKleur.BackColor = RGB(255,255,255) ← Wit txtKleur.BackColor = RGB(255,0,0) ← Rood txtKleur.BackColor = RGB(0,0,0) ← Zwart

J. Gils – E. Goossens

Programmeren in VBA

95

Hoofdstuk 14

Records rangschikken en zoeken

14.1 Probleemstelling Regelmatig moeten gegevens in tabellen opgezocht worden. Soms moet er alleen maar vooruit gezocht worden, in andere gevallen moet in de twee richtingen gezocht worden. Voor bepaalde toepassingen is het nodig dat de gegevens in een tabel gesorteerd worden: je kunt de volgorde met een index vastleggen of je kunt een kopie van een bestaande tabel maken waarbij de records ook fysisch in de gewenste volgorde liggen. Soms is het nodig dat je met behulp van een filter slechts toegang krijgt tot een deel van de records van een tabel.

14.2 Records op basis van bestaande indexen rangschikken Records in basistabellen worden niet in een bepaalde volgorde opgeslagen. Indexen maken het mogelijk om de records in een bepaalde volgorde te tonen. Indexen hebben geen invloed op de volgorde waarin de records opgeslagen worden. In Access definieer je een index via het tabelontwerp en dan kies je INDEXEN. De tabel TBLSPONSOR uit de database CENTENXP bevat bijvoorbeeld de volgende indexen:

De indexnaam van de primaire sleutel is ‘PrimaryKey’. Standaard is de indexnaam altijd gelijk aan de veldnaam. Je kunt de indexnaam wijzigen: in dat geval gebruik je de tag ‘idx’. Een Recordset-object dat naar een tabel verwijst heeft een eigenschap Index waarmee de recordvolgorde in het Recordset-object bepaald wordt. De eigenschap Index wordt ingesteld na het toewijzen van een tabel aan een Recordset-object. Het opgegeven Index-object, in dit voorbeeld IDXWOONPLAATS, moet al in de tabel TBLSPONSOR gedefinieerd zijn. Het volgende voorbeeld drukt in het venster Direct de naam, de woonplaats en de bedrijfscode van alle sponsors uit de tabel CENTENXP af, per woonplaats gesorteerd. De procedure wordt opgeroepen vanuit de module BASADO3 vanuit de database ADO.

J. Gils – E. Goossens

Programmeren in VBA

147

Sub ADOTabelIndex() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\access\CentenXP.mdb;" rst.CursorType = adOpenKeyset rst.LockType = adLockReadOnly rst.Open "tblSponsor", cnn, , , adCmdTableDirect If rst.Supports(adIndex) Then rst.Index = "idxWoonplaats" rst.MoveFirst Do While Not rst.EOF Debug.Print rst!SOORT, rst!BEDRIJF, rst!WOONPLAATS rst.MoveNext Loop Else MsgBox "index wordt niet ondersteund" End If rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub

Verklaring: 

 

RST.OPEN "TBLSPONSOR", CNN, , , ADCMDTABLEDIRECT De optie ADCMDTABLEDIRECT zegt explicitiet dat de naam van de recordbron (Source: TBLSPONSOR) een tabel is waarvan alle kolommen moeten opgenomen worden. Deze

optie is essentieel voor de goede werking van de index. IF RST.SUPPORTS(ADINDEX) THEN Deze methode test of een index kan toegepast worden op de geopende recordset. RST.INDEX = "IDXWOONPLAATS" Het recordset-object heeft een eigenschap INDEX waarmee je de index kunt instellen.

Opdrachten 1. Neem de vorige listing over en zet deze procedure, zoals alle andere procedures van dit hoofdstuk, in een nieuwe module BASADO3. Test de werking in het venster Direct. Test ook wat er gebeurt als je de optie ADCMDTABLEDIRECT wijzigt. Gebruik de helpfunctie om de andere waarden te kennnen. 2. Wijzig het bovenstaande voorbeeld zodanig dat je via een dialoogvenster naar de naam van de index vraagt en druk dan het eerste en vervolgens om de 50 records een record af. Noem deze procedure ADOTABELINDEX2.

14.3 Records met de methode Seek zoeken De methode Seek kan alleen maar gebruikt worden in een geïndexeerd Recordset-object dat aan een tabel gekoppeld is. De methode Seek zoekt alleen maar in de ingestelde index. Als het zoekcriterium in de index gevonden is, dan wordt de recordpointer naar die record verplaatst zodat die record de huidige record wordt. 148

Programmeren in VBA

J. Gils – E. Goossens

De syntaxis van de methode Seek: recordset.Seek KeyValues, SeekOption KeyValues

Een matrix van Variant-waarden. Een index bestaat uit een of meer kolommen en de matrix bevat de waarden die met elke corresponderende kolom moeten worden vergeleken.

KeyOptions

Een SeekEnum-waarde die aangeeft welk type vergelijking moet worden uitgevoerd tussen de kolommen van de index en de corresponderende KeyValues.

TIP

Als Seek de gewenste rij niet vindt, treedt geen fout op en wordt de huidige rij aan het einde van de Recordset geplaatst.

De verschillende waarden van KeyOptions: adSeekFirstEQ

1

Zoekt de eerste sleutel die gelijk is aan KeyValues.

adSeekLastEQ

2

Zoekt de laatste sleutel die gelijk is aan KeyValues.

adSeekAfterEQ

4

Zoekt een sleutel die gelijk is aan KeyValues of de positie direct volgend op de plaats waar deze overeenkomst zou zijn voorgekomen.

adSeekAfter

8

Zoekt een sleutel direct volgend op de plaats waar een overeenkomst met KeyValues zou zijn voorgekomen.

adSeekBeforeEQ

16

Zoekt een sleutelwaarde die gelijk is aan KeyValues of de positie direct voor de plaats waar deze overeenkomst zou zijn voorgekomen.

adSeekBefore

32

Zoekt een sleutel direct voor de plaats waar een overeenkomst met KeyValues zou zijn voorgekomen.

De methode Seek kan alleen gebruikt worden wanneer het Recordset-object geopend is met de CommandTypeEnum-waarde adCmdTableDirect. Voordat je Seek uitvoert, moet je de huidige index instellen met de eigenschap Index. Als de index een niet-uniek sleutelveld aangeeft, wordt met behulp van Seek de eerste record gevonden die aan de criteria voldoet. Het volgende voorbeeld vraagt naar een woonplaats en toont dan alle sponsors van die woonplaats. De procedure ADOTABELSEEK wordt in de database ADO bewaard en haalt de gegevens uit de tabel TBLSPONSOR van de database CENTENXP.

J. Gils – E. Goossens

Programmeren in VBA

149

Sub ADOTabelSeek() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim strSeek As String cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\access\CentenXP.mdb;" rst.CursorType = adOpenKeyset rst.LockType = adLockReadOnly rst.Open "tblSponsor", cnn, , , adCmdTableDirect strSeek = InputBox("Geef de naam van een woonplaats") If rst.Supports(adIndex) And rst.Supports(adSeek) Then rst.Index = "idxWoonplaats" rst.Seek Array(strSeek), adSeekFirstEQ If Not rst.EOF Then Do While Not rst.EOF And rst!WOONPLAATS = strSeek Debug.Print rst!SOORT, rst!BEDRIJF, rst!WOONPLAATS rst.MoveNext Loop Else MsgBox "Geen records gevonden" End If Else MsgBox "Index en seek niet ondersteund" End If rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub

TIP

Omdat je in een index zoekt, zullen alle records die aan de zoekstring voldoen netjes achter mekaar staan. Vandaar dat de methode Seek maar eenmaal uitgevoerd wordt.

Verklaring:   

RST.SUPPORTS(ADSEEK) Deze methode test of de methode Seek van toepassing is op deze recordset RST.SEEK ARRAY("GENT"), ADSEEKFIRSTEQ In de index, IDXWOONPLAATS, wordt gezocht naar eerste sleutel die gelijk is aan “Gent”. IF NOT RST.EOF THEN Als de recordpointer niet bij EOF staat, dan is er een sleutel gevonden. Omdat in een index gezocht wordt, zullen alle records die aan de zoekstring voldoen netjes achter mekaar staan. Vandaar dat de methode Seek maar eenmaal uitgevoerd wordt.

Opdrachten 3. Neem de vorige listing over en test de werking in het venster Direct. 4. Wijzig de programmacode zodaning dat alle woonplaatsen maar eenmaal afgedrukt worden en druk uiteindelijk ook het aantal verschillende woonplaatsen af. Maak gebruik van KeyOption AdSeekAfter. Noem deze procedure ADOTABELSEEK2.

150

Programmeren in VBA

J. Gils – E. Goossens

14.4 Sorteren met de eigenschap Sort Met de eigenschap Sort kun je de sorteervolgorde voor records in een Recordset-object instellen of als resultaat geven. Als er nog geen index bestaat, wordt een tijdelijke index gemaakt voor elk veld dat aangegeven wordt in de eigenschap Sort. Voor deze eigenschap moet de eigenschap CursorLocation zijn ingesteld op adUseClient. De sorteerbewerking is efficiënt omdat de gegevens niet fysiek worden herschikt, maar alleen worden benaderd in de volgorde die wordt aangegeven in de index. TIP

Als de waarde van de eigenschap Sort een lege reeks is (“”) dan krijgen de rijen hun oorspronkelijke volgorde terug en worden de tijdelijke indexen verwijderd. Bestaande indexen worden niet verwijderd.

De sorteersleutel van de eigenschap Sort moet je als een tekenreeks (String) toewijzen. Deze sleutel kan uit één of meer veldnamen bestaan. In feite bevat de sorteersleutel de waarde van de ORDER BY-component van een SQL-instructie, maar zonder de gereserveerde woorden ORDER BY. De standaardsorteervolgorde is oplopend (A tot Z of 0 tot 100). De achtervoegsels DESC (descending = aflopend) en ASC (ascending = oplopend) bepalen de sorteervolgorde. ASC is de default waarde en hoeft niet vermeld te worden. Het volgende voorbeeld opent een recordset die volgens een bepaald criterium gesorteerd wordt. Sub ADOEigenschapSort() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\access\CentenXP.mdb;" rst.CursorType = adOpenKeyset rst.LockType = adLockReadOnly rst.CursorLocation = adUseClient rst.Open "tblSponsor", cnn rst.Sort = "Soort DESC, Woonplaats, Bedrijf ASC" Do While Not rst.EOF Debug.Print rst!SOORT, rst!WOONPLAATS, rst!BEDRIJF rst.MoveNext Loop rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub

Verklaring: 

RST.CURSORLOCATION = ADUSECLIENT

Je moet duidelijk aangeven dat er lokaal gesorteerd moet worden: er wordt een kopie van alle records naar de cache van je computer gemaakt en er zullen ook tijdelijke indexen aangemaakt worden.

J. Gils – E. Goossens

Programmeren in VBA

151



RST.SORT = "SOORT DESC, WOONPLAATS, BEDRIJF ASC" De recordset wordt gesorteerd volgens drie sleutels: het veld SOORT aflopend, het veld WOONPLAATS bij default oplopend en het veld BEDRIJF oplopend.

Opdrachten 5. Neem de vorige listing over en test de werking in het venster Direct. 6. Wijzig de code zodanig dat eerst de gesorteerde lijst afgedrukt wordt en vervolgens nogmaals een ongesorteerde lijst. Druk niet alle records af, maar slechts om de vijf records. Noem deze procedure ADOEIGENSCHAPSORT2.

14.5 Records filteren met de eigenschap Filter De eigenschap Filter filtert selectief records in een Recordset-object. Het gefilterde Recordset-object wordt de huidige cursor. De huidige record wordt verplaatst naar de eerste record die voldoet aan de filtervoorwaarde. Wanneer de eigenschap Filter gewist is, wordt de aanwijzer voor de huidige record weer bij de eerste record in het Recordset-object geplaatst In de filterstring is het gebruik van logische operatoren (AND en OR) en vergelijkingsoperatoren (<, >, <=, >=, <>, = of LIKE) toegestaan. De filterstring staat tussen dubbele aanhalingstekens, de waarden waaraan de veldnamen moeten voldoen zet je tussen enkele aanhalingstekens als het om tekens gaat, tussen spoorwegtekens als het gezochte een datum is en helemaal niets als het gezochte een getal is. rst.Filter = "Soort like 'B*' and Woonplaats ='Antwerpen'" TIP

Als de veldnaam spaties bevat moet je hem tussen vierkante haken zetten.

Het is echter niet mogelijk om componenten te groeperen die met OR samengevoegd zijn en deze groep daarna met AND samen te voegen met een andere groep, zoals in het volgende voorbeeld: (SOORT=’BAK’ OR SOORT=’BHW’) AND BEDRAG>0. In plaats daarvan moet je de filter als volgt samenstellen: (SOORT=’BAK’ AND BEDRAG>0 ) OR (SOORT=’BHW’ AND BEDRAG>0) Het instellen van de eigenschap Filter op een reeks met lengte nul ("") heeft hetzelfde effect als het gebruik van de constante adFilterNone. TIP

Je kunt een filter ook vervangen door een SQL-instructie die een WHEREcomponent bevat bij het openen van de recordset.

Het volgende voorbeeld toont hoe je een filter instelt die alleen maar de sponsors waarvan het veld Soort begint met de letter ‘B’ uit Antwerpen. Vervolgens worden de soort, de woonplaats en de naam in het venster Direct afgedrukt.

152

Programmeren in VBA

J. Gils – E. Goossens

Sub ADOEigenschapFilter() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\access\CentenXP.mdb;" rst.CursorType = adOpenKeyset rst.LockType = adLockReadOnly rst.Open "tblSponsor", cnn rst.Filter = "Soort like 'B*' and Woonplaats ='Antwerpen'" Debug.Print rst.RecordCount Do While Not rst.EOF Debug.Print rst!SOORT, rst!WOONPLAATS, rst!BEDRIJF rst.MoveNext Loop rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub

Opdrachten 7. Neem de vorige listing over en test de werking in het venster Direct. 8. Wijzig de code zodanig dat de gesorteerde lijst afgedrukt wordt volgens Soort, Woonplaats en Bedrijf, met bovenaan een vermelding van het aantal records. Noem deze procedure ADOEIGENSCHAPFILTER2. 9. Maak een filter die een lijst van bedrijven uit Antwerpen geeft die nog niet gesponsord hebben. Noem deze procedure ADOEIGENSCHAPFILTER3. Haal de gegevens uit de tabel TBLSPONSOR van de database CENTENXP. Druk de lijst in het venster Direct af. Druk ook het aantal gevonden sponsors af.

14.6 Met de methode Find zoeken De Find-methode zoekt in een Recordset-object naar de record die voldoet aan de opgegeven zoekcriteria. De positie van de huidige record wordt ingesteld op de gevonden record. Als geen enkele record voldoet aan de zoekcriteria, danwordt de positie ingesteld op het einde (of het begin) van de recordset. De richting van de zoekopdracht, de beginrij en de verschuiving ten opzichte van de beginrij kunnen opgegeven worden. Syntaxis Find (Criteria, SkipRows, SearchDirection, Start) Criteria

Een waarde van het type String met een instructie die de kolomnaam, de vergelijkingsoperator en de waarde bevat die moeten gebruikt worden in de zoekopdracht.

SkipRows

Een optionele waarde van het type Long, waarvan de standaardwaarde nul is, die aangeeft bij welke rij ten opzichte van de huidige rij of beginbladwijzer de zoekopdracht moet beginnen. Standaard begint de

J. Gils – E. Goossens

Programmeren in VBA

153

zoekopdracht vanaf de huidige rij. SearchDirection Een waarde die aangeeft of de zoekopdracht moet beginnen bij de huidige rij of bij de eerstvolgende beschikbare rij in de richting van de zoekopdracht.  adSearchBackward ( -1) Zoekt achterwaarts en stopt bij het begin van het Recordset-object. Wordt geen overeenkomst gevonden, dan wordt de recordaanwijzer bij BOF geplaatst.  adSearchForward ( 1) Zoekt voorwaarts en stopt bij het einde van het Recordset-object. Wordt geen overeenkomst gevonden dan wordt de recordaanwijzer bij EOF geplaatst. Start

TIP

Een Variant-bladwijzer die als startpositie voor de zoekopdracht fungeert. Bij Criteria kan uitsluitend een naam voor één kolom worden opgegeven. Deze methode ondersteunt geen zoekopdrachten naar meerdere kolommen. In het criterium kunnen vergelijkingstekens en jokers voorkomen.

Het volgende voorbeeld toont hoe je in de tabel TBLSPONSOR naar de eerste letters van een woonplaats zoekt en vervolgens de bedrijven uit die woonplaats in het venster DIRECT afdrukt. Sub ADOMethodeFind() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim strZoek As String cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\access\CentenXP.mdb;" rst.CursorType = adOpenKeyset rst.LockType = adLockReadOnly rst.Open "tblSponsor", cnn strZoek = InputBox("Welke woonplaats wil je zoeken?") strZoek = "WOONPLAATS Like '" & strZoek & "*' " rst.Find strZoek,0, adSearchForward If rst.EOF Then MsgBox ("Het gevraagde is niet gevonden") Else Do While Not rst.EOF Debug.Print rst!SOORT, rst!WOONPLAATS, rst!BEDRIJF rst.Find strZoek,1,adSearchForward Loop End If rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub

154

Programmeren in VBA

J. Gils – E. Goossens

Verklaring: 

 

STRZOEK = "WOONPLAATS LIKE '" & STRZOEK & "*' " Het criterium lijkt op bijvoorbeeld: WOONPLAATS LIKE "Antw*" In feite moet het criterium nog eens tussen aanhalingstekens staan. In dat geval veranderen de dubbele aanhalingstekens in enkele aanhalingstekens: "WOONPLAATS LIKE 'Antw*' " Omdat de zoekstring een variabele is komt die op de plaats van Antw te staan, maar moet buiten de aanhalingstekens blijven want anders wordt er naar het woord strZoek gezocht: "WOONPLAATS LIKE '" & strZoek & "*' " RST.FIND STRZOEK,0,ADSEARCHFORWARD De methode Find zoekt de eerste record die aan de zoekvoorwaarde voldoet, te beginnen bij de huidige record en van voor naar achter. RST.FIND STRZOEK,1,ADSEARCHFORWARD Begint bij de volgende record te zoeken, van voor naar achter.

Opdracht 10. Vraag via een eerste dialoogvenster naar het soort bedrijf (BAK, BHW, …) en in een volgend dialoogvenster naar de eerste letters van de woonplaats. Zoek achtereenvolgens de bedrijven die aan de twee criteria voldoen. Ontwerp deze procedure, ADOFINDSOORTWOONPLAATS in de module BASADO3 van de database ADO. Haal de gegevens uit de tabel TBLSPONSOR van de database CENTENXP. Druk in het venster Direct het soort bedrijf, de bedrijfsnaam en de woonplaats af. De lijst moet oplopend volgens de woonplaats en aflopend volgens de bedrijfsnaam gesorteerd zijn. Denk eraan dat er maar in één kolom gezocht kan worden: zoek het soort bedrijf met de Find-methode en druk de gegevens alleen maar af als ook de woonplaats voldoet. (Uiteraard zou je met een goede filter dit probleem eenvoudiger kunnen oplossen.)

J. Gils – E. Goossens

Programmeren in VBA

155

Besluit Afhankelijk van het type Recordset-object gebeurt het rangschikken en zoeken op een andere wijze. Bij een recordset dat naar een tabel verwijst kan de ordening van de records gebeuren met de eigenschap Index van het Recordset-object. De indexwaarde moet een bestaande indexnaam van de onderliggende tabel zijn. Zoeken gebeurt met de methode Seek die alleen maar in de ingestelde index kan zoeken. De eigenschap EOF heeft de waarde False als een record gevonden is en de waarde True als geen record gevonden is. De gevonden record wordt automatisch de huidige record. Met de eigenschap Sort wordt een tijdelijke kopie van de recordset gemaakt en worden ook tijdelijke indexen gemaakt voor elk veld dat in de eigenschap Sort aangegeven is. De sorteervolgorde kan oplopend of aflopend zijn. De eigenschap Filter filtert selectief records in een recordset. Er kan op meerdere velden gefilterd worden. De methode Find kan een record zoeken die voldoet aan de opgegeven zoekopdracht. Om een volgende record te vinden moet een nieuwe zoekopdracht gegeven worden. In tegenstelling tot een filter kan de Find-methode maar in één veld tegelijk zoeken. Er kan zowel van voren naar achter als omgekeerd gezocht worden.

Wat je moet kennen en kunnen:   

het verschil tussen de methode Find en Seek begrijpen en die methodes op de juiste Recordset-objecten toepassen; het verschil tussen de eigenschap Index en Sort begrijpen en die eigenschappen op de juiste Recordset-objecten toepassen; records in Recordset-objecten filteren.

Opdrachten 11. Ontwerp de procedure ADOFILTERWOONPLAATSBEDRIJF in de module BASADO3 van de database ADO.MDB, die haar gegevens uit de tabel TBLSPONSOR van de database CENTENXP haalt. Vraag via een dialoogvenster naar de beginletters van een woonplaats. Maak een filter die een lijst van bedrijven, waarvan de bedrijfsnaam met de letter “B” begint, uit die woonplaats toont. Sorteer die lijst volgens woonplaats. Druk de bedrijfsnaam en de woonplaats in het venster Direct af. 12. Ontwerp de procedure ADOFINDCONTACT in de module BASADO3 van de database ADO.MDB. Haal de gegevens uit de tabel TBLSPONSOR van de database CENTENXP. Vraag via een dialoogvenster naar de eerste letters van de contactpersoon. Zoek achtereenvolgens de bedrijven die aan het gestelde criterium voldoen. Druk in het venster Direct de contactpersoon, de bedrijfsnaam en de woonplaats af. De lijst moet volgens de contactpersoon gesorteerd zijn.

156

Programmeren in VBA

J. Gils – E. Goossens

Hoofdstuk 15

Clone, RecordsetClone en Bookmark

15.1 Probleemstelling Soms bestaat de behoefte om vanuit een formulier over te stappen naar dezelfde record in het Recordset-object omdat je dan toegang hebt tot de methodes van de Recordsetobjecten, bijvoorbeeld een bepaalde record opzoeken. Nadat de methode uitgevoerd is wens je terug over te stappen naar het formulier waarbij de focus automatisch op de huidige record van het Recordset-object komt. De programmacode wordt in dit geval in een klassemodule bewaard. Dit is een module die aan het formulier gekoppeld is. De instructies in verband met recordsets zijn niet altijd dezelfde in een klassemodule als in een algemene module.

15.2 De eigenschap RecordsetClone en de methode Clone Als je in de keuzelijst met invoerveld CBOBEDRIJF op het formulier FRMSPONSOR uit de database CENTENXP de naam van een bedrijf kiest, dan worden de overeenkomstige gegevens van dat bedrijf in het formulier getoond. Deze keuzelijst is met een wizard ontworpen. De wizard genereerde de volgende programmacode: Private Sub cboZoek_AfterUpdate() ' De record zoeken die overeenkomt met het besturingselement Dim rst As Object Set rst = Me.Recordset.Clone ' methode van Recordset ' Set rst = Me.RecordsetClone ' formuliereigenschap rst.FindFirst "[SPONSOR_ID] = " & Str(Nz(Me![cboZoek], 0)) If Not rst.EOF Then Me.Bookmark = rst.Bookmark End Sub

De methode Clone en de formuliereigenschap RecordsetClone maken een kopie van de recordset die als recordbron (RowSource) voor een formulier dient, in dit geval TBLSPONSOR. Deze eigenschap is alleen in VBA beschikbaar en het nieuwe Recordset-object is alleenlezen. Je verwijst via het sleutelwoord ‘Me’ naar het formulier. Deze eigenschap of methode maakt het mogelijk om methoden en eigenschappen te gebruiken die je anders in een formulier niet kunt gebruiken:  tussen records navigeren: Move, MoveNext, …;  gegevens in records zoeken: Find;  het aantal records in een formulier tellen: RecordCount; De functie =Aantal([primair sleutelveld]) geeft ook het aantal records dat op dat moment beschikbaar is.  de absolute positie van de recordpointer aflezen: AbsolutePosition De waarde voor deze eigenschap is een integer getal tussen 0 voor de eerste record en RecordCount-1 voor de laatste record van de recordset. J. Gils – E. Goossens

Programmeren in VBA

157