?
Solved

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

Posted on 2003-03-16
3
Medium Priority
?
1,093 Views
Last Modified: 2012-08-13
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.
Enjoy,
Colin
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.

 
0
Comment
Question by:esotericxa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1000 total points
ID: 8147941
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:
http://www.mvps.org/access/forms/frm0028.htm

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:
Me!MyStateCombo.Requery

HTH,

Shane.
0
 

Author Comment

by:esotericxa
ID: 8150510
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8151163
Thanks, glad I could help!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question