Combo box relating to another Combo Box

[srpatel]

I have my tables structured in the manner displayed below. I am having difficulty in created the second sql query for the 2nd combo box to display data which is dependant on what the user selects from the 1st combo box. Please see below.

************************

tblLUPAssetClass
ClassID
Class PK

tblAssetType
AssetTypeID PK
ClassID
AssetTypeCode
AssetTypeDesc

tblAssetTypeLocation
AssetTypeLocationID
AssetTypeID PK
StructureLocationID PK

tblStructuredLocations
StructureLocationID
StructureLocationBase PK
StructureLocationDetail PK


******************

tblStructuredLocations – Contains the 3 types of locations we have.
e.g. Factory, Depot, Warehouse

tblAssetType – e.g. Floor Boards, Skirting, Coving

tblAssetTypeLocations – this table identifies where all assets are kept, the items have multiple locations
e.g. Floor Boards could be at all three Locations- Factory, Depot, Warehouse
e.g. Skirting only at Warehouse.

*******************
In combo Box One – I have the StructureLocations Table in the Row Source = tblStructuredLocations.

In Combo box two – I can’t seem to get the sql query right where by when a user selects say Warehouse, only the relevant relating fields would appear.

Your help is much appreciated.
Many Thanks
Shreekant

 

[TheAceMan1]

How are ya srpatel . . .

[blue]I can’t seem to get the sql query right where by when a user selects say Warehouse, only the relevant relating fields would appear.[/blue]

[blue]tblStructuredLocations[/blue] is [purple]not related to any of the other tables[/purple] . . . so how could you!

You need to get an relationship in there . . .

See Ya! . . . . . .

 

[srpatel]

Hey there AceMan,

Am kinda new to access development, from my relationships manager, i can see that StructureLocationID appears to be related to the AssetTypeLocation table.

lemme know if its still wrong!

Cheers!

 

[PHV]

something like this ?
In the AfterUpdate event procedure of combo one:
Me![combo two].RowSource = "SELECT * FROM tblAssetTypeLocation WHERE StructureLocationID=" & Me![combo one].Column(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886