Searching results in Combobox in subform with 'Like'

[Merlyn]

Hi,

I have a form with a subform. On the subform a combobox is shown. It contains over 500 items. I want to offer the users to find results containing the entered letters. (Eg. when entering "for", the results "effort", "ford" and "therefore" should be shown).

So in the properties of the combobox I use this as the rowsource:

SELECT tblOnderwerp.*, tblOnderwerp.Onderwerp FROM tblOnderwerp WHERE (((tblOnderwerp.Onderwerp) Like "’*" & Forms!frmZoekRegistraties!subformRegistraties.Form!Onderwerp & "*’")) ORDER BY tblOnderwerp.Onderwerp;

But no results are returned.

WHERE (((tblOnderwerp.Onderwerp) Like "*" & [Forms]![frmZoekRegistraties]![subformRegistraties].[Form]![txtZoekOnderwerp]& "*"));

Does not return any results either...

Does anyone have a suggestion?

Thanks!

[NeoPa]

Working with references to other objects is often a problem when working directly in SQL (Rather than when building up SQL in VBA).

Try :

SELECT   *
FROM     [tblOnderwerp]
WHERE    ([Onderwerp] Like '*' & Forms!frmZoekRegistraties!subformRegistraties!Onderwerp & '*')
ORDER BY [Onderwerp]

NB. Never develop code of any kind in a word processor or you may end up with invalid quote (' & ") characters. Only text editors can be relied on not to replace the proper characters with the slanted ones more popular in documents. If you didn't notice this problem then you need to be especially careful to avoid that risk.

[Mihail]

Certainly this can be done.
But, as far as I can think, not by using a combo box.
I can show you a solution based on a text box and a list box if you agree this approach.

[NeoPa]

The ComboBox is what the RowSource is connected to Mihail. Essentially it shows the results of the filter. We don't know the source of the filter as the question (rather strangely) didn't include that information. I would expect that to be a TextBox control from what Merlyn has said. It's also not clear whether this control is on the main form or the sub form, which would certainly be important info for the question.

From your post, I suspect you've misunderstood something about the question (which isn't too surprising as it isn't clear in many respects - another non-English-speaking poster, so it's quite understandable (Although missing out important information is less understandable as that doesn't depend on which language one speaks)).

[Merlyn]

Thank you for your reply, sorry I haven't been clear about what I want to do.
The subform only shows a Combobox, not a Textbox.
As rowsource of the Combobox I now entered:

SELECT * FROM tblOnderwerp WHERE ([Onderwerp] Like '*' & Forms!frmZoekRegistraties!subformRegistraties!Onderwerp & '*') ORDER BY [Onderwerp];

according to NeoPa's answer. But that doesn't show any filtered results.
I thought of trying what Mihail suggests, but that's not really what I prefer, so if it would be possible to use the combobox, I would be really glad.
@NeoPa: The Combobox is on the subform. ("On the subform a combobox is shown"). And what do you need to know stating: "We don't know the source of the filter as the question (rather strangely) didn't include that information." The filter of what control?

If any other information is needed, please let me know!

[NeoPa]

Merlyn:
And what do you need to know stating: "We don't know the source of the filter as the question (rather strangely) didn't include that information." The filter of what control?

Controls don't have filter properties. I was referring to the filter (or WHERE clause) in the RowSource string that you have included in your post.

As it happens, it isn't important where the control is to be found, but certainly referring to it correctly is. In your original post the name of the TextBox control is not specified in the question. Normally, that wouldn't be a problem, but your code refers to it using two distinct names. We do need to know which reference is correct to be able to suggest a solution that will work. My earlier suggestion used the name taken from the first attempt you posted.

[Merlyn]

I don't have a textbox control, I only have a combobox. By entering a few characters in the combobox I want to fiter the results shown.

[Mihail]

A solution can be (see attachment)

[NeoPa]

Merlyn:
I don't have a textbox control, I only have a combobox.

So what is [Forms]![frmZoekRegistra ties]![subformRegistra ties].[Form]![txtZoekOnderwer p] referring to in your post #1 then? If that's not a TextBox then you have a very strange naming convention. Typically, any name starting "txt" should refer to a TextBox.

[Merlyn]

Mihail: thank you so much!!! This was what I was looking for! Your attached example was the solution I needed.

[NeoPa]

@Merlyn

I appreciate the sentiment of selecting Mihail's post, but you cannot select a post that has no visible content. Mihail struggles with explanations in English, so we cut him some slack on the usual rule of including the explanation in the post itself, but frankly it's of very limited use for anyone else to find, even if the attachment were visible in the Best Answer area (which they're not).

@Mihail
Actually it's a shame that you posted a solution at all. It stopped Merlyn from having to understand the issues that were evident in their existing attempts, and left the whole issue very unclear for any readers who follow. It's too late to delete it now, but I'll be more aware in future. I hope you understand why our rules prohibit the simple posting of answers without any explanations. It does more harm than good in the long run.