I'm trying your examples and they seem to be what I am looking for (when I look at your supplied example).
However, when I try to implement in my database they don't work as expected.
When I attempt to use your class version I get Runtime Error 3061 Too few parameters. Expected 1.
The initialise line I've used is:
SAYT_ListBox.Initialize Me.JobList, Me.txtSearch, "[Client Name]"
And my recordsource is:
SELECT IIf([tblAccType].[AccTypeID]=1,[tblClient].[ClientName] & " - CASH",[tblClient].[ClientName]) AS [Client Name], tblStatus.StatusType AS Status, tblJob.JobID AS [Job ID], tblJob.JobTitle AS [Job Title], tblJob.JobStatus_FK, Nz([JobDateExpected],DateValue(Now())) AS [Due Date]
FROM tblAccType INNER JOIN (tblContact RIGHT JOIN ((tblStatusCategory INNER JOIN tblStatus ON tblStatusCategory.StatusCategoryID = tblStatus.StatusCategory_FK) INNER JOIN (tblClient INNER JOIN tblJob ON tblClient.ClientID = tblJob.JobClient_FK) ON tblStatus.StatusID = tblJob.JobStatus_FK) ON tblContact.ContactID = tblJob.JobContact_FK) ON tblAccType.AccTypeID = tblClient.ClientAccType_FK
ORDER BY IIf([tblAccType].[AccTypeID]=1,[tblClient].[ClientName] & " - CASH",[tblClient].[ClientName]), tblJob.JobID;
When I try to use your non class version, it always selects the item before the expected item. Think this is due to having Column Heads set to Yes, and when I set it to No, it works as expected. But can't find where I would alter your code to make it step along an extra line to account for the Column Heads.