Find as you type (FAYT) combo example for continuous form/datasheet (1 Viewer)

[CJ_London]

Thought I would just put up this simple example for FAYT for a combo on a continuous form/datasheet.

It is based on a similar principle to the cascading combo examples for continuous forms/datasheets I have provided in the past.

It involves using the combo change event to apply a 'Like' criteria to the combo rowsource and then removing the criteria when exiting the combo. Bu doing this the values displayed in the other rows are not affected.

Changing the rowsource effectively involves a requery of the data on each change which might have performance issues for those with very large datasets (10's of thousands of records) and/or slow connection to the back end.

I did trying working with the combo recordset filter instead to avoid the need to reload data, but without success. It would appear the combo recordset cannot have filters applied or assigned a new recordset - at least not within the change event.

To use, simply start typing and select one of the listed items. This example looks for all occurrences of what has been typed because of the initial '*'. In real life it is probably not required and if only occasionally required users can be trained to enter the initial *. Which is probably worthwhile doing anyway because users can then search for something like sm*plc (in this example anyway).

There is a second version of the form which applies criteria to multiple columns (just two in this example) but can easily be extended to more columns and different rules (such as using AND rather than OR)

Note: Other than the event code, the auto expand property of the combo has to be set to No

Note2: Just for fun, I left aligned the dropdown button in the first example. So do not adjust your set. I did this because for single column data with a wide variety of widths, all that whitespace can result in users selecting the wrong dropdown since it can be a long way away. It doesn't work so well for multi column combos as those columns get reversed as well.

 

[arnelgp]

i have to test.
this is what i noticed, why not demo on the customer table (lot's of field?)

first time you just select an Item on the combo, OK.
next time on same record, you select again, It is filtered to the same Item? you need to go to another record just to "remove" the "filter" spell.
that is on form fart1.

 

[CJ_London]

No

next time on same record, you select again, It is filtered to the same Item?

Hit the delete or backspace key and start typing again.

not sure what you want to happen otherwise. Or I don’t understand what you are saying

 

[arnelgp]

not sure what you want to happen otherwise. Or I don’t understand what you are saying

i don't understand your demo either.
the "normal" behaviour is after selecting an item on the list, all items are still available and not filtered.
you only filter when you are typing or in filter mode.

 

[CJ_London]

it does what it says it does. Added another field which you seemed to be implying makes a difference. Clicking the combo button displays all the records.

Only way I can replicate what I think you are saying is for the user to not exit the combo after making a selection but then clicking the dropdown button - which for me would be strange behaviour.

If it bothers you that much, then remove the combo click event and use the mouseup event

Private Sub Combo2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)


    If Y > Combo2.Height Then 'user is making a selection from the dropdown
    
        showDropdown = False
        
    Else 'user is using the dropdown button or clicking on the existing selection. Could be modified to check the X parameter to determine which one
    
        Combo2_Exit False
        Combo2.Dropdown
        
    End If


End Sub

 

[ABE86]

Thought I would just put up this simple example for FAYT for a combo on a continuous form/datasheet.

It is based on a similar principle to the cascading combo examples for continuous forms/datasheets I have provided in the past.

It involves using the combo change event to apply a 'Like' criteria to the combo rowsource and then removing the criteria when exiting the combo. Bu doing this the values displayed in the other rows are not affected.

Changing the rowsource effectively involves a requery of the data on each change which might have performance issues for those with very large datasets (10's of thousands of records) and/or slow connection to the back end.

I did trying working with the combo recordset filter instead to avoid the need to reload data, but without success. It would appear the combo recordset cannot have filters applied or assigned a new recordset - at least not within the change event.

To use, simply start typing and select one of the listed items. This example looks for all occurrences of what has been typed because of the initial '*'. In real life it is probably not required and if only occasionally required users can be trained to enter the initial *. Which is probably worthwhile doing anyway because users can then search for something like sm*plc (in this example anyway).

There is a second version of the form which applies criteria to multiple columns (just two in this example) but can easily be extended to more columns and different rules (such as using AND rather than OR)

Note: Other than the event code, the auto expand property of the combo has to be set to No

Note2: Just for fun, I left aligned the dropdown button in the first example. So do not adjust your set. I did this because for single column data with a wide variety of widths, all that whitespace can result in users selecting the wrong dropdown since it can be a long way away. It doesn't work so well for multi column combos as those columns get reversed as well.

Works like a charm! Thanks
Is there a way to make it so that i could use the arrow keyboard keys to navigate the dropdown list and make the selection with enter key?
I tryed by myself but couldnt

 

[CJ_London]

Don’t know, you would have to try, probably need to use one of the key events which may conflict with other events so would need some care