Filtering in data sheet view on combo box with multiple columns?

[Kerdio]

I have a form in datasheet view, and one of the columns [Assignee] is a combobox. When I try to click on the [Assignee] header to filter, I run into some weird issues.



I want the user to be able to dropdown the Assignee field and select a FullName from tblUsers, but for that value to be set to the UserID field from tblUsers. So the end user would see the assignee’s name, even though the value is the assignee’s UserID.

So I’ve tried two different things:

Option 1:

• Set the Assignee row source = “SELECT UserID, FullName FROM tblUsers;”
• Set the Assignee bound column = 1 (i.e., UserID)
• Set the column widths = 0”;2”

In Option 1 it seems to work perfectly, until I try to filter. But if I click the [Assignee] column header to filter for names, then select which names I want to filter, it returns 0 results. It’s so strange. But it seems to me that it is filtering for the FullName value (which is what is shown in the filter dropdown) instead of the UserID value.

Option 2:

• Set the Assignee row source = “SELECT FullName, UserID FROM tblUsers;”
• Set the Assignee bound column = 2 (i.e., UserID)
• Set the column widths = 2”,0”

This also seems to work fine until I try to filter. When I click the [Assignee] column header to filter for names, I get the following error message: “Syntax error in Query. Incomplete Query Clause.”. It doesn’t even let me see the filter dropdown, it just goes straight to this error message.



Any idea what I can do to fix this?

 

[arnelgp]

I would say use Option 1, and most important check if there are Null (blank) values on Assignee field on your table.
you should not have a null value on it on all records.