I have written some code that responds to a keypress event of a text box and auto completes the text, unfortunately it does not quite work. Here are the two functions I wrote:
Function GetFldLike(Wtbl As String, Wfld As String, Wval As String) As String
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LStr As String
Set db = CurrentDb()
LSQL = "SELECT " & Wfld & " FROM " & Wtbl & " WHERE " & Wfld & " LIKE '" & Wval & "';"
Set Lrs = db.OpenRecordset(LSQL)
If Lrs.EOF = False Then
LStr = Lrs(Wfld)
Else
LStr = ""
End If
Lrs.Close
Set Lrs = Nothing
GetFldLike = LStr
End Function
Private Sub Company_KeyPress(KeyAscii As Integer)
Dim Stmp As String
If KeyAscii = Asc("\") Then
If Me.Dirty Then
Me.Dirty = False
End If
Stmp = GetFldLike("Addresses", "Company", Me.Company & "*")
If Not (Stmp = "") Then
Me.Company = Stmp
End If
End If
End Sub
The problem occurs when the Comapny text box is filled from the Stmp variable, afterwards Access sets the text box contents to "\". Oddly it works fine if I put a break point on the "Stmp = ..." line, something to do with the text box losing focus. I need a way of aborting / surpressing the key press event immediately after the "Me.Company = Stmp" line, that way Access won't change the text box again.