Filtering the contents of one combo box on another in a subform

[AliJane]

Access 2010, Windows 7 professional

I'm creating a database for my small business (only two of us, so we each have our own "Update Timesheet" form) partly to learn Access.

I have a form called "Alison_Update_ Timesheets_F" with a subform called "TimesheetTasks _SF" (based on tables with similar names). I am using a combo box in the header of the "Timesheets " form to find the timesheet I want to edit.

On the main form, I show only the TimesheetID and the TimesheetDate (the query that the main form is based on has already filtered to only "my" recent (within 30 days) timesheets).

On the subform, I have a TimesheetID_F (the linking field to TimesheetID, which works fine).

Then on the subform I have three combo-boxes, then a text field for "Hours", and a checkbox for "Billable".

The combo-boxes are causing the problem.

In the first record on the subform, it works fine.
But as soon as I start to fill in the next record, the previous records entries change.

So I can have:

Company1, ProjectA, Task22 in the first record in the subform.

Then as soon as I pick Company2 in the next record, the project and task details blank in the record above.

I have an "AfterUpdat e" event to requery subsequent combos when I change the first in the sequence and I think this may be the cause of the problem.

For example, the code for refreshing the select statement for the Tasks list when I update the Project is:

Private Sub SelectProjectCombo_AfterUpdate()
    Me.TaskSelectCombo.Requery
End Sub

The Select statement for the tasks (the "Data source") has been built using the query wizard and is:

SELECT Tasks_T.TaskID, Tasks_T.TaskDescription, ProjectTasks_T.ProjectID_F
FROM Tasks_T INNER JOIN ProjectTasks_T ON Tasks_T.TaskID = ProjectTasks_T.TaskID_F
WHERE (((ProjectTasks_T.ProjectID_F)=[Forms]![Alison_Update_Timesheets_F]![TimesheetTasks_SF].[Form]![SelectProjectCombo]))
ORDER BY Tasks_T.TaskDescription;

My sub-form is in tabular form, in case that makes a difference.

Oh - and the key to the selected project is written to the TimesheetTasks table as a foreign key (ProjectID_F) so is bound to that, and the same for the Tasks (bound as TaskID_F).

Spent a long time on this and feel I'm going round in circles! Any help gratefully received.

[AliJane]

Sorry - meant to say - Access 2010.

[Rabbit]

Don't worry, the data is still there. It's just that on a continuous form, there's no way to have different record sources for each row. You just have to either live with it or not use cascading combo boxes. I don't know of any other way around it. You could try allowing inputs not in the list but that causes other problems of their own.

[AliJane]

Thanks. I need the combo boxes or it would be a nightmare. I'll perhaps do a "read only" version for seeing what's there as a sanity check, then. Days when only a couple of entries, not a problem. Days when lots of bits, it will be too easy to forget what's been done and what hasn't.

[zmbd]

Indeed, Rabbit is correct:

You may find the following insite articles to be helpful:
Microsoft Access / VBA Insights Sitemap
42.Cascaded Form Filtering
55.Multiple Combobox Filter with Clear Option
168.Example Filtering on a Form.