Partial lookup in a combo box 1

[vdzr]

With which code is that possible?

event = on click down?

 

[Pampers]

Hi vdzr,
What is a partial lookup?

Pampers
Keeping it simple can be complicated

 

[vdzr]

like "*" & [forms]![searchcmbbox] & "*"

I used now one text box and one combo box with reference to the text box

 

[vdzr]

If you put a value in a combo box, it will show a dropdown list of the 'Input' & "*"

Input = brown

dropdownlist shows :
brown
brown patrick
browny
etc ...

but not the existing query items like
James brown
Jacson brown velvet

Now I use a text field with a after update event (requery combobox) that is used as a parameter in the combo querysource

 

[Pampers]

Sorry, I can not answer this question...

Pampers
Keeping it simple can be complicated

 

[Remou]

You can rebuild the query in the Change event.

 

[Remou]

That was a bit obscure. Set the RowSource to, say:

Select Field From Table Where Field Like "*" & Forms!Form!Combo.Text & "*"

The requery in the change event, I think that should work.

 

[vdzr]

2118 error message You must save the field before requery??
and when I add me.combotest.refresh I get a new error : method or datamember not found
name combo box = ComboTEST
colum count = 2
with = 0;15
source = SELECT TblClienten.Codeprim, [Codeprim] & ", " & [Anaam] & ", " & [Rnaam] & ", " & [Vnaam] & ", " & [TblAutos].[Nummerplaat] & ", " & [TsTblWoning].[Polisnr] & ", " & [TblWoningen].[AdresWoning] AS client FROM ((((((TblClienten LEFT JOIN TblFamilialeVerzekeringen ON TblClienten.Codeprim = TblFamilialeVerzekeringen.SecIDclientcode) LEFT JOIN TblHospitalisatieverzekeringen ON TblClienten.Codeprim = TblHospitalisatieverzekeringen.SecIDCodeClienten) LEFT JOIN TblMutualiteiten ON TblClienten.Codeprim = TblMutualiteiten.SecIDclientcode) LEFT JOIN TblBeroepsverzekeringen ON TblClienten.Codeprim = TblBeroepsverzekeringen.SecIDclientcode) LEFT JOIN TblAutos ON TblClienten.Codeprim = TblAutos.SecIDclienttblWoning) LEFT JOIN TsTblWoning ON TblClienten.Codeprim = TsTblWoning.SecIDCodeClient) LEFT JOIN TblWoningen ON TsTblWoning.PrimIDTstblwoningen = TblWoningen.SelectiePolVerzMak WHERE ((([Codeprim] & ", " & [Anaam] & ", " & [Rnaam] & ", " & [Vnaam] & ", " & [TblAutos].[Nummerplaat] & ", " & [TsTblWoning].[Polisnr] & ", " & [TblWoningen].[AdresWoning]) Like "*" & Forms!FQClienten!ComboTEST.text & "*"));

Private Sub ComboTEST_Change()
Me.ComboTEST.Refresh
Me.ComboTEST.Requery
End Sub

 

[vdzr]

I changed the 'after change event' to
Private Sub ComboTEST_Change()
Me.Refresh
Me.ComboTEST.Requery
End Sub
and the old error is gone !

but now its getting interesting,

The datasheet view from the combo source sql gives the right record but ...
an error message occurs in the form like : error 2237 the text you entered is not an item in the list
and the debugger stops at me.refresh

Is there still a little syntax ...

 

[MajP]

I would think your combo should be unbounded. It is trying to assign a value that is not in the rowsource.

 

[vdzr]

The combo is unbound, and 'after update' he looks for the choosen primIDclient (width = 0")

Can somebody post a tested code, please!

I used the refresh code because a 'save' commond gives an other error message that references to use the me.refresh code.

This partial look up in a combo box would be a nice tool for many people, I think.

thanks already

 

[Remou]

It is more complicated than I first thought, but I will get back here when I have a better version. A the moment I am looking at a textbox positioned over the combobox, which I have half working.

 

[vdzr]

That I have already

Fill the text box and make the width of the combo just that little so you only see the pull down (matchcode).
Tab order -> textbox(=zoekgedeelte),combobox(=cmdzoekclient)

Code for the text box :
Private Sub Zoekgedeelte_LostFocus()
If IsNull([Zoekgedeelte]) Or [Zoekgedeelte] = "" Then
Zoekgedeelte = "*"
End If
Me![CmbZoekClient].Requery
SendKeys "{F4}"
End Sub

If you enter or tab on the text box The combo box [cmbzoekclient] will open automaticly with the filtered items where you can select one to use as goto record after update

hope this helps

But I only want to use a combo box! Why does it only looks up the beginning of a given text and everything else behind the text ([textfield]&"*" and not "*" & [textfield] & "*" !

 

[MajP]

I am sorry I should have said "limit to list = no" in your properties to avoid

error 2237 the text you entered is not an item in the list
and the debugger stops at me.refresh

 

[vdzr]

I can't put limittolist=No when the first primID row is hidden with width = 0";1"

Message = microsoft office can't set the limit to list to No right now!
The first visible column, whitch is determined by the Columnwidths property, isn't equal to the bound column, adjust the columnwidth property first, and set the limittolist property!

I need the ID for gotorecord (afterupdate) and not the concatenated string of namefield and contractnumbersfield and ...

 

[MajP]

Read this FAQ I wrote faq702-6304. It does exactly what you want with you providing two lines of code on a form.

 

[vdzr]

thats the way to go .... thanks