How to use cascading combos to *select* locations

[Medoomi]

Hi,

I'm a bit lost, and finding the Access learning curve *very* steep...

Anyway, I have read about using filtered queries to limit available
selections in eg. combo box 2 based on the entry selected from combo
box 1. However, I think I need something a little different.

I am trying to develop a database to catalogue "talks" given at
various locations (locations are three lookup tables: Country, Region,
and Building). And the main reason I do not wish to use filtered
queries is that I wish the locations to be unique (eg. two different
regions might have a building with the same name, but these locations
are unique).

So far, I have the following table for the talks

tblTalks
TalksID (PK Autonumber)
Talks

And I have the following structure for drilling down to a unique
location:

tblLocations
LocationID (PK Autonumber)
CountryID (FK)
RegionID (FK)
BuildingID (FK)

tluCountries
CountryID (PK autonumber)
Country

tluRegions
RegionID (PK autonumber)
Region
CountryID (FK)

tluBuildings
BuildingID (PK autonumber)
BuildingName
RegionID (FK)

But I would not have a clue how to relate the talks to the locations.
My only stab was to relate tbltalks to tblLocations through another
table containing
TalksID (FK)
LocationID (FK)
in order to create a many-to-many relationship--for on talk can be
given at many locations, and one location can receive many talks)

At the moment I don't know the next step.
Can someone please enlighten my befuddled mind.
Thx

 

[Guest]

It maybe that buildings in different locations can have the same name but do
you want to establish a many-to-many relationship? Disregarding the name of
the building, the physical object itself can only be in one place
(obviously!) so I am wondering whether that is a good idea.

From what I can tell from the PKs and FKs you are creating relationships
between tables directly as well indirectly through the "locations" table.

tblecountry
countryID (PK)
Country

tbleregion
RegionID (PK)
Region
CountryID (FK)

tblelocations
LocationID (PK)
RegionID (FK)

tblebuilding
BuildingID (PK)
BuildingName
LocationID (FK)

tblejunction
BuildingID (FK)
TalkID (FK)

tbletalk
TalkID (PK)
TalkName


When you come to create your form put "tbletalk" in the main form and
"tblejunction" in the subform. Use "tblebuilding" as the source for the
"buildingID" field in "tble junction".

Hope that helps.

 

[Arvin Meyer [MVP]]

There is an example using cascading list boxes (which work the same way) on
my website at:

http://www.accessmvp.com/Arvin/Combo.zip

 

[Guest]

Maybe I'm reading more into your question than there is. I see it as a "how
do I find the right building so I can assign a talk to it, using combo boxes
to 'drill down' to get to the building by selecting Country, then Region
first?" If that's the case, maybe this will help. There may be some syntax
errors here, but I think it is essentially correct.

Let's call the form "MyForm". In a convenient place on your form, say the
header, put 3 combo boxes - cboCountry, cboRegion, and cboBuiding. They are
UNBOUND and have as their row source the following SQL statements:

cboCountry: SELECT * FROM tblCOUNTRY
cboRegion: SELECT * FROM tblREGION WHERE ( tblREGION.CountryFK =
Forms![MyForm].cboCountry )
cboBuilding: SELECT * FROM tblBUILDING WHERE (tblBUILDING.RegionFK =
Forms![MyForm].cboRegion )


Next add the following to the code behind MyForm

Private Sub cboCountry_Click()
' clear previous selections
cboRegion = Null
cboBuilding = Null
If Not IsNull(cboCountry) Then
cboRegion.Requery
End If
cboBuilding_Click ' clears the form since we don't have a Region or
Building selected
End Sub

Private Sub cboRegion_Click()
' clear previous selection
cboBuilding = Null
If Not IsNull(cboRegion) Then
cboBuilding.Requery
End If
cb0Building_Click ' will clear the form since we haven't selected a
building for the new region yet
End Sub

Private Sub cboBuilding_Click()
If Not IsNull(Me.cboBuilding) Then
Me.Filter = "( BuildingFK = " & Me.cboBuilding & ")"
Else
Me.Filter = "( BuildingFK = -1 ) " ' Impossible situtation, will clear
the form
End If
Me.FilterOn = True
End Sub