This is intended as the first in a series of examples showing different ways of filtering and grouping data
This example focuses on the use of cascading combo boxes
. This method is particularly useful where you have a very large dataset that would be impossibly slow to search using a single combo box or listbox
For example, in my UK Postal Address Finder
app, I have a table of 2.6 million postcodes. I have mentioned this example before in various threads.
For example: https://www.access-programmers.co.uk/forums/showpost.php?p=1580504&postcount=37
For info, UK postcodes have 7 or 8 characters including a space
- Area e.g. HA
- District e.g. HA3
- Sector e.g. HA3 0
- Zone e.g. HA3 0S
- Postcode e.g. HA3 0SN
For anyone interested, additional info is provided in the attached PDF file
NOTE: All the above are official UK postcode terminology except zone (added here to assist with searching sectors)
The normal postcode entry is a textbox. A postcode builder
is available when you aren't sure what postcode to enter.
Searching a dataset of this size is obviously impossible using a single combo box. To make it manageable, the search is broken down using 5 cascading combos. The contents of each combo are dependant on the choices made in the previous combo
Each combo is based on a separate table
filtered on the previous selection which makes it very fast.
In addition, all fields used in the combo searches are indexed
. This increases the database size BUT makes a dramatic difference to the search time needed
The attached example is a cut down version
of the postcode builder to make the file small enough to upload here.
It only has data for two of the 125 postcode areas in the UK - Bristol (BS) & Kirkwall (KW). Inactive postcodes have been removed from the list
This reduces the total number of postcodes to around 28500 instead of the full data set of 2.6 million
It also contains an example postcode map
. Please keep this in the same folder as the database.
Click the OK button to start using the postcode builder
A dropdown box appears with a list of postcode areas
. Select an area from the list
Further dropdown lists appear in turn until you have selected a full postcode
In the full app, a Google static map
centred on the postcode geo-coordinates is automatically downloaded.
The attached just uses the same example map
for each postcode
Another good example of the use of cascading combo boxes is provided on the FMS website: http://www.fmsinc.com/microsoftacces...cascading.html
It would be helpful if others could provide links to good examples of cascading combos