Subform issue (1 Viewer)

[knn9413]

I have a main form that has a combo box (cboCurrentEmployeeName) to select an Employee Name. And then there is a subform within the same form that displays the list of active tasks.

The record source for the subform is a query. The query is as follows. Please note that the tables are joined on employee-id and not the employee name.

SELECT TasksEntries.Project, TasksEntries.Task, TasksEntries.StartDate, TasksEntries.Status FROM TasksEntries INNER JOIN (SELECT EmployeeId FROM Employees WHERE Employees.EmployeeName=Forms!frmMainForm!cboCurrentEmployeeName) AS EmpList ON TasksEntries.EmployeeId=Emplist.EmployeeId WHERE TasksEntries.Status<>'COMPLETE';

The names of the fields in the subform are Project, Task, StartDate, Status and these are present in the subform only and not on the main form

I added an after update event on the cboCurrentEmployeeName which is as follows

Private Sub cboCurrentEmployeeName_AfterUpdate()
Me("StartDate").Form.Requery
End Sub

When I select an employee name from the combo box I get a runtime error 2465: Microsoft office can't find the field StartDate referred to in your expression. I get the same error doesn't matter what field I use.

Your help will be much appreciated.

 

[pbaldy]

You want the name of the subform control there, not a field:

http://www.mvps.org/access/forms/frm0031.htm

 

[knn9413]

You want the name of the subform control there, not a field:

Hi Paul,

I am new to this so I apologize in advance if I misunderstand / misstate anything. So based on my form, I think this is what I should be using.

Me!Subform1.SourceObject

My main form is frmMainForm and my subForm on this is called frmActiveTasks

So I tried doing 2 things but both ended up with the same error. I definitely think I am doing something wrong

I tried Me!frmActiveTasks.Project.Requery (Project is one of the controls in the subform) and Me!frmActiveTasks.Requery and both of them returned the same error "Microsoft access can't find the field 'frmActiveTasks' referred to in your expression"

I have double checked the name of the form and it is correct

Unsure of how to proceed with this now.

 

[Galaxiom]

Use the name of the subformcontrol on the main form, not the name of the form which is its SourceObject.

Me.subformcontrolname.Requery

 

[Galaxiom]

Why the subquery? This should be the same as your query

SELECT TasksEntries.Project, TasksEntries.Task, TasksEntries.StartDate, TasksEntries.Status 
FROM TasksEntries 
INNER JOIN Employees 
ON TasksEntries.EmployeeId=Employees.EmployeeId 
WHERE Employees.EmployeeName=Forms!frmMainForm!cboCurrentEmployeeName) 
 AND TasksEntries.Status<>'COMPLETE'

In fact the Employee table is only required for matching the name.

A better approach would be to display EmployeeName in the combo but have the EmployeeID as its BoundColumn. Then you would not even need the Employee table in the query.

SELECT Project, Task, StartDate, Status 
FROM TasksEntries 
WHERE EmployeeID=Forms!frmMainForm!cboCurrentEmployeeName) 
 AND Status<>'COMPLETE'

Another alternative that requires no code is to use this in the subform and use the subformcontrol's LinkFields to pick the EmployeeID

SELECT EmployeeID, Project, Task, StartDate, Status 
FROM TasksEntries 
WHERE Status<>'COMPLETE'

LinkMasterFields: cboCurrentEmployeeName
LinkChildFields: EmployeeID

 

[knn9413]

Use the name of the subformcontrol on the main form, not the name of the form which is its SourceObject.

Me.subformcontrolname.Requery

I am not sure at all now. I am uploading the file now. I am hoping someone can take a look at it and help me out

Thanks in advance

 

[bob fitz]

See attached db. Perhaps this is what you require:

 

[knn9413]

See attached db. Perhaps this is what you require:

Thanks. that was perfect. I see the mistake I was making.

 

[knn9413]

Why the subquery? This should be the same as your query

SELECT TasksEntries.Project, TasksEntries.Task, TasksEntries.StartDate, TasksEntries.Status 
FROM TasksEntries 
INNER JOIN Employees 
ON TasksEntries.EmployeeId=Employees.EmployeeId 
WHERE Employees.EmployeeName=Forms!frmMainForm!cboCurrentEmployeeName) 
 AND TasksEntries.Status<>'COMPLETE'

In fact the Employee table is only required for matching the name.

A better approach would be to display EmployeeName in the combo but have the EmployeeID as its BoundColumn. Then you would not even need the Employee table in the query.

SELECT Project, Task, StartDate, Status 
FROM TasksEntries 
WHERE EmployeeID=Forms!frmMainForm!cboCurrentEmployeeName) 
 AND Status<>'COMPLETE'

Another alternative that requires no code is to use this in the subform and use the subformcontrol's LinkFields to pick the EmployeeID

SELECT EmployeeID, Project, Task, StartDate, Status 
FROM TasksEntries 
WHERE Status<>'COMPLETE'

LinkMasterFields: cboCurrentEmployeeName
LinkChildFields: EmployeeID

Thanks. I like the first approach that you mentioned using the bound columns. I will try and use that approach to eliminate the subquery

 

[knn9413]

Why the subquery? This should be the same as your query

A better approach would be to display EmployeeName in the combo but have the EmployeeID as its BoundColumn. Then you would not even need the Employee table in the query.

SELECT Project, Task, StartDate, Status 
FROM TasksEntries 
WHERE EmployeeID=Forms!frmMainForm!cboCurrentEmployeeName) 
 AND Status<>'COMPLETE'

In the above approach, how does it know to pick up the employee id? Using the cboCurrentEmployeeName would imply it is picking up the name correct? Shouldn't I be retrieving the employee id and then using it here?

 

[knn9413]

I did try this approach and it worked great though. also understood that the bound column being set to the employee id forces that to be picked up.

One question that I have is, if I need to use / pass the name to a different form, how do I do that? Based on this won't I be passing the employee number each time?

- I found the solution for this one. thanks