combo box and query

[niccig]

Please help - new to Access and VBA. I've searched old posts but I still can't work this out.

I have a sub form "Hours" based on the table Hours. This sub form appears within the "Pay" form. On the sub form I want 2 combo boxes: cboFaculty and cboProject. cboFaculty is bound to a query that lists Faculty (from Faculty table, FacultyID, Faculty Name, CurrentFaculty=yes) and stores the FacultyID into the Faculty field in the Hours table. That's fine, I can do that with the combo wizard.

But here's the problem, I want to select the faculty name from cboFaculty, and that then filters cboProject to only show the current projects for that faculty member (this is from the Project Table with fields ProjectID, ProjectName, FacultyID, CurrentProject=yes). Once a project is selected, I want the ProjectID to be stored in the Project# field in the Hours table.

Please remember a newbie with Access and VBA so I need very detailed instructions.

 

[niccig]

What I've tried

Ok, this is what I've tried based on an older post here:
I've got cboFaculty based off the query from Faculty Table (FacultyID, FacultyName, CurrentFaculty=yes), and I've stored the FacultyID in the FacultyID field in Hours table.

cboProject row source is:
SELECT Project.ProjectID, Project.ProjectName FROM Project WHERE Project.FacultyID=Forms!Subhours!cboFaculty ORDER BY Project.ProjectName;
I have bound column 1 to ProjectID field in the Hours Table.

The AfterUpdate code is Me.cboProject.Requery

Problem is that it won't requery. It's kept the first faculty's projects as the options for cboProject even though I've selected another faculty member on the same record, or gone to a new record. Help!

Also, how do I add into cboProject row source to also limit the projects to current projects (CurrentProject=yes)?