Top 3 Products & Services

Dated: May. 31, 2013

Related Categories

You have probably encountered multiple lists of data, where you with the choosing of one piece of data you get another list filled, filtered by the chosen piece of information. For example, you would chose the state, then the city and finally the street – a simplified choice exists on GPS devices. Through a practical example we will show you how to quickly get to that form.

In the example we will create three tables and three lists of information: states, cities and streets. It is important that every group of information in some way belongs to a hierarchically higher group, as a city belongs to the state, and the street to the city. The group of information of the highest level – in this case the list of states doesn't have a field that records affiliation to one of the groups.

Create an empty form and place the list of information on it, that you will then adjust by your liking. Copy it and paste it on the form twice. Name them, from left to right: IstStates, IstCities, IstStreets, and in the label control write: States, Cities, Streets. First we will adjust the source of data for all three, and later we will go backwards – program the behavior of the lists. Choose a list of states, open the properties and on it choose Row Source. Click on the key... then set in the inquiry the table of states and then drag both fields to the network, with the sorting by the city name turned off.

As soon as you close the inquiry, you will see that the property Row Source gets the SQL statement. The first (index) field should not be seen by the user. Tell the program that this list uses two columns and that you can only see the second one: type in the number 2 in the property Column Count and „0cm;1cm“ in the property column width.

Then choose the list of cities and repeat the process: add in the inquiry the table of cities, set all three on the inquiry network, sort the information by the name of state and now in the Column Count you write in 3 and for the width you write in „0cm;0cm;1cm“. If it is clear why the zero is used (zero width, ie. you can't see it) the question is why 1 cm, when the real width of the list is far larger than that? Access automatically uses all the available list width. The property Row Source in this case remembers the SQL statement: SELECT tblStates.IDState, tblStates.State FROM tblStates ORDER BY tblStates.State.

Finally, repeat the whole process again for the third list. The SQL statement for the third list should look like this: SELECT tblStreets.IDStreet, tblSreets.IDCity, tblStreets.Street FROM tblStreets ORDER BY tblStreets.Street.

We have only the programing of the application left. When the form is loaded, the list of cities and streets should be empty. When changing the list of cities, the list of streets refreshes. When changing the list of states, the lists of cities and streets refresh. The logic is really simple. Both lists should be made empty when loading the form. Now choose the list IstStates, go to the card properties and add VBA program 4b in the event AfterUpdate. Watch out that in the SQL command the filter WHERE must be written in front of ORDER BY (first filter and then the way of sorting), but also look out for spaces. Choose the IstCities and use the event AfterUpdate in this field. Use the SQL statement copied in the Notepad, then copy it into VBA and adjust it.

Let's see how you will read the list information in a program way. In the event AfterUpdate transfer the program. Notice that from the first list we are reading the column 1. If you would leave out this parameter, the program would first read the first column and that is the ID field. Counting columns starts with zero and not one, because the program reads the second column first.

Now that you've gotten free know-how on this topic, try to grow your skills even faster with online video training. Then finally, put these skills to the test and make a name for yourself by offering these skills to others by becoming a freelancer. There are literally 2000+ new projects that are posted every single freakin' day, no lie!

Previous Article

Next Article