Combo drop down box doesn't display the correct information, please help!

[shaimakem]

I would really appreciate your advice in the following situation:

My database has only 2 tables (Departments: DeptID, DeptName) and (RecordManagEntry: RMNumber, Year, DeptID)

I have a form that has 2 boxes (one of which is a combo drop down that displays the department names to be selected), and the other is showing the next availabe box number to be added for the database for our facility. This form was created origionally for one Facility that has multiple departments.

Now, they need me to add different facilities with their departments for other regions, so the user can add box numbers for different facilities, so I added the table: (Facility: FacID, FacName), created the relationship for it 1:M with Department table, inserted couple of records, added another Combo box in the same Form that shows the FacilityNames in its drop list:

What I need the form to do:
When I select the facility from the drop down box, I need the 2nd combo box that shows all departments to display only the departments for that specific selected Facility.
it seems no matter what facility I select now, it shows me all the deptNames from the drop down, how can I relate those combo boxes to each other?

[June7]

Review: http://www.datapigtechnologies.com/f...combobox2.html

Did you create another table for associating facilities and departments - a junction table between Departments and Facility tables?

[shaimakem]

I didn't actually create the junction table as I thougt each Facility can have many departments 1:M but not the other way around, so if its not M:M relationship then the bridge table is not necssary to my understanding correct?

I can create it, but how do I proceed from there to solve my issue?
Thanks again for any advice.

[June7]

So each department will be associated with only one facility? You added a field in Departments for the FacilityID?

Did the tutorial help?

RowSource for the facility combobox:

SELECT FacID, FacName FROM Facilities ORDER BY FacName;

RowSource for the department combobox:

SELECT DeptID, DeptName FROM Departments WHERE Facility = [cbxFacility] ORDER BY DeptName;

Then need code to requery department combobox. Suggest you put it in department combobox GotFocus event.

Be aware, dependent comboboxes with lookup alias will not work nice in continuous or datasheet form.

[shaimakem]

Yes correct, I added the FacilityID field in the Department table (I think it didn't allow me to create the relationship withouth adding this column as a FK in the child table), so each department can belong to only one facility, then I checked the "Enforc Referential Integrity"...
Since I only have about 10 departments, I added 2 facilities only so far to test with, so I manually added the new departments and it was easy to insert values for the Foreign Key column that I created in the department table (FacilityID).

It seems the Tutorial is exaclty what I need to do, so I will re-watch it and try the requery and the rowsource (These are new concepts for me! so I will search to see why it was needed).
Thanks for helping my little brain