Access subform datasheet

[hondaman2003]

I have a form and subform. The subform is in datasheet view. This subform has several fields including 2 drop down boxes. The second drop down box is dependant on what you selected in the first drop down box.

For example the first drop down has customers and the second has orders. When you select a customer, the second drop down only shows you the orders for that customer. When i go down to a new record in the subform and select a customer, the order field in the previous record clears out. When i look on the table the correct data is there, it's just that the field clears when i select a customer on a different record. how can I stop this from happening?

 

[dhookom]

You can't stop this from happening in a datasheet. I typically don't filter the second combo box. I will set the order of the records in the combo box to put the valid items at the top of the list.

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

It seems to me that you should be able to filter without effecting the other records.

Can you explain how I can set the order the way you describe?

 

[dhookom]

without effecting the other records" you aren't effecting records, you are effecting the display of values in a control. There is not a separate set controls for each record.

Assuming a form in Northwinds displaying the employee table. You want a combo box to select the Title and then filter the Employee based on the Title.

The employee combo box would have a row source like:

SELECT Employees.EmployeeID, [Title]=[Forms]![frmRptCrit]![cboTitle] AS Expr1, [LastName] & IIf([Title]=[Forms]![frmRptCrit]![cboTitle],""," XXX") AS Expr2 FROM Employees ORDER BY [Title]=[Forms]![frmRptCrit]![cboTitle], [LastName] & IIf([Title]=[Forms]![frmRptCrit]![cboTitle],""," XXX");

This would not limit the display of employees to the title but would identify and sort them. You could also add code to the after update event of cboEmployeeID to check the value of the second column to determine if the user selected the wrong value.

Duane
Hook'D on Access
MS Access MVP