Creating selection windows in an MS Access database yourself

Welcome to the start of a 4-page blog about selection windows in MS Access! After following this steam course, you can also get out of the box when making selection windows. Do you work through this blog with ease? Then see our second blog.

Okay, let’s start ..

If you create an MS Access database, you will sometimes use the Selection List control to display multiple choices. This is a little optically elegant solution from Microsoft programmers. Therefore, a much better solution has been developed for this.

On the left you will see the MS Access drop-down list; to the right our solution. You will agree that this looks considerably quieter and more straightforward, right?

Curious and would you like to be able to do this as well?

You know how to handle Visual Basic for Applications (Vba: Alt-F11) and you want to expand your knowledge or add more structure to your code. Or you may have just begun here and need a more complicated case. Then I can help you with this blog and the (3) successive blogs! In this blog we will look at the build-up of the “Selection windows” tool and in the coming blogs to its programming.

Also for MS Access beginners who want to learn more, this blog is recommended. The true lover can also use the tool with some adaptation in Excel.

Get acquainted with these blogs. This applies to your knowledge of MS Access and Vba as well as in your own databases. In addition, you can download an example database in which the tool is placed from our site, so you can easily get started.

The case – Selection windows instead of Selection lists

For one of our customers, we designed a database that contained a number of specific features of each device. These properties were divided into three types of features. In this case, I have taken an example of the purchase of 2nd hand bikes. Among the characteristics of the purchased bike, I took the bike itself, the steering wheel and the tires. You will see two records of the database Used bikes with the main screen ‘Fiets’.

How do you end up in the section “Fiets”?

The “Input Box Selection List” control is of course unsuitable for use because it shows only one option, but if you use the Wizard, you will find this selection box among other things. You know this way, of course, also from various internet sites you are asked to make a choice.

We also wanted to use this idea for our customer! The result of the selection had to be shown in a window on the main window. For all three of the attributes we wanted to use the same Access form.

The solution

I would like to show how we succeeded. It is necessary that you have some knowledge of Visual Basic in MS Access, but you can also copy and paste it and provide the tables with your own input if you want to use this in your own database.

In this example, as I said, I chose to check for a second-hand bicycle, the steering wheel and its tires (this content is, of course, only for illustration and not practical). I suggested that the characteristics of each bicycle be recorded in the database. As a feature, I chose the bike itself (‘Fiets‘), the steering wheel (‘Stuur‘) and the tires (‘Banden’). See the two screens at the top.

In addition, you’ll see a clip from the main screen of the “Bicycle” attribute that shows the bike’s features. In the left-hand corner you will see the options and in the right-hand screen the selected items.

Designing a selection list

You start an empty form by clicking on Form Design from the Create menu. You call this form frmSelectionwindow. In this form, place a Selection list and click Cancel immediately if you used the Wizard. Also, remove the selection list label if it has been included. Make sure your selection list is getting the right size, copy and paste it and place both elements on your form as in my example.

Now you are going to place both buttons between the drop-down lists, which can transfer the device properties from the left-hand list to the right-hand list. A simple action that you probably have done more often. In the Format Properties (Ctrl-Enter) of the button, type Caption ‘->’ and the other button ‘<-‘.

It is important to give a handy name (Other-Name) to this element so that you later know in Visual Basic exactly the distinction between the list elements. I called the left-hand list ‘lstLinker’ and the right ‘lstRechter’. You may wonder why I let the names of the letters ‘lst’. It is my custom to apply the Leszynski Naming Convention. The two buttons call ‘btnNaarRechts’ and ‘btnNaarLinks’ and begin with the initials ‘btn’, as indicated in this convention. Later in this explanation you will see that I use the same name in the tables.

Add the Cancel and Ok button now. Give them the names btnAnnulate and btnOk (Other-Name). Then type the captions under Format Caption as & Cancel & & amp; Ok. It has been observed that one of the letters in a database button is underlined. This is done by placing an ampersand (&) prior to the letter. The button is then available with the keyboard with the Alt-a combination or the bntOk button with the combination Alt-o.

The Collums

Because we have three features of the device, you could have three tables in the database, but you can also have one ‘reference table’. This table lists tblReferences (again according to the Leszynski code), starting with the first three letters as the abbreviation of ‘table’. The fields in the table let me start with the three letters ‘ref’ and an underscore. You will have a lot of fun in the future if you also follow this convention. You can then immediately identify in your queries or vba code which table the field is from.

Also, the fields name me with three letters of the Leszinsky code. If the field contains a string (short text) after the first three letters of the table, then the underscore shows ‘str’ and for a numeric field ‘int’ of Long’s Integer or ‘Long’. Beginning with ‘mem’ are fields that do not have content restriction, also called ‘memo fields’.

My reference table looks like the one in this image.

I have not been quite logical because the field ref_datestamp should actually be called Ref_dteDatestamp. A little too much of the good. Tip: This field still needs a supplement. In the properties of this field, set the function ‘= now ()’ under the ‘Default Value’ feature. Each time a new record is created, the day and time will be listed in your table.

Filling of the tblReferences

Now we are going to fill in the reference table. A simple, simple and necessary work.

As you can see, I did not use all the fields in the table because they do not apply in this example.

If you take over my tblReferences, you can lose information in the “ref_strVoluit” field and appear at other times in your database to give the user more clarity.

The table tblFietsen and the form frmFietsen

The choices should of course also come into your database. For this reason, it is necessary that you create a form in your database, listing the type of bike and perhaps the date on which it was obtained. This form will also cover the characteristics of the bike.

We start with the table (see next). The table seems to me to be discussed now. In the field fts_strMerkFiets and the fts_dteConceptions, you can simply fill in manually. Maybe you could add more fields about the bike, but of course, it’s about filling the other three fields with your selection windows.

The FrmFietsen in the Design View

I kept the main form simple. Under the properties of this form, you can choose the tblFietsen as a record source. Then you put the brand bicycle, the date received and the three attributes as text boxes. In addition to the three features, you place a button. This button will call you later the selection window. Among all three features, you add a label and a drop-down list (without input box).

Now it’s about choosing the right names. It is your intention that your VBA code will recognize the elements later.

Maybe you did not know the next trick. In the control panel property window, click the word Control Source, after which the record source becomes black. Copy this word, go to Other, and paste it as Name. Do this with the other text boxes. The drop-down lists do not get a Control Source, leaving you with the words “Non-dependent.” We will fill the source later with the vba code. The names of the buttons and the selection lists are obvious: btnFiets with lstFiets, btnStuur with lstStuur and btnBands with lstBanden.

Finally, the three attributes fields fts_strFiets, fts_strStuur and fts_strBanden will not be visible. You can also do this later if you first want to see what is being filled in in those fields.

Continuation

With this design we have finished the first and simple part of the Selection Boxes! The sequel to this blog is the Visual Basic part, where significantly more of your thinking is being asked.

Do you have questions about this? Leave a comment below. Would you like to hire a MS Access Expert or are you looking for a MS Access database specialist? Then contact us. We will be happy to answer your questions!