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.
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
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”
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 & “, “
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:
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.
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.
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.