Lookup query on own table

[MrRuz]

Hi guys!



I'm trying to run a query in a lookup attribute that references the table that it's in while maintaining data integrity. The problem I'm having is restricting the lookup to records that match the current record.

For example, in Table 1 (tbl_Category) below, I can query Level1 of Category2 to only show Category as an option by using the SQL

SELECT tbl_Category.Level2, tbl_Category.Name
FROM tbl_Category
WHERE (((tbl_Category.Level1) Is Not Null))
ORDER BY tbl_Category.CategoryName;

My problem is I'd like to restrict the selections of all records in Level1 and Level2 to records where Type is equal to the Type of the current record. So, For example, in Table 2, Category 3 in Level 1 would only be allowed to select Category 3 or Category 4 because their types are equal.

I would of course, use this same method in Level2.


Table 1: tbl_Category

ID	Type	Name	Level1	Level2
1	1	Category	null	null
2	1	Category2	Category
3	2	Category3	null

Table 2: tbl_Category

ID	Type	Name	Level1	Level2
1	1	Category	null	null
2	1	Category2	Category
3	2	Category3	null
4	2	Category4	Category3

[June7]

Sounds like cascading comboboxes. Review http://www.datapigtechnologies.com/f...combobox2.html

[MrRuz]

Hey thanks! I'll look at it when I get home.