Opening A Record in a subform, based on selection in another subform
This is my first post, but often view this site to solve issues. Unfortuantely I have not been able to come up with a solution for my current development. I am working on a Task list with a different type of setup. The main form is a one stop shop for multiple users.
There are two tables
- ID (autonum)
- First Name (text)
- Last Name (text)
- Full Name (Calculated)
- Task (short text)
- Description (Long Text)
- Start Date (short date)
- Assigned To (Lookup to Full Name)
- Etc (Additional fields)
The MainMenu (main form) is set up bound to the Team table. With a dropdown field to select a team member. The form has two subforms (CurrentTaskSub and NewTaskSub). Both Subforms are bound to Tasks. CurrentTaskSub is created from a query of Task where tasks are not complete and past the start date. NewTaskSub is a direct form of the task table.
When a name is selected it uses a parent/child relationship to filter the CurrentTaskSub to tasks assigned to that team member. That part works.
What I want to happen is when you select one of the tasks from CurrentTaskSub, it will populate in NewTaskSub to view all the info on the task and to change or update it. Then it will refresh both Subforms when a change is save in NewTaskSub whether that is completing a task, updating it, or creating a new task.
Here is what I have attempted:
On the CurrentTaskSub subform I select the task field and set that on click it will execute VBA code.
the current code of many I have tried is:
Forms!MainMenu![NewTaskSub].Form.Filter = "[Task] = " & Me.Task
Forms!MainMenu![NewTaskSub].Form.FilterOn = True
'run a findfirst against that recordset
'NewTaskSub.FindFirst "[Task] = " & Me.Task
'if item not found issue warning
'If NewTaskSub.NoMatch Then MsgBox Me.Task & " not found"
Both these produce debug errors. I think I am on the right track but can seem to find the solution I am hoping for. Any assistance or advice is appreciated. Thank you.