How do you genericly or easily build of herarchical trees within MSAccess2000 or later?

Dear All,

Having created some subtables using the "analyze table wizard" so that you can click on the plus next to Regiontbl(Country) and see all of the states for example in table view of Region, or conversly from the state table click a combo box in a column headed up "Lookup to RegionTbl" I get this query

SELECT RegionTbl.ID AS xyz_ID_xyz, RegionTbl.RegionFld AS xyz_DispExpr_xyz, RegionTbl.RegionFld
FROM RegionTbl
ORDER BY RegionTbl.RegionFld;

In Table design View Lookup tab query thingy of the state table.

have spent a fair bit of time trying to find out what this means but still can't find out where DispExpr is defined or what code it is using or even how you do it by hand having used the wizard to get this set up.

Basically if you have 2 records in a table called RegionTbl

1 Australia
2 England

and you let Access 2000 set up say a state table with Country_ID field containing a 1 and a 2, this query ensures that you see the text of Australia or England and not the 1 or the 2... Using the MS Access supplied Surrogate key record number.

I am not an SQL guru. I even have an IBM and a MS SQL7 Books with SQL reference material in them but they do not cover the xyz_ID_xyz and the xyz_DispExpr_xyz stuff. These must be MS Access builtin functions or whatever. Can't find anything using Google or the MS Site either.

And they do not appear in the query builder stuff as far as I can see and VBA seems to show nothing, not that I know much about VBA either. I am sure this is simple and a real common thing, perhaps that explains its lack of coverage??

Finally, If you DO knpow how to code this up WITHOUT the wizard apart from just copying it parrot fashion which probably will not work, can you do TWO of these lookups in a query, second one to a different table, and if so, what do you use for the second and one eg abc_ID_abc and abc_DispExpr_abc and the third one def_ID_def and def_DispExpr_def and so on???

What I am really leading up to is:

I want to, with out writing VBA, if possible, be able to

have a third table, the Citytbl table that allows me to select a constrained list of countries

from the complete list of countries, say Australia in a country drop down combo box or similar,

Then select a constrained list of States, in the next field, of states the ONLY EXIST in Australia,

say Victoria, easy huh,

Then I want to select City from those cities that only exist in Australia, and Victoria.

An so on, then say Street from just the streets that exist within Australia, Victoria, Melbourne, using surrogate lookup for the constrained fields.

Simple requirement huh. None of the tutorials deal with this very common requirement. I am amazed. One apparently has to write this in your own code as far as I can see. What is going on here.

So that entering Data in the table view of the "street table" should have a drop down combo box and I select Australia.
Next box, State, I see 7 Australian states, I select Victoria, next field, City, I see only australian Victorian Cities and no others, and I select Melbourne, next box street, a straignt text field here as this is the Street table so I can type in a valid text for a street in Melbourne, Victorai, Australia and file the record.

Then I want to achieve the same thing on a Country form, a State Form, a City form and then a Street form, (search, add or update) preferably by filling the fields of say the street form with

Country: AU
State: VI
City: ME
Street: Fre*

hit enter or click

and having the thing give me either auto fill of the fields or a selectin of Australia or Austria and then go on to complete the Victoria and the Melbourne or give me a selection list of say Melbourne and Melton and then give me the Fred St record or a list of all streets starting with "Fre" or just a blank street field.

I am very happy to pay 250 points for an answer to this. very easy if you know how.. The form bit is possibly a bit hard because of the not fully filled fields requirements but will give the points even if the form just uses combo boxes accept for the street field which is text.

If you think you have a good answer to the ability to partially prefill the fields on the form and then hit enter and let it trot through validating and only throwing up a selection list where required, let me know, I may be happy to pay more points.

priorities are, a generic facility if possible using MS Access Builtin facilities, no VBA or very minimum of VBA, SQL as required. I am amazed it does not just do this easily out of the box. it may be able to be coaxed into doing it but it is not that easy.

Many thanks in advance, Sorry if this is a common question. I may be a bit in experienced at searching this site but my brief look did not pick anything up. Is there a programmers exchange with generic modules to do this sort of thing in VBA for MS Access?

BTW, No I do not wish to change the Design so the each table only validates the "one" above it, I want each of the lower tables say the street table to validate with increasing precision as you work down the tree of country, state, city, street,

The City table would validate on Country, then States within that country and both country and state appear as seperate fields in this City table. The Street table has a validated or constrained fields for Country, then State within the country chosen, then City within the country and state chosen then a text field for Street.
PS if you ave not done this before, you might find it extremely useful. Not concerned about web pages yet but have noticed a lot with this sort of funtionality at least in combo boxes, select the top one, one, hit enter, then do the next one hit enter or click  etc so although a little clumsy for the operator, but it is common.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The xyz_ID_xyz and xyz_DispExpr_xyz are field aliases.

In an SQL query, you can give a table or a field an alias so they are either easier to refer to or are less confusing. For example, if

This line:
SELECT RegionTbl.ID AS xyz_ID_xyz

means "Select regionTbl.ID, but call it xyz_ID_xyz instead". I don't know why Access has chosen those aliases but it is probably to avoid potential conflicts with existing names - for example, if you had a field already called ID in your table.

Aliases aren't required, but can make using fields and tables an awful lot easier.

While you can have as many of these lookups as you want in a table, ideally you should not do it here. Tables and queries should never be used for direct data entry - precisely for the reason that advanced (VBA) logic cannot be included. For example, in your requirement, the combo box to limit the states must be requeried for it to show the correct values when the country is changed. You cannot do this in a table.

To do it in a form is pretty straightforwards. This link shows you an example, and one way to do it:

Another way to do it is to make the second combo box's RowSource be an SQL query that has the first combo as a parameter, eg:
SELECT States FROM tblStates WHERE Country = Forms!MyForm!MyCountryCombo

You then need to issue a Requery command in the AfterUpdate property of the first combo to get the second combo to update its contents correctly:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
esotericxaAuthor Commented:
Many thanks. Answers look simple as I had hoped. now I just have to practice for a bit. I am still amazed at how few people know how to do this at all, let alone with complete confidence. Even fewer can or will put it down in readable form. Frankly this is the only answer I have ever seen and the brevity and completeness show great competence. Well Deserved points.
Thanks, glad I could help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.