Creating selection windows part 4 in MS database: The Final commands

The last Selection Window commands

Hello everyone. This is the last blog about selection windows. I hope you have become a lot wiser after my blog series. And if not, you can always leave a comment at the bottom of the page, to which I respond as quickly as possible.

In the previous blog, I showed you the codes in the Selection window that moved your choice from left to right or vice versa.

Selectievensters in Access

Now the choices are made, we will have to fill in the frmFietsen main window.

Lets begin!

We start filling by clicking the btnOke button.

The code below is then executed.

 

       Private Sub btnOke_Click()
              ‘Amsterdam, 17 juni 2017
             Dim strForm            As String
             Dim strItems           As String
             Dim strVeld              As String
             strForm = “frmFietsen”
             strItems = fncItemsRechts
             strVeld = “fts_str” & strPbForm
             With Forms(strForm)
                     .Controls(strVeld).Value = strItems
                     .Controls(strVeld).Requery
                      .prcToonlist
                      .refresh
              End with
             DoCmd.Close acForm, Me.Name
       End Sub

Because we use the form “frmFietsen” a number of times in this procedure, I put the value “frmFietsen” in the variable strForm. Then the right items are retrieved with the familiar function frmItemsRight.

We fill the variable strVeld with the first seven letters of the table tblFietsen and the string from the public variable strPbForm, which we have assigned the value ‘Fiets’ when you open the frmSelectionVern form.

I will just show the declaration of the strPbForm.

Selectievensters in Access

This public variable is still filled. If all the procedures in the module Form_frmSelectionVenster have been run through, it only loses the content.

Now we fill the variable with the content of strPbForm and let that precede the value of the string ‘fts_str’, which gives the field ‘fts_strFiets’.

Between With and End With we fill the assignments related to the form, which these assignments with Forms (strForm). Moreover, we burden our program slightly less.

In the frmFietsen, which we will discuss below, there is a control (also called control) with the name fts_strFiets. This control has the property Recordsource and retrieves the value from the field of tblfiets. We placed this value in the field with the function fncItemsRight – see our previous blog. We will then have to refresh the contents of the field (.requery).

Finally, we have a prcToonList procedure in frmFietsen and we have the frmFietsen window refreshed with the .refresh command. We will discuss the procedure prcToonList below.

With the last command DoCmd.Close acForm, Me.Name we close the form frmSelectievester and the main form frmFietsen appears again.

The btnAnnuleren button

We also close the form with the btnannuleren. It is easy to close the form without anything happening.

Private Sub bntAnnuleren_Click()
   DoCmd.Close acForm, Me.Name
End Sub

Back to the code behind the main window frmfietsen

Selectievensters in Access

By opening the form, two procedures are immediately followed.

Private Sub Form_Current()
Private Sub Form_Load()

 

Selectievensters in Access

For us, the procedure Form_current is important, which we get when we choose under the properties of the form in the design view, for the event ‘At designation’.

Private Sub Form_Current()
   prcToonList
End Sub

 

Every time we choose the frmFietsen form, the Form_current procedure is executed, which then invokes our prcToonList procedure. This shows the choices made in the three listControls lstFiets, lstStuur and lstBanden by going through the procedure prcToonListst three times with the arguments ‘Fiets’, ‘Stuur’ and ‘Banden’

       Sub prcToonList( _
             )
              ‘Amsterdam, 17 juni 2017
             ‘Zet het tonen van de verschillende lstControls in werking
             prcToonLists “Fiets”
             prcToonLists “Stuur”
             prcToonLists “Banden”
       End Sub

As you may have noticed, I have changed the choices in the form and also made the fields fts_strFiets, fts_strStuur and fts_strBanden visible. You now know that the values match the ref_id in the table tblReferences. These values will have to end up as an SQL string in the RowSource property of the lstConstrols. We will follow the prcToonLists for a moment to see how that happens.

       Sub prcToonLists( _
             strListControl As String _
              )
              ‘Amsterdam, 17 juni 2017
             ‘Toont de gemaakte keuzes voor Fiets, Stuur en Banden
             Dim strItems     As String
             Dim strSQL            As String
             Dim rst            As Recordset
             Dim strVeld       As String
             On Error GoTo prc_err
             With Me
                            strVeld = “fts_str” & strListControl
                            If nz(Me(strVeld),””) = “” Then
                            strSQL = “”
                            Else
                            strItems = Nz(Me(strVeld).Value, “”)
                            strSQL = “SELECT ref_id
                             strSQL = strSQL & “, ref_strVerkort
                            strSQL = strSQL & ” FROM tblReferenties
                          strSQL = strSQL & ” WHERE ref_strTabel = ‘” & strListControl & “‘”
                             strSQL = strSQL & ” AND ref_ID In (” & strItems & “)”
                             strSQL = strSQL & ” ORDER BY ref_strVerkort;”
                            End If
                            Debug.print strSQL
                     Select Case strListControl
                                   Case Is = “Fiets”
                                   !lstFiets.RowSource = strSQL
                                   Case Is = “Stuur”
                                   !lstStuur.RowSource = strSQL
                                   Case Is = “Banden”
                                   !lstBanden.RowSource = strSQL
                            End Select
                            .Refresh
             End With
       prc_exit:
              Exit Sub
       prc_err:
             If Err.Number = 1 Then ‘Vul hier het nummer in voor de te ondervangen fout
                     Resume Next ‘bijvoorbeeld
      Else
                     MsgBox Err.Number & “, ” & Err.Description
                     Resume prc_exit
                     Resume
      End If

 

The procedure is called the first time with the argument ‘Bike’ in the variable strListControl. Under the ‘With me’ you see that the field gets the value ‘fts_strFiets’, which is equal to the field in the table ‘tblFietsen’.

Now we check whether the field fts_strFiets contains a value. That can of course NULL and then it becomes with the nz function “” or already nothing “”. If that is the case, the string strSQL also becomes “”.

In the other case, an SQL string is built up. You can easily see what that string looks like in the ‘Direct’ window. If you have not used this ‘programming window’ before, you can bring it out as follows. In the command bar of the vba window, choose View, and Window Direct or the key combination Ctrl + G.

By now entering the code after End if the assignment Debug.print strSQL, you see in the window Direct (what the string strSQL looks like).

Selectievensters in Access

Of course, the Debug.print code is later redundant and you can delete it or put it out of action with a previous apostrophe. As a programmer you will use a lot of this assignment.

Finally, we have to assign the sqlstring to the three listControls. We do this with the SELECT function. Actually, speak for themselves. Depending on the value of the strListControl argument (for example, “Bicycle”), the Recordsource property of the lstControl (now called “lstFiets”) is assigned the contents of the strSQL (ie “SELECT ref_id, ref_strRefort FROM tblReferences WHERE ref_strTabel =” Bike ‘AND ref_ID In (8, 4, 5) ORDER BY ref_strShort;’).

Finally, I added a bug fix with the names prc_err and prc_exit. If you check this you will see the perhaps superfluous command Resume after Resume prc_exit. I use this if the error in the msgbox is shown and the code is in the interrupt phase. You can then move the yellow line to Resume to check step-by-step what is going on.

Closure

This was the fourth blog and that’s all. If you have run every exercise neatly, you have become a lot wiser. I hope that I have given you insight into the way of VBA programming in Access and you may be able to use the Selection windows in your own applications. In that case I would love it if you would send me a message below briefly!

You can now receive the previously promised sample database. Mail to marketing@florijn.com to receive the download link.

If you still have questions about making selection windows; leave a comment below.

Contact us

Do you have a specific question or wish, or do you simply 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