Or is my code bad? I'm trying to make a "type-ahead" feature for MS Access. It needs to provide the following features:
- pressing <TAB> or <ENTER> moves the cursor to the next field
- if multiple entries in the lookup table match what has been typed so far, provide more fields so the proper entry can be selected
The following code should execute a SQL query and print the number of returned records in the debug window. It prints "0" when the WHERE clause is WHERE [Name] LIKE "*" but "20" when it's WHERE [Name] Is Null. Because of the initial condition, I also wound up with zero rows returned when I used the value in a text box (so the query was dynamically generated, "WHERE [Name] LIKE """ & Text0.Value & "*"""). I get precisely the opposite behavior when I run the query in the Access query window--why? And, just to be sure I'm getting the right query, I copy it from one window to the other.
Private Sub Text0_KeyPress(KeyAscii As Integer)
If KeyAscii < 32 Then
Text0.Value = ""
Exit Sub
End If
Text0.Value = Text0.Value & Chr(KeyAscii)
Dim sql As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
sql = "SELECT [User] FROM [Users] WHERE [User] LIKE '*'"
rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Debug.Print sql, Text0.Value, rs.RecordCount
End Sub
|
