Autocomplete from one object to another

[WC Justice]

I have an Access 2000 database that currently uses a combo box to populate
information about an existing party into a new record. I first check to see
if that party is already in the database via the combo box; if it is, I
select it, if not, I key the new party's info in. I have changed the combo
box to a list box, which hides the PartyID and then shows a concotonation of
LastName, FirstName, then a column of more specific identifying info in case
there are two parties with the same first and last names.

I would like to be able to begin typing in the LastName texbox and have vba
highlight the first matching record at the top of the list box, enabling me
to select another record, if appropriate, to execute the update code. For
example, if I have 5 Smiths, I would like to type only "Smi", have all 5
Smiths appear at the top of the list box, and still be able to select the
3rd Smith.

Is this possible? If so, please advise.

Thanks

 

[Guest]

Yes it can be done. Place some code in the ON CHANGE
event that requeries after every letter is entered. At
the end of the code do a SendKeys "{F2}" statement so the
cursor ends up at the end of the field ready for the next
letter.

David S.

 

[WC Justice]

Thanks, but that I had already tried that (I am now using a list box instead
of a combo box), using:

Private Sub LastName_Change()
Me.lstNameSelect.Value = Me.LastName
Me.lstNameSelect.Requery
End Sub

This seemed to have no effect on the list box, other than it appears that
the box is being requeried.

The list box consists of 3 columns, with the first and bound column (0"
width) being an OwnerID, the second being a concotonation of LastName,
FirstName, and the third an additional reference number. I tried making the
first column visible and typing the OwnerID number in the LastName box, but
it made no difference. I am hoping that as I type "Ada", the first instance
of "Adams, John" will be highlighted in the list box so that I can easily
see that it is there, and so that I can select it, if appropriate, and have
the "After Update" event trigger the code that populates the current record
with the data associated with "Adams, John".

Please advise. Thanks