combi box to long (1 Viewer)

[dtdukes86]

hi all i hope everyone is well, I was asked a good question today that I wasn't sure about, my client asked me about a combi box he has on the new order form, the box at the moment has his customer list as the record source for the combi box drop-down in last name order, working great so I thought, the bound field is a customer account number made up of UCN-AB-XXXX , where ab is the account holder intials and xxxx is the customer id number from the table.

anyway, he told me it takes too long to scroll through the nearly 450 records to find the customer he wants. he then explained he's worried that in a year's time, the volume of customer details saved would soon be overwhelming as this 450 is from a month or two, so he asked me if he could filter the drop-down list.

For example, is he wanted to select a customer with first name jody he can type jo and the list filters it, i explained if he types "ucn-j" the list does jump to the closest account number but he wanted to take this idea further. I'm wondering what the option might be. has anyone got similar issues I've seen this before but said it cannot be done.

 

[theDBguy]

Hi. Do a search on "FAYT" on this website.

 

[Ranman256]

i have a textbox above the combo box to filter: txtFlt

when user enters the filter string, switch source queries of the combo

the query sql is like:
qsFilterRecs = "select * from table where [Acct] like '*" & txtFlt & "*'"

'the AfterUpdate event for the filter:

sub txtFlt_Afterupdate()
   if isnull(txtFlt) then
     cboBox.Rowsource = "qsAllRecs"
   else
     cboBox.Rowsource = "qsFilterRecs"
   endif
cboBox.requery
end sub

 

[CJ_London]

I use code like this

Private Sub cboCtrl_Change()

    cboCtrl.RowSource = "SELECT somefields FROM myTable WHERE afield like '*' & cboCtrl.text & '*' ORDER BY afield"
    If showdropdown Then cboCtrl.Dropdown
   
End Sub

Private Sub cboCtl_Click()

    showdropdown = False
   
End Sub



Private Sub cboCtrl_Exit(Cancel As Integer)

    cboCtrl.RowSource = "SELECT somefields FROM myTable ORDER BY afield"
   
End Sub

change names ( cboCtrl, somefields, myTable, afield) to suit

 

[MajP]

Find as we typed

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...

www.access-programmers.co.uk

 

[GaP42]

I have used the code from:
https://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html as well as MajP's depending on circumstances.