fill a combo based on first 3 characters entered

[kpalazzi]

I have a form with a subform. The subform is based on a query. I have a combobox that lists customer names on the main form and fills in the fields on the subform based on the combo selection.

This works great except the db will hold 90,000 + customer records so I need the combo to fill with only the list of customer names based on the first 3 characters entered.

Ex. combo = enter smi, fills with Smith, Smitty, Smithe, etc.

Tblcustomers - has the custname field

qryEtch is the query the subform is based on and has the detail fields I need to autofill based on combo

tblClaims_Etch is the table the main form is based on and has the combo named customer

Kimberly Palazzi
kpalazzi@mjhinc.com

 

[BoxHead]

The following rowsource on a combo box that displays Cities limits itself to those cities that begin with the first letter typed in the combo box.

SELECT DISTINCT tblSalesCustomers.CustCity
FROM tblSalesCustomers
WHERE (((Left([CustCity],1) Like Left([Forms]![frmMain]![combo5],1)));

On the Combo5 Change event...

  Cmd1.SetFocus
  Combo5.Requery
  Combo5.SetFocus
  Combo5.SelStart = Nz(Len(Combo5),0)

You cannot requery the combobox while it has the focus so the focus is shifted to a command button, 'Cmd1' while the combo is requeried. The SelStart just sets the cursor at the end of the combobox in case the person wants to continue typing. Since the combo box is populated at this point, the autoexpand is available.

For changing this to handle the first three letters, you would have to set the criteria to reflect Left(fieldname,3) and probably limit the requery call with

If Len(Combo5)=3 Then
        Combo5.Requery

etc...

HTH
John

Use what you have,
Learn what you can,
Create what you need.