DON'T KNOW HOW TO DELETE THIS - IGNORE, BAD ADVICE
From my experience in A2003, don't use SQL as a variable. SQL is an actual property in something called a queryDef (if you are familiar with this) in VBA development. Instead, use strSQL, it will be much safer.
How many members are we talking about here? Requerying the combo box's underlying query with key strokes in the actual combo box is not something I'd recommend. The combo box will autofill as you type in a name. As far as I can tell, you don't need an an change event.
As far as the name "comboname", it really helps you and anyone who comes along afterwards to give a more descriptive name to that query or table field.
I've done what you are trying to do in the past but instead, I have an unbound text box, say "txtSearchCombo" near the combo box to enter parameters of name or membership number. You would then set your query to be:
strSQL = "Select comboname,[Memb No] from qry_name_for_register_combo where comboname Like '*" & txtSearchCombo & "*' "
You would then set an on change event for txtSearchCombo like the following. Note, the error handling thing is an anal retentiveness of mine, you may not need it, but I have had it hammered into me in the past on the old news group, comp.databases.ms-access since the 90s.
Code:
Private Sub txtSearchCombo_Change()
On Error GoTo Err_Proc
Me.cmb_memb_no.Requery
Exit_Proc:
Exit Sub
Err_Proc:
Select Case Err.Number
Case Else
MsgBox "Access Error " & Err.Number & " " & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
Resume Exit_Proc
End Select
End Sub
On the form on_current event, reset the txtSearchCOmbo to a zero length string and then call the above on_change event:
Code:
Private Sub Form_Current()
On Error GoTo Err_Proc
Me.txtSearchCombo = ""
txtSearchCombo_Change
Exit_Proc:
Exit Sub
Err_Proc:
Select Case Err.Number
Case Else
MsgBox "Access Error " & Err.Number & " " & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
Resume Exit_Proc
End Select
End Sub
--
Tim