referring to a field in a datasheet form

[BillE]

I have a typical form/subform, and the subform is a datasheet with several
columns.

One of the columns is a combo box, and the rowsource is an SQL statement,
like:
select EmployeeID, EmployeeName, EmployeeDept from tblEmployees order by
EmployeeName

I would like the contents of the combo box to be filtered, based on the
value of another field in the datasheet. For example, showing only the
Employees in the current department.

I tried adding
where EmployeeDept = " & me.EmployeeDeptID
to the SQL statement which populates the dropdown, where EmployeeDeptID is
another field in the datasheet.

However, sometimes the "me.EmployeeDeptID" doesn't correctly refer to the
value of the EmployeeDeptID field in the current row, but has a value from
another row, perhaps the previously selected row, I don't know. The
combobox gets populated with employees from a different department.

Is there a way to do this that works?

Thanks
Bill

 

[Allen Browne]

Although the combo appears on each or of a form that is in Continuous or
Datasheet view, it does not have a separate RowSource in every row.

There is only one RowSource: if you filter the combo so that it does not
have the data needed for some rows, those rows will appear blank (unless the
bound column is the display column.)

The expression:
Me.EmployeeDeptID
refers to the field of the current record. Therefore that value is NOT the
same as the one the combo is in on every row. (I think that's what you
discovered.)

 

[Klatuu]

Use the form current event of the subform to requery the combo box.

 

[BillE]

I understand. Thanks.