By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Searching results in Combobox in subform with 'Like'

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOnderwerp.*, tblOnderwerp.Onderwerp FROM tblOnderwerp WHERE (((tblOnderwerp.Onderwerp) Like "*" & Forms!frmZoekRegistraties!subformRegistraties.Form!Onderwerp & "*")) ORDER BY tblOnderwerp.Onderwerp; 
But no results are returned.

Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblOnderwerp.Onderwerp) Like "*" & [Forms]![frmZoekRegistraties]![subformRegistraties].[Form]![txtZoekOnderwerp]& "*"));
Does not return any results either...

Does anyone have a suggestion?

Thanks!
Mar 16 '12 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,418
Working with references to other objects is often a problem when working directly in SQL (Rather than when building up SQL in VBA).

Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT   *
  2. FROM     [tblOnderwerp]
  3. WHERE    ([Onderwerp] Like '*' & Forms!frmZoekRegistraties!subformRegistraties!Onderwerp & '*')
  4. 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.
Mar 16 '12 #2

100+
P: 759
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.
Mar 17 '12 #3

NeoPa
Expert Mod 15k+
P: 31,418
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)).
Mar 17 '12 #4

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Mar 20 '12 #5

NeoPa
Expert Mod 15k+
P: 31,418
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.
Mar 20 '12 #6

P: 4
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.
Mar 21 '12 #7

100+
P: 759
A solution can be (see attachment)
Attached Files
File Type: zip SmartCombo.zip (88.5 KB, 92 views)
Mar 21 '12 #8

NeoPa
Expert Mod 15k+
P: 31,418
Merlyn:
I don't have a textbox control, I only have a combobox.
So what is [Forms]![frmZoekRegistraties]![subformRegistraties].[Form]![txtZoekOnderwerp] 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.
Mar 22 '12 #9

P: 4
Mihail: thank you so much!!! This was what I was looking for! Your attached example was the solution I needed.
Mar 23 '12 #10

NeoPa
Expert Mod 15k+
P: 31,418
@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.
Mar 23 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.