Dynamically 'fill' a combo box ?

[liamfitz]

The following code executes on the 'Key_Up' event of a combo box.

Private Sub cboClients_KeyUp(KeyCode As Integer, Shift As Integer)
strSQL = "SELECT tblClients.Client_ID, tblClients.Forename, tblClients.Surname, tblClients.DOB " & _
"FROM tblClients " & _
"WHERE (((tblClients.Surname) Like '" & cboClients.Text & "*')) " & _
"ORDER BY tblClients.Surname, tblClients.Forename, tblClients.DOB;"
cboClients.RowSource = strSQL
cboClients.Requery
End Sub

An error occurs, on the 'cboClients.Requery' line - " you must save the current field before you run the requery action "
Im thinking it's assuming it's an item already 'in list', and needs to know where to store the reference field. is there a way of filtering the data using my SQL, then selecting an item from the resultant list ( which of course should become increasingly shorter, the more characters you type, and show the specific record being sought eventually ) ? Thank you.

 

[Beetle]

I'm a bit lost as to what you are trying to achieve here. Filter as you type is functionality that is already built into combo boxes, so are trying to add a new value to the list? If so you need to use the Not In List event.

Maybe I'm missing something?

 

[boblarson]

i'm a bit lost as to what you are trying to achieve here. Filter as you type is functionality that is already built into combo boxes, so are trying to add a new value to the list? If so you need to use the not in list event.

Maybe i'm missing something?

^^^^^^^+1 ^^^^^^^

 

[cos]

http://www.youtube.com/watch?v=pMiJHhjzC8Y

 

[liamfitz]

You're quite right, I didn't explain very well. I'll put it in logical steps, which should simplify my plan.
1. When form is loaded, combo is loaded with Client info ( same fields as in my select statement ) BUT ALL RECORDS - in this case about 1200 i.e unfiltered.
2. When user types it DOES filter that Row Source ( albeit on the Forename, and not the Surname which I would prefer ).
3. I want my sub routine above to 'clear out' this original FULL LIST.
4 Then select a recordset/subset of that data which is based on my "WHERE Surname LIKE '" & cboClient.Text & "*' ORDER BY ... " etc. - i.e the typed text adding to the wild-card criteria.
5. As the sought Client/Record appears, the user then selects it, and teh combo box takes its value for the search for main from data.

Even changing the filter from the Forename to Surname would work, but I don't know how.

 

[boblarson]

Okay, this can be a little slow at times but here's what you are probably looking for:
http://allenbrowne.com/AppFindAsUType.html

 

[liamfitz]

Thanks for this link boblarson. I've now got what I wanted basically. I've changed the field order of the original 'SELECT' statement, so it now automatically searches on Surname rather than Forename ( as this is the field order in the row source, I was wondering if the default field for searching i.e 1st visible field, could be changed in the properties for example to the 2nd ) But it looks fine, and is probably the better way round. How do I hide the ID field ( I didn't use a wizard ),and set this as the stored field for selecting an item, to base my recordsource for main form on ? Many thanks.

 

[boblarson]

The column widths of the combo can hide the ID field. If you have two fields in the row source and the combo's column count property has been set to 2 then the columns widths property would be like:

0";2"

or if it is the second column:

2";0"