Create selection boxes in MS Access database part 3: continuation VBA code

November 14, 2017
Archief

Opening of the form frmSelectieVenster

This blog is a continuation of second blog about selectionwindows. There we discussed the code that is executed when we click on one of the btnFiets, btnStuur or btnBanden buttons and the Selection window opens. Now let’s see what happens next.

Let’s get straight to the point. You will find the code below which opens the Form frmSelect window.

_{Option Compare Database}

_{Option Explicit}

_{Dim strPbForm As String}

_{‘Deze publiek variabele kent drie strings om informatie op te halen en weg te schrijven}

_{‘Fietsen’, ‘Stuur’ en ‘Banden’, die alle drie als Public in strPBform worden doorgegeven}

_{Private Sub Form_Open(Cancel As Integer)}

_{‘Amsterdam, 17 juni 2017}

_{‘start de linkerlijst}

_{Dim strItems As String}

_{Dim strOpenArgs As String}

_{Dim intLengte As Integer}

_{‘Me.imgLogo.Picture = CurrentProject.Path & “\images\imgLogo.jpg”}

_{intLengte = InStr(1, Nz(Me.OpenArgs, “”), “,”)}

_{strPbForm = Nz(Left(Me.OpenArgs, intLengte – 1), 0)}

_{strPbForm = Mid(strPbForm, 4)}

_{strItems = Nz(Mid(Me.OpenArgs, intLengte + 1), 0)}

_{Me.lblOpschrift.Caption = strPbForm}

_{prcLijstRechts strItems}

_{prcLijstLinks}

_{End Sub}

In the previous blog the following command was stated:

‘DoCmd.OpenForm “frmSelectieVenster”, , , strLinkCriteria, , , strArgs’ which opened the window frmSelect Window, and in strArgs passed the arguments (‘btnFiets, 3,4,5). The variable (‘btnFiets, 3,4,5) can be retrieved with the Me.OpenArgs function and places in the public variable strPbForm. With the Instr () function we search for the comma position. We start at the 1st position of the variable ‘btnFiets, 3,4,5’ and find the place of comma with the formula

instr(1, Me.OpenArgs, ”,”)

with which the variable INTLengte enters the value 9. The comma is in the 9th position. Because Me.OpenArgs can also be Null, I’ll fix a possible error with the NZ () function.

intLengte = instr(1, Nz(Me.OpenArgs,””) ”,”).

Now we can fill the strPbForm variable with the first 8 positions of the Me.OpenArgs.

strPbForm = Nz(Left(Me.OpenArgs, intLengte – 1), 0).

From the outcome strPbForm = ‘btnFiets’ we do not want to bring the first three positions. We do that with the Mid () function.

strPbForm = Mid(strPbForm, 4)

The variable strPbForm is now filled with the word ‘Fiets’. This value is required when retrieving the values from the tblReferences. We also get the items with the Mid () function in the same logical way from position 10 (INTlengte 9 + 1)

strItems = Nz(Mid(Me.OpenArgs, intLengte + 1), 0)

The variable strItems are now filled with ‘3,4,5’. We still know that 1 and 3 stand for ‘Good front wheel’, ‘Good rear wheel’ and ‘Hand brake’. The heading of the form frmSelectVenster gets the value of the strPbForm variable (‘Fiets’) with the command: Me.lblOpschrift.Caption = strPbForm

The following command execute the prcLijstRechts procedure. We have already explained this in our previous blog. The procedure receives the 3.4.5 argument with the variable strItems. Finally, the procedure prcLijstLinks is executed. Pay attention.

The procedure prcLijstLinks

^{Private Sub prcLijstLinks()}

^{‘Amsterdam, 17 juni 2017}

^{‘vult lstLinker}

^{Dim strSQL As String}

^{Dim strItemsRechts As String}

^{‘Vraag de rijen op die in de rechterlijst voorkomen:}

^{strItemsRechts = fncItemsRechts}

^{strSQL = “SELECT ref_id”}

^{strSQL = strSQL & “, ref_strVerkort”}

^{strSQL = strSQL & ” FROM tblReferenties”}

^{strSQL = strSQL & ” WHERE ref_strTabel = ‘” & strPbForm & “‘”}

^{If Not strItemsRechts = “” Then}

^{strSQL = strSQL & ” AND ref_id NOT IN (” & strItemsRechts & “)” ‘zondert de rechterrijen uit.}

^{strSQL = strSQL & ” ORDER BY ref_strVerkort”}

^{End If}

^{With Me.lstLinker}

^{.RowSource = strSQL}

^.Requery

^{End With}

^{End Sub}

The procedure starts with a function. In the previous blog, we learned that a function returns a value. The variable strItemsRechts is filled with the value retrieved with the fncItemsRechts function.

Fetching the values in the right list with the function “fncItemsRechts”

^{Function fncItemsRechts () As String}

^{‘Amsterdam, 16 juni 2017}

^{‘Routine die de lijst met items samenstelt}

^{Dim strItem As String}

^{Dim strItems As String}

^{Dim lngItem As Long}

^{‘Controleer of er een item in de rechterlijst voorkomt.}

^{If Me.lstRechter.ListCount = 0 Then ‘De lijst is leeg}

^{strItem = “”}

^Else

^{For lngItem = 0 To Me.lstRechter.ListCount – 1}

^{strItem = Nz(Me.lstRechter.Column(0, lngItem), 0)}

^{strItems = strItems & strItem}

^{‘ Dan nog komma tussen de namen (als het niet het laatste item is}

^{If Not (lngItem = (Me.lstRechter.ListCount – 1)) Then}

^{strItems = strItems & “, “}

^{End If}

^Next

^{End If}

^{fncItemsRechts = strItems}

^{End Function}

Fetching the values goes with a For, Next – loop and as often as values appear in the list. With Me.lstRechter.ListCount, the number of values is retrieved from the right list. These were filled with the previous assignment with the values 3.4.5. In other words, there are three items.

For lngItem = 0 To Me.lstRechter.ListCount – 1

The first item has the value 0, which causes the For, Next loop to go three times and lngItem will get 0, 1 and 2 in succession. With this value of lngItem, the contents of this item are retrieved with:

strItem = Nz(Me.lstRechter.Column(0, lngItem), 0)

In succession the values 3, 4 and 5 are found in the right-hand list. The value ‘strItem’ found is added to the variable ‘strItems’ with a comma, unless this is the last item in the list.

strItems = strItems & strItem

‘Then another comma between the names if it is not the last item

If Not (lngItem = (Me.lstRechter.ListCount – 1)) Then

strItems = strItems & “, “

End If

The function fncItemsRechts thus provides the string ‘3, 4, 5’ in our case, which will fill the variable strItemsRechts.

With the strSQL variable we will retrieve all values ‘ref_id’ and ‘ref_strVerkort’ from the tblReferences with the value ‘Fiets’ (strPbForm). However, if something is filled in the variable ‘strItemsRechts’ (If Not strItemsRechts = “”), they are excluded by the command’ AND ref_id NOT IN (“& strItemsRechts &”) “.

So here’s actually: Get all the ref_id’s from the tblReferences where ‘Fiets’ is filled in in the ‘ref_strTabel’ field (which will be ‘1, 2, 3, 4, 5, 6, 7, 8’), but without this from the values ‘3.4 and 5’, because they are already in the right list.

This will have the window frmSelectVenster run through the entire process when it opens and the left and right lists will be filled.

Transfer left to right

We will do this in two ways, namely with the button btnNaarRechts or double-click on the left list. The code below allows the prcNaarRechts to run:

^{Private Sub btnNaarRechts_Click()}

^{prcNaarRechts}

^{End Sub}

^{Private Sub lstLinker_DblClick(Cancel As Integer)}

^{prcNaarRechts}

^{End Sub}

^{Hoe werkt de prcNaarRechts()}

^{Private Sub prcNaarRechts()}

^{‘Amsterdam, 17 juni 2017}

^{‘Voegt het linkerItem toe aan de rechterlijst}

^{Dim lngItem As Long}

^{Dim strRechterItems As String}

^{If Me.lstLinker.ListIndex = -1 Then ‘Niets doen als er geen naam geselecteerd is (.ListIndex = -1)}

^{If Me.lstLinker.ListCount > 0 Then ‘De lijst kan ook leeg zijn}

^{MsgBox “Selecteer eerst een naam.”, vbInformation}

^{End If}

^Else

^{‘Welke items staan al in de rechterlijst?}

^{strRechterItems = fncItemsRechts}

^{‘Controleer welk item in de linkerlijst geselecteerd is en voeg het dan toe aan die in de rechterlijst}

^{For lngItem = 0 To Me.lstLinker.ListCount – 1}

^{If Me.lstLinker.Selected(lngItem) Then}

^{If strRechterItems = “” Then ‘zonder komma}

^{strRechterItems = Me.lstLinker.Column(0, lngItem)}

^{Else ‘met komma}

^{strRechterItems = strRechterItems & “, ” & Me.lstLinker.Column(0, lngItem)}

^{End If}

^{Exit For}

^{End If}

^Next

^{prcLijstRechts strRechterItems}

^{prcLijstLinks}

^{End If}

^{End Sub}

The first part of this procedure requires some explanation. It starts with clicking on the btnNaarRecht and no selection has been made. In that case, the .list index is equal to -1. After that, all items can already be transferred to the right list and no items left in the left list. We hereby overcome the cessation of the procedure. If no selection has been made and there are still items in the left list, a message appears in the message box. With the earlier explanation of the function fncItemsRechts, the follow-up of the procedure will be read soon. In fact, the right items are retrieved and the selected item from the left list with the Me.lstLinker.Selected command (lngItem) is added.

Transfer from left to right

Similarly, we do this in two ways. With the btnNaarLinks or double click on the right list.

^{Private Sub lstRechter_DblClick(Cancel As Integer)}

^prcNaarLinks

^{End Sub}

^{Private Sub btnNaarLinks (Cancel As Integer)}

^prcNaarLinks

^{End Sub}

^{Hoe werkt de procedure prcNaarLinks()}

^{Private Sub prcNaarLinks()}

^{‘Amsterdam, 17 juni 2017}

^{‘Verwijdert het gekozen item uit de rechterlijst en bouwt de linker opnieuw op}

^{Dim lngItem As Long}

^{Dim strRechterItems As String}

^{Dim intItem As Integer}

^{Dim strItem As String}

^{Dim strPlaats As String}

^{If Me.lstRechter.ListIndex = -1 Then ‘Niets doen als er geen naam geselecteerd is (.ListIndex = -1)}

^{If Me.lstRechter.ListCount > 0 Then}

^{MsgBox “Selecteer eerst een naam.”, vbInformation}

^{End If}

^Else

^{‘Controleer of het item in de rechterlijst geselecteerd is, en verwijder het dan}

^{strRechterItems = fncItemsRechts}

^{For lngItem = 0 To Me.lstRechter.ListCount – 1}

^{If Me.lstRechter.Selected(lngItem) Then}

^{strItem = Me.lstRechter.Column(0, lngItem)}

^{intItem = Len(strItem)}

^{strPlaats = InStr(1, strRechterItems, strItem)}

^{strRechterItems = Left(strRechterItems, strPlaats – 1) & Mid(strRechterItems, strPlaats + intItem + 2)}

^{Exit For}

^{End If}

^Next

^{‘Opnieuw opbouwen}

^{prcLijstRechts strRechterItems}

^{prcLijstLinks}

^{End If}

^{End Sub}

It will not surprise you that this procedure is almost the same as the prcNaarRechts procedure.

The difference is the assignments.

strPlaats = InStr (1, strRechterItems, strItem)

strRechterItems = Left (strRechterItems, strPlaats – 1) & Mid (strRechterItems, strPlaats + intItem + 2)

First, the location of the selected item is searched in the variable strRechteritems and then with the Left () and Mid () functions from the string. After the item, two positions are added: the comma and the space.

If the srtRechterItems have been changed, both list boxes are rebuilt.

Continuation

This third blog is also on display again. There is one last blog in which we will transfer the results to the form frmFietsen simply by clicking on the btnOke.

Do you have questions about our blog? Leave a comment below.

Contact us

Do you have a specific question or request, are you in a bottleneck or just want to brainstorm?

Create selection boxes in MS Access database part 3: continuation VBA code

Opening of the form frmSelectieVenster

Fetching the values in the right list with the function “fncItemsRechts”

Transfer left to right

Transfer from left to right

Continuation

Contact us

Martijn Wijnen

Leave a Reply Cancel reply

Vacature Senior Delivery Manager / Account Manager Tennet TSO

Falende ICT-projecten belemmeren energietransitie

Gezocht: MS Access Programmeur

AWS als hostingplatform

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here

Add Your Heading Text Here