search button to fill a combo box

[boomer1974]

Good day all,
This is most likely easy for some of you but I am having trouble. I have an unbound form and on that form, an unbound combo box. Users are complaining because there is about 600 items in the list when they hit the drop down and it takes too long to scroll down to find the right item(record source for combo box is an SQL statement) my question is what is the best way to build a search function (similar the the built in access find) to fill that combo box based on what the user types in? Any direction would be greatly appreciated.

Thanks,
Boomer

 

[Orion45]

The simple solution is to create a SQL statement that looks at the search field to complete the WHERE clause. The WHERE clause would then look something like this:
WHERE(TableName.FieldOne = [forms]![MainFormName]![searchFieldName]);
The combo box then needs to requery once the user has entered something in the search box (AfterUpdate).
Let me know if you need clarification.

 

[boomer1974]

Here is my code so far. I am getting the message "no current record" so I think that there is somehting wrong with my sql "like" statement. Could you provide any assistance?
********************************************************
Private Sub Command330_Click()
Dim db As Database
Dim rs As Recordset
Dim strsql As String
Set db = CurrentDb()
strsql = "SELECT * FROM tblUser Where([LastName] Like """ & " * " & Forms!frmCalender!srchfld.Value & " * " & ""&quot"
Set rs = db.OpenRecordset(strsql)
MsgBox rs!LastName
End Sub
***********************************************************
Thanks, Boomer

 

[Orion45]

I didn't realize you were using a recordset to fill your combo box. You'll need to assign the srchfld.value to a varible before this will work. Try adding this to your code.

dim strTemp as string
strTemp= "*" & (me.srchfld) & "*"
strsql = "SELECT * FROM tblUser Where([LastName] Like '"& strTemp &"')"

You might also want to make sure that your recordset contains something. Do an EOF check to make sure its not empty.

If rs.EOF = False then
  MsgBox rs!LastName
end if

Hope this helps,