How do you genericly or easily build of herarchical trees within MSAccess2000 or later?
Posted on 2003-03-16
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
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
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
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.