Cascading Combo Box not filtering

[Phred]

Front end Access 2007. Back end SQL Server 2008 R2.



I have two combo boxes on the same form. The two tables in the query are normalized with primary keys and foreign keys.

Combo Box 1: EntityTypeCMB holds the following values:

PK EntityType
1 Client
2 Owner of Record
3 Complainant Affiant

Combo Box 2: EntityRelationshipCMB

FK EntityType
1 Owner
2 Individual
2 Corporation
2 LLC
2 Company
3 Owner
3 Lessee
3 Trust Beneficiary

In the first combo box if you select "Client" the only value that should appear in the second combo box is "Not Applicable"

In the first combo box if you select Owner of Record four values should appear in the second combo box, "Individual", "Corporation", "LLC", "Company".

In query builder a relationship is established from the PK of the Entity Type table to its corresponding FK in the RelationshhipType table.

The first combo box Entity Type works fine. Calls the values from the table, displays the EntityType name "Owner of Record" on the screen and populates the underlying table with the PK in this instance 2.

I have two problems:

Problem 1. The "Select" and "From" statements work correct and return the correct values. The "Where" statement is not filtering correctly and does not give me any values. So something is wrong with my Where statement.

SELECT dbo_EntityTypeLookup.EntityID, dbo_EntityRelationshipLookup.EntityRelationship
FROM dbo_EntityTypeLookup INNER JOIN dbo_EntityRelationshipLookup ON dbo_EntityTypeLookup.EntityID = dbo_EntityRelationshipLookup.EntityTypeLookup_EntityID
WHERE (([dbo_EntityTypeLookup]![EntityID]=[Forms]![Property]![PropertyEntityJoin].[Form]![EntityID]));

Problem 2. I am not sure where I should place this SQL statement for best operation. Should it function as a "Row Source" statement or should it be a "stand alone" query that is called from the On Click event from the second combo box.

I hope I have given enough data.

Thanks

Fred

[June7]

Be aware that cascading combobox/listbox with lookup alias will not work nice in continuous or datasheet view form.

Why are you using a join in the SQL? I need better understanding of table structures.

I suggest placing the SQL statement in RowSource property.

Also suggest you name the combobox different from the field, like cboEntityID

SELECT EntityRelationship FROM dbo_EntityRelationshipLookup WHERE [EntityID]=[cboEntityID];