Self look up, auto fill

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I get a form to automatically fill in something that was entered
previously in this field, after someone starts typing the correct entry?

In other words, I want the Table (I guess) to look up itself. Can this be
done? I see it all the time in commercial programs, some of which are likely
based on Access, but I don't know how to make it happen.

Thanks.
 
The only way I have found to do that is to make the control a drop-down
combo box and put the "limit to list" as false.

As far as I know, Access does not have an auto-fill like IE.

Rick B
 
Rick B said:
The only way I have found to do that is to make the control a
drop-down combo box and put the "limit to list" as false.

As far as I know, Access does not have an auto-fill like IE.

Rick B
Click to expand...

That's the easiest way, though I'm pretty sure you can get a text box to
do this if you're willing to write lookup code for its Change event. I
can't remember if I've tested that out or not.
 
Dirk Goldgar said:
That's the easiest way, though I'm pretty sure you can get a text box
to do this if you're willing to write lookup code for its Change
event. I can't remember if I've tested that out or not.
Click to expand...

I just tried it out, and it looks to me like the Keypress event is
easier to work with for this purpose. I just tried this code on a
"LastName" text box, and it seems to work pretty well:

'----- start of experimental code -----
Private Sub LastName_KeyPress(KeyAscii As Integer)

Dim strMatchText As String
Dim strFoundText As String

Select Case KeyAscii
Case Is < 32: Exit Sub
Case Is > 126: Exit Sub
End Select

strMatchText = Mid(Me.LastName.Text, 1, Me.LastName.SelStart) &
Chr$(KeyAscii)
KeyAscii = 0

If Len(strMatchText) = 0 Then Exit Sub

With Me.RecordsetClone

.FindFirst _
"LastName Like " & _
Chr(34) & _
Replace(strMatchText, """", """""") & _
"*" & Chr(34)

If .NoMatch Then

With Me.LastName
.Text = strMatchText & Right(.Text, Len(.Text) -
(.SelStart + .SelLength))
.SelStart = Len(strMatchText)
End With

Else
strFoundText = !LastName

With Me.LastName
.Text = strFoundText
.SelStart = Len(strMatchText)
.SelLength = Len(strFoundText)
End With

End If

End With

End Sub

'----- end of code -----

Please note that this is *not* thoroughly tested, and probably has some
quirks I haven't come across.
 
Thanks, Dirk and Rick. I'll experiment with the code, and it's another step
towards being able to write code myself, at which I am still in the baby-step
stage. And I'm sure I'll use "control -" plenty myself, but I'd rather have
a more automated answer for my end-stage users, who mostly do not want to
learn new tricks.
 
r. howell said:
Thanks, Dirk and Rick. I'll experiment with the code, and it's
another step towards being able to write code myself, at which I am
still in the baby-step stage. And I'm sure I'll use "control -"
plenty myself, but I'd rather have a more automated answer for my
end-stage users, who mostly do not want to learn new tricks.
Click to expand...

If you plan to try out that code I posted, be aware that a couple of the
lines were broken into two by the newsreader. You'll have to fix them
before the code will compile.
 
I'd really like to use this feature, which seems very similar to what happens
in EXCEL, however when I type in a name similiar to an already listed name it
autofills the stored name and won't let me type the new name. (i.e. I have a
Phillip Young already in the table. When I try to type in Phil Old, the text
box auto fills with Phillip Young and doesn't recognize that Phil Old is
actually a new item.) Is there more to this code?
 
Hello Dirk,

I read your post regarding autofill. I'd like to use the code myself. When
testing the code I noticed that if I wrote the word "Cat" in the first record
then in the second the after the "C" was pressed it recognised the word "Cat"
but it would not let me add a space after the word. However it would let me
add "CatNip". Any idea why it won't allow spaces? Sorry for the inconvenience.

Regards John.
 
Dirk,
I'm new to Access and tried the code and had the same issue JohnC had with
spaces. Could it be the """" in the replace string? Your help would be
greatly appreciated.
Kelly
 
Kelly said:
Dirk,
I'm new to Access and tried the code and had the same issue JohnC had with
spaces. Could it be the """" in the replace string? Your help would be
greatly appreciated.
Click to expand...


I've looked at the code -- from way back in 2005! -- and made some further
tests, and it's definitely got a problem with the entry of spaces at the end
of the text. The reason, it turns out, is that trailing spaces are
truncated when a string is assigned to the .Text property. I was unaware of
this behavior when I wrote the code, but it makes some sense when you
consider that a text box automatically truncates trailing spaces when you
enter them manually.

I've written a different approach that may work better, but again I haven't
tested it exhaustively. The code below uses more than one event, and a
couple of module-level variables, so you will have to merge it with your
existing code for the form (but remove the ToEquipment_KeyPress procedure
that you currently have).

'----- start of code for form module -----
Option Compare Database
Option Explicit

Dim mRst As DAO.Recordset
Dim mintLastLen As Integer

Private Sub Form_Load()

Set mRst = Me.RecordsetClone

End Sub


Private Sub Form_Unload(Cancel As Integer)
Set mRst = Nothing
End Sub


Private Sub ToEquipment_Change()

On Error GoTo Err_ToEquipment_Change

Dim intLen As Integer
Static fWorking As Boolean

If mRst.RecordCount = 0 Then Exit Sub

If Not fWorking Then
With Me.ToEquipment
intLen = Len(.Text)
If intLen > mintLastLen Then
mRst.FindFirst _
"ToEquipment Like " & _
Chr(34) & .Text & "*" & Chr(34)
If Not mRst.NoMatch Then
fWorking = True
.Text = mRst!ToEquipment
.SelStart = intLen
.SelLength = 255
fWorking = False
End If
End If
End With
End If

Exit_ToEquipment_Change:
mintLastLen = Len(Me.ToEquipment.Text)
Exit Sub

Err_ToEquipment_Change:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_ToEquipment_Change

End Sub


Private Sub ToEquipment_GotFocus()
mintLastLen = 0
End Sub
'----- end of code for form module -----

Try that and see if it gives you better results.
 
Back
Top