Iain:
Your model is spot on. For data entry, say you have a form based on an
Events table, and each event takes place in a specific place, the events
table need only include the SpaceID foreign key column. This is a numeric
value referencing the numeric (e.g. autonumber) primary key column of Spaces.
Using a 'surrogate' numeric key caters for more than one place having the
same name.
To show the region, state and country for the selected place in the form you
have several options:
1. Base the form on a query which joins the required tables, events to
places, places to regions and so on. Bind controls to the region, state and
country columns from those tables. Use a bound combo box for the PlaceID
column and set it up to show the place name like so:
ControlSource: PlaceID
RowSource: SELECT PlaceID, Place FROM Places ORDER BY Place;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
when you select an item in the combo box the relevant region, state etc will
show in the other columns.
2. Base the form on the Events table and use the same bound combo box and
use expressions calling the DLookup function as the ControlSource properties
of unbound text box controls to show the region, state etc, referencing the
bound text box in the DLookup function's criterion.
3. Base the form on the Events table and as the RowSource for the bound
combo box join the relevant tables and return the region, state etc in other
columns in the combo box's list. Then reference these columns in unbound
text boxes, e.g. for the region:
=[PlaceID].Column(2)
The Column property is zero-based, so Column(2) is the third column.
You'll find an example, amongst other approaches, of this last multi-column
combo box method in my demo file for handling this type of hierarchically
related entities at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
One thing about this type of data which is sometimes overlooked is that there
could be a missing link in the chain. I came across this when helping out
someone at the OECD with a database of members internationally. Some small
counties do not have regional structures, so there is no equivalent of State
or Region. Consequently the chain between Place and Country is broken. The
way to cater for this is, for each such country to include a row in the Sates
table with a value in the text column such as N/A and the relevant CountryID
in the foreign key column which references the key of the Countries table.
Similarly in the Regions table include a row with a value in the text column
such as N/A and a value in the StateID column which references the row in
States for that country with the N/A value. All rows in Places for that
country will have a foreign key RegionID value which references the N/A row
in Regions for that country. The chain is then unbroken.
When it comes to adding new data you can use a combo box's NotInList event
procedure to allow you to type in the new value and insert it into the
referenced table. Here's an example of the NotInList event procedure for a
combo box bound to a CityID column:
Private Sub cboCities_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
This opens a frmCities form in dialogue mode and passes the new city name to
it for you to then enter other data, e.g. the County, which would again be
via a combo box in the frmCities form and that combo box would use code in
its NotInList event procedure for adding a new County, and so on up the line.
The Open event procedure of the frmCities form includes the following code:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
BTW, if you've looked at the sample Northwind database you'll have seen that
it does not have tables such as those described above, but simply has columns
in the Customers table for City, Region and Country. Just because it comes
with Access don't assume this is correct. It is of course not correctly
normalized and wide open to inconsistent data being entered.
Ken Sheridan
Stafford, England
Thank you Phil,
The tables I listed were purely ficticious to illustrate what I meant by
tables "in series" but to pick up on the point you made regarding the
pitfalls of geographic regions, each Place would have a Region and each
Region a State etc.
Assuming that whatever contents each table were to represent, then this type
of construction is both sound and permissable. Correct?
Following on from this, if I wished to auto populate Country, State and
Region by selecting a Place or if I wished to add a new Place and choose
which Region it belonged to, is this sort of functionality feasible with the
sort of construct I'm talking about.
Iain
I am slightly confused regarding the viability of relating too may tables
together in series using One to Many relationships for each relationship.
[quoted text clipped - 30 lines]
