Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-27-2018, 12:18 PM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,398
Thanks: 92
Thanked 1,826 Times in 1,697 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Grouping & Filtering #1 Cascading combo boxes example

This is intended as the first in a series of examples showing different ways of filtering and grouping data in Access

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
Attached Images
File Type: png PostcodeBuilder1.PNG (22.7 KB, 356 views)
File Type: jpg PostcodeBuilder2.jpg (75.9 KB, 348 views)
Attached Files
File Type: zip UK Postcodes Info PDF.zip (451.4 KB, 71 views)
File Type: zip PostcodeBuilderExample.zip (893.2 KB, 99 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-15-2018, 06:15 AM   #2
Dangerous
Newly Registered User
 
Join Date: Oct 2018
Posts: 54
Thanks: 13
Thanked 0 Times in 0 Posts
Dangerous is on a distinguished road
Re: Grouping & Filtering #1 Cascading combo boxes example

Quote:
Originally Posted by isladogs View Post
This is intended as the first in a series of examples showing different ways of filtering and grouping data in Access
Thanks for your reply, Your postcode builder works perfectly but having looked at the AfterUpdate event code I've no idea how to convert it to my need as I don't understand some of it.

I've also looked at the FMS example (have looked at it before but not actually downloaded it till today) and, for me, selecting 'Animal' in the 'Kingdom' dropdown then opening the 'Phylum@ dropdown it is blank (as it is for other entries in 'Kindom' if selected despite there being 3 'Phylum' names shown with the 'KingdonID' of 'Animal'. The remainder of the dropdowns don't limit the list to data in the previous dropdown.

For now 2 levels of cascade will do me & if I get it working I could, if need be, develop it further but at the moment I'm stuck.
Dangerous is offline   Reply With Quote
Old 11-16-2018, 12:22 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,398
Thanks: 92
Thanked 1,826 Times in 1,697 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Grouping & Filtering #1 Cascading combo boxes example

Hi
You posted to a moderated area rather than your own thread.
The principle is the same whether you have 2 cascading combos or more than that.
Happy to explain if you have a specific question about my example.
If so, please DON'T REPLY HERE -send me a PM instead

I have to admit I've only looked at the example on the FMS site and not actually tried it.
I'm surprised they would publish something that doesn't work properly as it's a very good site.
Can suggest you inform FMS about those issues. They may wish to fix them

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
cascading comboboxes , filtering , grouping

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can my cascading combo boxes sometimes begin with null value boxes? shocktea Modules & VBA 1 12-06-2013 08:48 PM
Please Help With Cascading Combo Boxes to Autofill Text Boxes hlenorem Forms 1 12-22-2009 07:22 AM
Cascading Combo Boxes tnvolfan3333 Forms 6 01-16-2004 01:29 PM
Additional Help with Filtering List Boxes with Combo Boxes Knight General 0 12-27-2002 06:57 AM
Cascading Combo Boxes SteveD Forms 3 08-30-2001 05:57 PM




All times are GMT -8. The time now is 06:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World