filter a combo box in a continuous form

[dmcp]

hi
i have a combo box in continuous form is there a way that i can filter the combo as i write in it?

[puppydogbuddy]

Yes, highlight the combobox in design view, invoke the property sheet, then set the auto expand property to yes.

[dmcp]

this is not what i meant. what i need is to show the records that contain the characters that i wrote in the combo box

[Stewart Ross]

Hi dmcp. It would help us to help you if you would provide enough detail when asking a question so that our expert contributors, who give their time freely and voluntarily, are not misled. In your one-sentence question you did ask about filtering the combo as you wrote in it, not filtering your form. The answer given by Puppydogbuddy was accurate for the question you asked; unfortunately what you asked was not what you really meant.

After you have finished typing whatever characters you want to match into your combo you can apply a filter to the recordsource of your form using VBA code in the After Update event of your combo. You have not provided names of controls or underlying fields so the following skeleton code uses dummy names which you will need to replace.

In design mode select the combo and bring up its properties. Right click on the After Update event and select Build, Code. The VB Editor will be started with an empty After Update subroutine. Copy and paste the following, replacing the dummy names as appropriate:

Dim ComboContents as Variant
ComboContents = Me![name of your combo control]
If IsNull(ComboContents) then
	Me.Filteron = False ' cancel filter if combo cleared
else
	Me.Filter = "[name of the field you want to filter] Like *" & ComboContents & "*"
	Me.Filteron = True
end if

This just applies a filter to the form on whatever field it is you want to filter. The wild card "*" characters are used to ensure matching on any text before and after the characters you want to find.

The filter is cancelled by clearing the combo box (tested for null above) but you may wish to put a command button on the form marked 'show all' to do this for your users instead.

-Stewart