Character by character query

[bez999]

I have an Access database where I display a form showing postcodes.
The requirement is to be able to type a partial postcode and see a list of postcodes beginning with those characters.

The form is driven by a query that gets its parameter from a text box on the form. At the moment I enter the partial postcode and press enter and the query gives me all the postcodes starting with those characters and that works fine.

What would be nicer for the user is as they type the postcode the list would match their characters and thus gradually reduce the number of postcodes shown.
I have got it to react to key presses and refine the list but after each character is typed I have to set the focus back to my input box. Setting the focus in VBA selects all the text in the box.

The problem I have is that I need to reposition the cursor to the end of the input box so that the user sees his postcode appear normally.

Has anybody any ideas on how I can do this, please?


Regards
Chris Bezant

 

[Remou]

You can use the Change event to requery the listbox. The Row Source of the list box should be something like:

[tt]SELECT tp.Key,
tp.Postcode
FROM tblP tp
WHERE tp.Postcode
LIKE Forms!frmForm!txtPostcode.Text & "*"[/tt]

 

[PHV]

Have a look at the SelStart property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

I built a class module that encapsulates code for a "find as you type" combo box. Not sure if that is what you are asking.
faq702-6304 but the code will change any combo into a find as you type combo.

 

[TheAceMan1]

How are ya bez999 . . .

A combobox with [blue]Auto Expand[/blue] property set to yes would work great here . . . and save you all the coding!

See Ya! . . . . . .

 

[MajP]

thus gradually reduce the number of postcodes shown

Auto expand will match a value, but will not give you that functionality. The module requires the user to write just four lines of code:


'Public faytCombo As FindAsYouTypeCombo
'
'Private Sub Form_Open(Cancel As Integer)
' Set faytCombo = New FindAsYouTypeCombo
' Set faytCombo.FilterComboBox = Me.YourComboBoxName
' faytCombo.FilterFieldName = "strLastName"
'End Sub

The rest is encapsulated in a class module. Drop it in a module and use it whenever you want.