Lookup dependent on previous field

[VinceC]

I have 3 tables;

Employer
- eID
- eName

EmployerGroup
- eID (Combo Box lookup of Employer table above)
- eGrpID
- eGrpName

Employee
- eID (Combo Box lookup of Employer table above)
- eGrpID (Combo Box lookup of EmployerGroup table above)
- eeID

My problem: When entering data in the Employee table, after entering the eID, the Combo Box lookup of eGrpID lists ALL EmployerGroups(eGrpID) and not just the ones specific to the eID just entered.

Example, I have 2 companies CompanyA and CompanyB. CompanyA has 2 groups GroupA1 and GroupA2. CompanyB has GroupB1, GroupB2, GroupB3. When I create a record in the Employee table, after choosing say CompanyA, for the EmployerGroup field "eGrpID" I would like only GroupA1 and GroupA2 to be displayed in my dropdown combo box. However, all 5 groups appear.

Can someone help me? I think it has something to do with the Row Source property in table design for eGrpID.

Vince

 

[dcx693]

You should do a search in the forums on cascading combo boxes. Many examples.

 

[VinceC]

Sorry, I can't seem to find a similar example.

My problem is with tables not forms (popular in examples).

I just want the 2nd drop down list to be limited to items relevant to the item from the first drop down list when creating a record in my Employee table.

I will continue looking through the examples. Thanks.

 

[VinceC]

Thanks. I thought that this might be an easy thing to do with tables. I was reluctant to add more objects to the database.

 

[smathisen]

Vince,

I've been looking for the same solution to what should be a simple thing. In my case, I have a clients table which holds client company information. A second table is for client representatives (there may be several representatives from the same company).

So when I designate the client name with a lookup dropdown, then I move on to designate the actual representative. And I only want to look at a list of representatives from the previously selected client, not every representative from every client, which is the case now.

Have you found a solution?

Stein

 

[VinceC]

No, I have not found a solution ... yet. Although I fear that Pat is correct. Its such a drag, because it seems like a simple thing to do.

I played around with the sql statement in the Row Source property of the Lookup for my field in table design. However, I had no luck. I tried to "add" a table to the query design grid. I also tried to use a criteria in the query design grid. I think that the problem is after you have selected a choice in the first drop-down, that choice is not actually saved anywhere yet or is not available for reference. If it was, then you could use it as part of your criteria when building the SQL statement for the Row Source. If you know how to do that please let me know and I would be willing to work on it more with you.

Hope this info. helps or leads to a solution for you or me if we're lucky. Maybe someone out there has an idea or can help. I don't want to use a form if possible.

 

[smathisen]

Vince,

I just located some of the sample documents related to dynamic or cascading combo boxes in forms. I am not an Access expert, though I have taken some courses, but it appears to me that Pat's suggestion is the right one. This is a question of faciliting data entry, which the task of forms. At a glance, the files I've located seem to address the construction of dynamic combos in forms, so I'll work through some of the examples and see.

Stein