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

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.

Selectievenster

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?
Contact us today on 020 6 254 254 or info@florijn.com

 

Write a comment

Your email address will not be published. Required fields are marked *

Recente blogberichten


WhatsApp Ons whatsapp