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.
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.
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.
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.
Back to the code behind the main window frmfietsen
By opening the form, two procedures are immediately followed.
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’.
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’
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.
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).
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.