Explanation of the VBA code of the Selective window in a MS Access database
Day future MS Access database expert! I hope you found my previous blog interesting. Awesome that you also want to get started with this second (of four) blog. Let’s start right away.
Right list
Let’s start filling the right list with the name lstRechter of the form frmSelectVenster. Open the Visual-Basic screen with the Alt-F11 key combination. Note that it begins the code with:
The second option prevents you from setting variables in your code without having indicated it (declared) with the word Dim. An error message may occur if you have, for example, entered your code srtRechterLijst instead of strRechterLijst. Without the Option Explicit, the code is executed, and the variable strRechterList is not filled because you gave it a wrong name. You will then have to search for the error properly in your code. Pre-declaration thus prevents a lot of problems. In Visual Basic we know the distinction between Sub and Function routines. The Sub-routine passes the code and performs one or more operations. The Function routines return a value. I will come back later. I have given the code (see below in the smaller font) the name prcLijstRechts, where prc stands for Procedure. The text in brackets (strItems is a variable that is filled in when calling the procedure – see later).
The rules shown in green are the text of the previous apostrophe and are skipped when executing the code.
‘Show the rows in the right list.
You see, I’ve started declaring the variable strSQL as a string. This variable we fill in with content. First with “SELECT ref_id” and then this content is supplemented with “, ref_strVerkort”. It is a way of transparent programming that is widely used. You can also set the assignment as I do next, but it becomes less clear and you can make changes later.
You may not know that Access is based on the computer language SQL. SQL commands capture specific data from your database. In the form frmFietsen we have chosen the tblFietsen as a record source. Actually, the SQL command “SELECT * FROM tblFietsen” = retrieve all records of the tblBikes table with the restriction as indicated in “WHERE”. The code stated above is fully written:
“SELECT ref_id, ref_strVerkort FROM tblReferenties WHERE ref_strTabel = ‘Fiets’ en ref_id IN (3, 4, 5) ORDER BY ref_strVerkort”
You see that I have already filled in the bold strPbForm and strItems variables with ‘Fiets’ and ‘3,4,5’. Of course, I will return later on the variables, but now I can say that the strItems retrieve information from the table “tblFietsen” and the field “fts_strFiets” This sql command connects to the “Drive Source” property (lstRechter.Rowsource) of the “lstRechter” control.
Back to the properties of the Selection Lists
We have to go back to the design view of the form frmFietsen. Choose there for the properties of ‘lstRechter’ and set it as shown below. In the format you see that there are two columns, the first column of which is not visible (0cm). You also choose not to show the column headings.
If you copy and paste the above SQL command into the Running Source property and you choose Form View, you will see the selected Id’s 3.4.5 displayed as “Good Front Wheel”, “Good Rear Wheel” and “Hand Brake.” Just take a look at the tblReferences and the SQL command.
You can still choose to show the 1st column and column header. In the View, you’ll see ‘ref_id’ and ‘ref_strVerkort’ with IDs 1 and 3 in the 1st column.
Filling the variables ‘strItems’ and ‘strPbForm’
As mentioned, my variables were already filled with ‘Fiets’ and ‘1, 3’. This takes place when the form frmSelectieVenster opens. To do this we need to look at the code of the frmFietsen. After all, from this form, let’s open the Selection window.
Go to the frmFietsen in the design view, right-click on the btnFiets and choose Create event. The VBA screen appears again. I will put down the code below.
The variable strLinkCriteria will be filled with the ID of the bike currently present in the FrmFietsen Display window, for example ‘Batavus’ with the fts_ID = 1.
You see this displayed in the 3rd line: strLinkCriteria = “[fts_id] =” & Nz (Me.fts_ID, 0).
Do not be put off by the code. The hooks around fts_id mean that it’s a field of a table and prevent any spaces in the naming (which I have not done) to be included as well. This section is a text section because it is quoted between quotes. Then the “value” section retrieved from the form frmFietsen is followed by the word “Me” and the value from the field called “.fts_ID.value”.
All this is between the function NZ (). The internal vba function NZ, represents NULLZERO returns a value if the field itself contains the NULL (completely unfulfilled). In this case, I choose to return a 0.
In the next line you see, I want a blank string (“”) at a zero value.
The ampersand (&) connects the text section with the value portion so that the variable strLinkCriteria will now be filled with “fts_id = 1”.
The following line is now easier to understand. In the variable strArgs, the name of the current field (‘btnFiets’) and the contents of the field will be ‘fts_strFiets’, i.c. ‘3.4.5’).
The last line of buttons speaks for itself – pay attention to the commas. Open the form frmSelectVerster with the fts_id = 1 (strLinkcriteria) record and the arguments (‘btnFiets, 3,4,5). Arguments? Yes, if the frmSelectVenster is open, it has a feature that can be retrieved with vba (.openargs). We will use these arguments later in the opening codes of the form ‘frmSelectVenster’
Other buttons
For the sake of completeness, I also put down the code of the other buttons.
You see that the code is hardly different. It would be too compressed into one routine called by all three buttons, but in this case it is a bit unnecessary. If you have pasted the routines in the Visual Basic window, you must still select the Program Capture feature under the Click Criteria Properties. This will connect the execution of the code to the Button when clicked.
Opening of the frmSelectionwindow
Finally, we will check what happens when the frmSelect window is opened. To this end, it is necessary to interrupt the rule ‘strLinkCriteria’. Click the btnFiets code and then on this line, and then press the F9 key of your keyboard. The line will now appear red-brown, indicating that the code execution will stop.
Now go to the frmFietsen, choose the View and click on the btnFiets.
The execution of the procedure will stop at the indicated line and appear in yellow. Now you can see what happens with the F8 function key. Once you have landed in the DoCmd line after two times F8, go with the mouse over both of the completed variables. You will then see that they are filled in. You can also see the variables in the menu bar if you choose Image, Local Variables Window.
You can see that the variable ‘strArgs’ is now filled with the value ‘btnFiets, 8,4,3,2’. In other words, the name of the button and apparently the contents of the field fts_strFiets are filled with 8,4,3,2. If your field is not filled yet, of course, it’s all but ‘btnFiets’Continued with F8 and then the window opens frmSelectVenster with the code ‘Private Sub Form_Open (Cancel As Integer)’.
Continue with F8 or click the triangle under the ‘n’ of ‘Insert’ in the toolbar. The square, slightly further, stands for ‘Stopping’ of the execution.
Continuation
This second blog is done! In the third blog we continue with the explanation of the code. Again, it will not get easier.
Do you have questions about our blog? Leave a comment below.
Contact us
Do you have a specific question or wish, are you in a bottleneck or just want to brainstorm?
Contact us today by calling 020 6 254 254 or send email to info@florijn.com.