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 "AfterUpdate" 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:
Expand|Select|Wrap|Line Numbers
- Private Sub SelectProjectCombo_AfterUpdate()
- Me.TaskSelectCombo.Requery
- End Sub
Expand|Select|Wrap|Line Numbers
- 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;
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.